By default, PL/SQL program units are created using definer rights and as such are executed with all the privileges granted directly to the user that owns them. This can be very useful when you want low privileged users to perform tasks that require a high level of privilege. In these cases the tasks can be wrapped up in a PL/SQL program unit, with execute privilege granted on that program unit to the low privileged user. The problem with definer rights is it is very easy to accidentally expose excessive functionality to a user.
An alternative is to create the program unit with invoker rights, so it is run in the context the calling user, rather than the user that created it. The advantage of this is the program unit is only able to perform tasks that the calling user has privilege to perform, including those privileges granted via roles. Invoker rights has a number of issues including:
Oracle 12c introduced code based access control (CBAC), allowing roles to be granted directly to definer and invoker rights program units, thereby letting you to guarantee the level of privilege present in the calling user, without having to expose additional objects directly to that user. This article focusses on how code based access control can be used to solve the first two issues associated with invoker rights program units.
Related articles.
Create two test users. The first will be able to create tables and PL/SQL program units, while the second can only connect to the database.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; DROP USER cbac_user_1 CASCADE; DROP USER cbac_user_2 CASCADE; CREATE USER cbac_user_1 IDENTIFIED BY cbac_user_1 QUOTA UNLIMITED ON USERS; GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO cbac_user_1; CREATE USER cbac_user_2 IDENTIFIED BY cbac_user_2 QUOTA UNLIMITED ON USERS; GRANT CREATE SESSION TO cbac_user_2;
Connect to the CBAC_USER_1
user and create two test tables.
CONN cbac_user_1/cbac_user_1@pdb1 CREATE TABLE tab1 ( id NUMBER ); INSERT INTO tab1 SELECT level FROM dual CONNECT BY level <= 5; COMMIT; CREATE TABLE tab2 ( id NUMBER ); INSERT INTO tab2 SELECT level FROM dual CONNECT BY level <= 5; COMMIT;
Create a definer rights function that accesses the test tables. The return value from the function includes information about the calling user (CallUser), the user whose privileges are currently active (PrivUser), the number of rows in the TAB1
table (T1Count) and the number of rows in the TAB2
table (T2Count). Since this is a definer rights function we know access to both tables will be possible.
CONN cbac_user_1/cbac_user_1@pdb1 CREATE OR REPLACE FUNCTION get_count_definer RETURN VARCHAR2 AUTHID DEFINER AS l_count1 NUMBER; l_count2 NUMBER; l_return VARCHAR2(32767); BEGIN SELECT COUNT(*) INTO l_count1 FROM cbac_user_1.tab1; SELECT COUNT(*) INTO l_count2 FROM cbac_user_1.tab2; l_return := 'CallUser=' || USER || ' PrivUser=' || SYS_CONTEXT('userenv', 'CURRENT_USER') || ' T1Count=' || l_count1 || ' T2Count=' || l_count2; RETURN l_return; END; /
Create a similar function, but this time make it invoker rights. Let's assume access to TAB1
is mandatory, but access to TAB2
is optional, hence the additional exception handler.
CREATE OR REPLACE FUNCTION get_count_invoker RETURN VARCHAR2 AUTHID CURRENT_USER AS l_count1 NUMBER; l_count2 NUMBER; l_return VARCHAR2(32767); BEGIN SELECT COUNT(*) INTO l_count1 FROM cbac_user_1.tab1; BEGIN SELECT COUNT(*) INTO l_count2 FROM cbac_user_1.tab2; EXCEPTION WHEN OTHERS THEN l_count2 := -1; END; l_return := 'CallUser=' || USER || ' PrivUser=' || SYS_CONTEXT('userenv', 'CURRENT_USER') || ' T1Count=' || l_count1 || ' T2Count=' || l_count2; RETURN l_return; END; /
Grant access to both functions to the CBAC_USER_2
user.
GRANT EXECUTE ON get_count_definer TO cbac_user_2; GRANT EXECUTE ON get_count_invoker TO cbac_user_2;
Connect to the CBAC_USER_2
user and attempt to use the functions.
CONN cbac_user_2/cbac_user_2@pdb1 SELECT cbac_user_1.get_count_definer FROM dual; GET_COUNT_DEFINER ---------------------------------------------------------------------------------------------------- CallUser=CBAC_USER_2 PrivUser=CBAC_USER_1 T1Count=5 T2Count=5 1 row selected. SQL> SELECT cbac_user_1.get_count_invoker FROM dual; SELECT cbac_user_1.get_count_invoker FROM dual * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "CBAC_USER_1.GET_COUNT_INVOKER", line 9 SQL>
The definer rights function worked as expected. Notice that although the current user is CBAC_USER_2
, the procedure is running with the privileges of the CBAC_USER_1
user, hence it has access to both test tables. The CBAC_USER_2
user currently has no privileges on the test tables, so the invoker rights function fails as expected. Notice, we have not exposed any of the test tables directly to the CBAC_USER_2
user.
SELECT * FROM cbac_user_1.tab1; SELECT * FROM cbac_user_1.tab1 * ERROR at line 1: ORA-00942: table or view does not exist SQL>
Prior to 12c our only option was to grant privilege on the test table(s) to the CBAC_USER_2
user, either directly or via a role. The following role only grants access to the mandatory table (TAB1
).
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; DROP ROLE cbac_role; CREATE ROLE cbac_role; GRANT cbac_role TO cbac_user_1, cbac_user_2; GRANT SELECT ON cbac_user_1.tab1 TO cbac_role;
With the role granted to the CBAC_USER_2
user, we can test the functions again. The invoker rights function now works as expected, using the privileges granted to the current user.
CONN cbac_user_2/cbac_user_2@pdb1 SELECT cbac_user_1.get_count_definer FROM dual; GET_COUNT_DEFINER ---------------------------------------------------------------------------------------------------- CallUser=CBAC_USER_2 PrivUser=CBAC_USER_1 T1Count=5 T2Count=5 1 row selected. SQL> SELECT cbac_user_1.get_count_invoker FROM dual; GET_COUNT_INVOKER ---------------------------------------------------------------------------------------------------- CallUser=CBAC_USER_2 PrivUser=CBAC_USER_2 T1Count=5 T2Count=-1 1 row selected. SQL>
The problem is, we have exposed the TAB1
table to the CBAC_USER_2
user.
CONN cbac_user_2/cbac_user_2@pdb1 SELECT * FROM cbac_user_1.tab1; ID ---------- 1 2 3 4 5 5 rows selected. SQL>
With the 12c code based access control functionality, we can make sure the invoker rights function runs with any mandatory privileges, regardless of the calling user.
Revoke the role from the CBAC_USER_2
user and instead grant it against the function.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; REVOKE cbac_role FROM cbac_user_2; GRANT cbac_role TO FUNCTION cbac_user_1.get_count_invoker;
The CBAC_USER_2
user still gets the expected results from the invoker rights function, but we have no longer exposed the TAB1
table to the calling user.
CONN cbac_user_2/cbac_user_2@pdb1 SELECT cbac_user_1.get_count_invoker FROM dual; GET_COUNT_INVOKER ---------------------------------------------------------------------------------------------------- User=CBAC_USER_2 Priv User=CBAC_USER_2 T1 Count=5 T2 Count=-1 1 row selected. SQL> SELECT * FROM cbac_user_1.tab1; SELECT * FROM cbac_user_1.tab1 * ERROR at line 1: ORA-00942: table or view does not exist SQL>
Notice we have not revoked the role from the owner of the PL/SQL, only the user that is calling it. In order to grant the role to the code, the owner of the code must also be granted that role.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; -- Demonstrate the requirement that code owner must be granted the role also. REVOKE cbac_role FROM cbac_user_1; GRANT cbac_role TO FUNCTION cbac_user_1.get_count_invoker; GRANT cbac_role TO FUNCTION cbac_user_1.get_count_invoker * ERROR at line 1: ORA-01924: role 'CBAC_ROLE' not granted or does not exist SQL> -- Repair what we just broke. GRANT cbac_role TO cbac_user_1; GRANT cbac_role TO FUNCTION cbac_user_1.get_count_invoker;
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/12c/code-based-access-control-12cr1