JL Computer Consultancy

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.

Package dbms_sys_sql

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'.

Creating suitable users

rem

rem     c_user.sql

rem

rem     minimalist create user - execute as a dba (SYS preferred)

rem

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;

Main Script

rem

rem     Script:        dbms_sys.sql

rem     Author:        Jonathan Lewis

rem     Dated:         23 Feb 1999

rem     Purpose:       Demo of using dbms_sys_sql.sql

rem

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

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

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.

rem

create or replace procedure create_table as

        v_cursor       number;

        v_dummy        number;

begin

        v_cursor := dbms_sql.open_cursor;

        sys.dbms_sys_sql.parse_as_user(

               v_cursor,

               'create table jpl_xx(n1 number)',

               dbms_sql.v7

        );

/*

        dbms_sql.parse(

               v_cursor,

               'create table jpl_xx(n1 number)',

               dbms_sql.v7

        );

*/

        v_dummy := dbms_sql.execute(v_cursor);

        dbms_sql.close_cursor(v_cursor);

end;

/

grant execute on create_table to public;

Loopback database links

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:

        D804t.WORLD =

               (DESCRIPTION =

                       (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.

        connect sys/sys

        create or replace procedure jpl_junk as

        begin

               for r1 in (

                       select table_name 

                       from user_tables@d804@hp

                       where rownum <=10

               ) loop

                       dbms_output.put_line(r1.table_name);

               end loop;

               end;

        /

        grant execute on jpl_junk to jpl;

Connect as sys and execute the procedure - typical output appears below, listing tables owned by SYS.

        connect sys/sys

        set serveroutput on

        execute jpl_junk

        ACCESS$

        AQ$_MESSAGE_TYPES

        AQ$_QUEUE_STATISTICS

        ARGUMENT$

        ATEMPTAB$

        ATTRCOL$

        ATTRIBUTE$

        AUD$

        AUDIT$

        AUDIT_ACTIONS

Connect as another user and execute the procedure - a different set of tables will be listed, e.g.

        connect jpl/jpl

        set serveroutput on

        execute sys.jpl_junk

        ROWID_TEST

        TEMP

        TEMP_JPL

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.

Conclusion:

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..