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.