JL Computer Consultancy

User or Owner access in stored procedures.

June 1999


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;


Back to Main Index of Topics

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


Back to Main Index of Topics