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 from
dbms_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:
- 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).
- 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 is
userid. Here is how you can use it to remove jobs residing in schemas “A” and “B”:
Connected to: Oracle Database 11g Enterprise Edition Release 188.8.131.52.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.
There is another mechanism that can be used – proxy authentication.
But I should still say that I was very excited when Alex shared this dbms_sys_sql info.
Nice tip, thanks but you can use more easy way to remove jobs in different schema:
exec DBMS_IJOB.remove(); commit;
Kirill Loifman, dadbm.com