Using DBMS_SYS_SQL to Execute Statements as Another User

I do realize that for most of you, there may be nothing new about the dbms_sys_sql package – knowledge of it has been floating around for quite a while. I myself discovered this package a couple of years ago while playing around with HTMLDB’s (now APEX’s) internals. I’m posting this as a response to a vox populi demand: I often see people who could benefit fromdbms_sys_sql, if they only knew about it.

Sometimes you need to execute SQL (or PL/SQL) as some other user. Say you need to remove a job residing in some other user’s schema. Or you need to create a database link in a different schema. You can’t do things like these without becoming the other user. If you don’t know the other user’s password then you basically have two choices:

1. Remember the user’s password hash value, then change the password, then log in and do your job, then put the password back using identified by values (you can skip the first and last parts if you don’t care about keeping original password in place).
2. Create a procedure with definer rights (which is the default) in another schema and use it to execute statements, effectively behaving as you are the other user.

A third method exists: use dbms_sys_sql. It is similar to dbms_sql except that it allows you to parse and execute statements as another user. That is, the dbms_sys_sql.parse() function accepts an additional parameter, which isuserid. Here is how you can use it to remove jobs residing in schemas “A” and “B”:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning,
Real Application Clusters, OLAP, Data Mining and Real Application Testing options
SQL> select job, schema_user   2   from dba_jobs   3   where schema_user in ('A', 'B');    
   JOB SCHEMA_USER ---------- ------------------------------          3 A          4 B          5 A
SQL> declare   2   l_cursors dbms_sql.Number_Table;   3   l_result number;   4  begin   5   for cur in (   6  
select j.JOB, u.user_id   7     from dba_jobs j, dba_users u   8     where j.SCHEMA_USER=u.username   9     
and j.SCHEMA_USER in ('A', 'B')  10   ) loop  11    --parse the cursor only if we haven't already  12 
 if ( not l_cursors.exists(cur.user_id) )  13    then  14     l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
 15     --parsing anonymous PL/SQL block as a job owner  16     dbms_sys_sql.parse_as_user(  17      c => l_cursors(cur.user_id), 
18      statement => 'begin dbms_job.remove(:job); end;',  19      language_flag => dbms_sql.native, 
20      userid => cur.user_id  21     );  22    end if;  23    --bind the job number  24  
dbms_sys_sql.bind_variable(  25     c => l_cursors(cur.user_id),  26     name => 'job',  27    
value => cur.job  28    );  29    --remove the job by executing  30    l_result:=dbms_sys_sql.execute(l_cursors(cur.user_id));
31   end loop;  32  end;  33  /  PL/SQL procedure successfully completed.  SQL> select job, schema_user   2   from dba_jobs 
3   where schema_user in ('A', 'B');  no rows selected

Keep in mind that this package is undocumented, so any behavior cannot be guaranteed. Keep an eye on who is granted an execute privilege on it, since as you have seen, it is a very powerful tool.