User or Owner access in stored procedures.
As you know, a stored procedure executes under the owners identity, not under the identity of the caller. At least, this is the case under Oracle 7.3 and 8.0; from Oracle 8.1 procedures can be defined to operate with the invoker's rights.
There are, however, at least two ways to bend the rules, and this note describes them. The first is down to the undocumented package dbms_sys_sql which is used by the Oracle Replication Options, the second is through a cunning use of loop-back database links, suggested to me by Thomas Kyte.
If you examine the $ORACLE_HOME/rdbms/admin/catrepad.sql you will find a couple of calls to the procedure
dbms_sys_sql.parse_as_user(cursor, string, version)
which does exactly what the name suggests - if you use this procedure instead of dbms_sql.parse, the string will be parsed in the schema of the caller at run-time rather than being parsed in the schema of the owner. The main script below can be used to demonstrate the effect; the script has two variant copies of a routine to create a table - depending on which piece of code you comment out, the table will be created in the schema of the procedure owner (dbms_sql) or in the schema of the caller (dbms_sys_sql).
To use the script, first create a couple of users, with the privileges 'create session', 'create table', and one with 'create procedure'; the first script below will help. When this has been done connect as SYS and grant execute on dbms_sys_sql to the user with the 'create procedure' privilege. Connect as this user and run the main script, then connect to the other user and execute 'owner.create_table'.
rem minimalist create user - execute as a dba (SYS preferred)
create user &1 identified by &1
default tablespace user_data
temporary tablespace temporary_data
quota unlimited on user_data
grant create session to &1;
grant create table to &1;
grant create procedure to &1;
grant execute on sys.dbms_sys_sql to &1;
rem Script: dbms_sys.sql
rem Author: Jonathan Lewis
rem Dated: 23 Feb 1999
rem Purpose: Demo of using dbms_sys_sql.sql
rem If the procedure is created using the ordinary dbms_sql parse
rem then the user who calls the procedure creates a table in the
rem schema of the user who created the procedure.
rem If the procedure is created using the dbms_sys_sql parse as user
rem then the table is created in the schema of the user calling the
rem procedure, although that user id needs a tablespace quota, and the
rem privilege to create tables.
rem The creator of this package needs execute privileges on dbms_sys_sql
rem granted from sys. Execute on dbms_sql is public, as is the synonym.
create or replace procedure create_table as
v_cursor := dbms_sql.open_cursor;
'create table jpl_xx(n1 number)',
'create table jpl_xx(n1 number)',
v_dummy := dbms_sql.execute(v_cursor);
grant execute on create_table to public;
On the database server create a public database link which does a 'loopback' to the local machine. e.g.
create public database link D804@hp using 'd804t'
where your tnsnames.ora has an entry like:
(ADDRESS = (PROTOCOL = tcp)(host = 127.0.0.1)(port = 1526))
(CONNECT_DATA = (SID = D804))
Note the 'connection qualifier' format of the database link. For some reason this is a requirement for database links which loop back to the local machine. If you do not use the '@extrabit' on the database link you get oracle error:
ORA-02082: a loopback database link must have a connection qualifier
Check that this is working by issuing a query like: select user from dual@d804@hp;
To demonstrate the effect of loopback connections and their impact on the execution of stored procedures you could follow the steps below - I used the SYS account to create a procedure and another account (JPL) which owned a few tables to demonstrate the effect of executing the procedure.
Connect as sys and create the procedure - granting execute to the other account. Change the name of the loopback db link and the other user as appropriate.
create or replace procedure jpl_junk as
for r1 in (
where rownum <=10
grant execute on jpl_junk to jpl;
Connect as sys and execute the procedure - typical output appears below, listing tables owned by SYS.
set serveroutput on
Connect as another user and execute the procedure - a different set of tables will be listed, e.g.
set serveroutput on
If you repeat the process but change the procedure to omit the database link, both calls will list tables owned by SYS (or the owner of the procedure if you are not using the SYS account).
The trick here, of course, is that the database link has been specified without a user id and password (connect to user identified by password), so current userid and password are used to connect back to the database and the SQL is finally resolved at run-time instead of compile time.
Both methods are a little naughty - one depends on an undocumented package (albeit one that Oracle uses itself) and the other depends on what might be called a side-effect. Which one should you use - if either? Possibly you should avoid both and wait to implement Oracle 8.1; personally I would be happy to use either method for batch-like jobs which generate and report transient data, but would not use either technique for manipulating persistent base data..