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
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.
Connect as read_only and run an update.
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
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
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.
