How to change a DBMS job owned by another user as user sys?

Also: How to create or test a private DB link as user sys?

Say we are logged in as user sys and need to change/delete/add a DBMS job owned by another user, e.g. we need to set it to BROKEN:

SQL> SELECT user FROM dual;

USER                          
------------------------------
SYS                           


SQL> SELECT JOB,SCHEMA_USER,BROKEN FROM dba_jobs where job=1;

       JOB SCHEMA_USER                    BROKEN
---------- ------------------------------ ------
         1 SYSMAN                         N     


SQL> begin
SQL> dbms_job.broken(1, TRUE);
SQL> end;

ORA-23421: job number 1 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 536
ORA-06512: at "SYS.DBMS_JOB", line 248
ORA-06512: at line 2

This doesn’t work since, we are not owning this job, so the PL/SQL block would need to be exeucted as user SYSMAN.

Option 1: Find out the password, logon as the user and do the change.

Option 2: If you don’t have the password, you could make note of the password hash in the password column of SYS.USER$ for that user, change the password, quickly create a session, revert back to the old password by setting the password hash value through ALTER USERS … IDENTIFIED BY VALUES ‘…’ and then do the job change in the created session. Risk is: You are locking out users using the actual password for a short while.

Option 3: In Mumbai you can use the “Execute script as another user…” function in the console window to change the job. You need to be SYS for this, but you won’t need the password of the job owner.

How does Mumbai do its magic here? The “Output” panel in the lower part of Mumbai’s console window shows what Mumbai actually executed:

declare
 uid number;
 l_result integer;
 sqltext varchar2(1000) := 'begin dbms_job.broken(1, TRUE); end;  ';
 myint integer;
 begin
     select user_id into UID from all_users where username like 'SYSMAN';
     myint:=sys.dbms_sys_sql.open_cursor();
     sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
     l_result:=sys.dbms_sys_sql.execute(myint);
     sys.dbms_sys_sql.close_cursor(myint);
 end ;
PL/SQL procedure successfully completed

So, the dbms_sys_sql package is used to execute the SQL. The trick is to parse as another user with the parse_as_user procedure.

Besides editing/adding/deleting DBMS jobs, the “Execute script as another user…” comes in handy when creating/testing private database links.

This entry was posted in Mumbai. Bookmark the permalink.

7 Responses to How to change a DBMS job owned by another user as user sys?

  1. You can also use dbms_ijob instead of dbms_job

  2. Option 2 has another risk if your password policy forbids reuse of passwords🙂

    http://blog.sydoracle.com/2010/10/temporary-access-to-database-accounts.html

    • fordoradba says:

      Which or environment does, but as user sys, we modify the users profile to defaut.
      reset thier password. Perform our necessary tasks. Reset the password back to the original value, and then set the profile back to the original value.

  3. Hi Marcus,

    what privilegues do I need to run sys.dbms_sys_sql.parse_as_user ?

    sys granted user1
    sys> grant execute on dbms_sys_sql to user1;

    user1 then:
    user1> declare procedure
    2 execute_immediate_as_use(
    3 vsql in clob,
    4 vasuser in varchar2
    5 ) is
    6 uid number;
    7 l_result integer;
    8 myint integer;
    9 begin
    10 begin
    11 select user_id
    12 into UID
    13 from all_users
    14 where username like vasuser;
    15 exception when no_data_found then
    16 raise_application_error(-20999, ‘User “‘ || vasuser || ‘” does not exist’);
    17 end;
    18
    19 myint:=sys.dbms_sys_sql.open_cursor();
    20 sys.dbms_sys_sql.parse_as_user(myint,vsql,dbms_sql.native,UID);
    21 l_result:=sys.dbms_sys_sql.execute(myint);
    22 sys.dbms_sys_sql.close_cursor(myint);
    23 end execute_immediate_as_use;
    24 begin execute_immediate_as_use(‘create table temp(i int)’, ‘USER2’); end;
    25 /
    declare procedure
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1364
    ORA-06512: at line 20
    ORA-06512: at line 24

    What do I miss ?

    Thanks
    Matthias

  4. Thanks, of course that’s what I missed.
    user1 had granted the DBA-role but that wasn’t sufficient.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s