Invokers rights

If synonyms are not an option, how can invoker rights be implemented to allow users access to stored PL/SQL code?

The sample configuration is as follows:

- User app1, contains application tables and code

- User firefight will have update privileges on the tables and execute on all stored code

- User read_only has read only (select) privileges on the tables and only has execute on stored code which does not update

The problems are:

- What if there is more than one application owner with duplicate table names? Synonyms are problematic for firefight user because the correct schema needs to be established prior to update particularly when using stored PL/SQL

- The read_only user obviously cannot have execute privileges to a package which provides update privileges. In essence this means that the user cannot see these packages at all.

Create demo users:

 CREATE USER app1 IDENTIFIED BY app1;  
 CREATE USER read_only IDENTIFIED BY read_only;  
 CREATE USER firefight IDENTIFIED BY firefight;  
 GRANT CREATE SESSION TO app1, read_only, firefight;  
 GRANT CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE TO app1;  
 GRANT UNLIMITED TABLESPACE TO app1;  

Connect as app1 and create a table:

 CREATE TABLE t1(artist_id   NUMBER,   
                 artist_name VARCHAR2(50 CHAR),   
                 CONSTRAINT t1_pk PRIMARY KEY (artist_id));  
 CREATE SEQUENCE artist_id_seq START WITH 1 INCREMENT BY 1;  

 GRANT SELECT ON t1 TO read_only;  
 GRANT SELECT, INSERT, UPDATE, DELETE ON t1 TO firefight;
 GRANT SELECT ON artist_id_seq TO read_only, firefight;

Create a simple API allowing update and insert privileges and populate the table with some sample rows.

 CREATE OR REPLACE PACKAGE t1_api  
 AS  
  PROCEDURE do_insert(pi_artist_name IN t1.artist_name%TYPE);  
  PROCEDURE do_update(pi_artist_id  IN t1.artist_id%TYPE,   
            pi_artist_name IN t1.artist_name%TYPE);  
 END t1_api;  
 /  
 show errors  
 -- By default uses DEFINER privileges  
 CREATE OR REPLACE PACKAGE BODY t1_api  
 AS  
  PROCEDURE do_insert(pi_artist_name IN t1.artist_name%TYPE)  
  IS  
  BEGIN  
   INSERT INTO t1(artist_id, artist_name)  
   VALUES(artist_id_seq.nextval, pi_artist_name);  
   COMMIT;  
  END do_insert;  
  PROCEDURE do_update(pi_artist_id  IN t1.artist_id%TYPE,   
            pi_artist_name IN t1.artist_name%TYPE)  
  IS  
  BEGIN  
   UPDATE t1  
   SET  artist_name = pi_artist_name  
   WHERE artist_id = pi_artist_id;  
   COMMIT;  
  END do_update;  
 END t1_api;  
 /  
 show errors

 exec t1_api.do_insert('Pink Floyd');  
 exec t1_api.do_insert('The Smiths');  
 exec t1_api.do_insert('The Beatles');  
 COMMIT;  

Given the scenario above, read_only cannot be given any privileges on the package at all otherwise they will be able to update the contents of the table. This results in the following error:

 GRANT EXECUTE ON t1_api TO firefight;

 CONNECT read_only/read_only;  
 exec app1.t1_api.do_insert('REM');  
 /* Returns an expected error:
 Error starting at line 40 in command:
 exec app1.t1_api.do_insert('REM')
 Error report:
 ORA-06550: line 1, column 7:
 PLS-00201: identifier 'APP1.T1_API' must be declared
 ORA-06550: line 1, column 7:
 PL/SQL: Statement ignored
 06550. 00000 -  "line %s, column %s:\n%s"
 *Cause:    Usually a PL/SQL compilation error.
 *Action:
 */

 CONNECT firefight/firefight;  
 exec app1.t1_api.do_insert('Foo Fighters');  
 -- Works successfully, "anonymous block completed"

 -- Extra row can be seen in the table
 SELECT *  
 FROM  app1.t1  
 ORDER BY artist_id;

 ARTIST_ID ARTIST_NAME                                      
 ---------- --------------------------------------------------
          1 Pink Floyd                                         
          2 The Smiths                                         
          3 The Beatles                                        
          4 Foo Fighters                                       

Although this works, read_only is prevented from viewing the contents of the package - ideally, for support purposes, read_only should be able to view the contents of the packaged code. Invoker rights is ideal for this scenario.

So the next step is to amend the package to use invoker rights. Since a synonym is not an option in this case, the schema owner is prefixed to the name of the table to ensure the correct table is updated. Note that this also has the advantage of working throughout the development life cycle, as the code is moved to different environments, the application owner schema will always be updated.

Firstly, amend the calling SQL script to use variables.

 set define on verify on  

 DROP PACKAGE t1_api;
 COLUMN the_user new_value app_user;  
 SELECT user AS the_user  
 FROM  dual;  
 -- Pass the value of the compiling schema (app1) into the script to compile the stored procedure
 START t1_api_invoker.sql '&app_user' 

Now update the package to use the variable. Note the use of the double "." to ensure the object names are prefixed by the schema. &&1 references the value of the first argument passed into the script - &app_user.

 set define on verify on  
 CREATE OR REPLACE PACKAGE t1_api  
 AUTHID CURRENT_USER  
 AS  
  PROCEDURE do_insert(pi_artist_name IN t1.artist_name%TYPE);  
  PROCEDURE do_update(pi_artist_id  IN t1.artist_id%TYPE,   
            pi_artist_name IN t1.artist_name%TYPE);  
 END t1_api;  
 /  
 show errors  
 -- By default uses DEFINER privileges  
 CREATE OR REPLACE PACKAGE BODY t1_api  
 AS  
  PROCEDURE do_insert(pi_artist_name IN t1.artist_name%TYPE)  
  IS  
  BEGIN  
   INSERT INTO &&1..t1(artist_id, artist_name)  
   VALUES(&&1..artist_id_seq.nextval, pi_artist_name);  
   COMMIT;  
  END do_insert;  
  PROCEDURE do_update(pi_artist_id  IN t1.artist_id%TYPE,   
            pi_artist_name IN t1.artist_name%TYPE)  
  IS  
  BEGIN  
   UPDATE &&1..t1  
   SET  artist_name = pi_artist_name  
   WHERE artist_id = pi_artist_id;  
   COMMIT;  
  END do_update;  
 END t1_api;  
 /  
 show errors  

 -- Note that execute is now granted to read_only and firefight
 GRANT EXECUTE ON t1_api TO firefight;
 GRANT EXECUTE ON t1_api TO read_only;

Connect as read_only and run an update.

 > CONNECT read_only/read_only  
 Connected  
 > exec app1.t1_api.do_insert('REM')  
 Error starting at line 73 in command:  
 exec app1.t1_api.do_insert('REM')  
 Error report:  
 ORA-01031: insufficient privileges  
 ORA-06512: at "APP1.T1_API", line 6  
 ORA-06512: at line 1  
 01031. 00000 - "insufficient privileges"  
 *Cause:  An attempt was made to change the current username or password  
       without the appropriate privilege. This error also occurs if  
       attempting to install a database without the necessary operating  
       system privileges.  
       When Trusted Oracle is configure in DBMS MAC, this error may occur  
       if the user was granted the necessary privilege at a higher label  
       than the current login.  
 *Action:  Ask the database administrator to perform the operation or grant  
       the required privileges.  
       For Trusted Oracle users getting this error although granted the  
       the appropriate privilege at a higher label, ask the database  
       administrator to regrant the privilege at the appropriate label.  
 > SELECT *  
 FROM  app1.t1  
 ORDER BY artist_id  
  ARTIST_ID ARTIST_NAME                     
 ---------- --------------------------------------------------  
      1 Pink Floyd                       
      2 The Smiths                       
      3 The Beatles                      
      4 Foo Fighters              

This produces the expected "insufficient privileges" error.

Now run the package as firefight:

 > CONNECT firefight/firefight  
 Connected  
 > exec app1.t1_api.do_insert('Metallica')  
 anonymous block completed  
 > SELECT *  
 FROM  app1.t1  
 ORDER BY artist_id  
  ARTIST_ID ARTIST_NAME                     
 ---------- --------------------------------------------------  
      1 Pink Floyd                       
      2 The Smiths                       
      3 The Beatles                      
      4 Foo Fighters                      
      5 Metallica         

The insert was performed as expected.

Although a synonym is the easiest way to ensure invokers rights code works as expected, sometimes environment configuration means that it is not possible.
As demonstrated above, SQL scripts to create the objects can be prefix the name of the object with the schema the update should be applied to, allowing the read_only user to be granted execute privileges on the package.