8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1 (12.1)
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:
- Some program units may require a mixture of mandatory and optional privileges. If the calling user is lacking those privileges considered mandatory, the code becomes useless.
- Since the calling user must have privileges on the objects referenced by the invoker rights program unit, either directly or via a role, this means those objects are exposed to the calling user. The calling user may then decide to use those objects for a different purpose, which was not originally intended.
- If an invoker rights program unit (prog2) is called from a definer rights program unit (prog1), then prog2 is run in the context of the owner of prog1, not the calling user.
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.
- Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c Release 1 (12.1) (INHERIT [ANY] PRIVILEGES)
- Control Invoker Rights Privileges in Views in Oracle Database 12c Release 1 (12.1) (BEQUEATH CURRENT_USER)
Defining the Problem
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>
Pre-12c Solution
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>
Code Based Access Control Solution
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:
- Using Code Based Access Control for Definer's Rights and Invoker's Rights
- Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c Release 1 (12.1) (INHERIT [ANY] PRIVILEGES)
- Control Invoker Rights Privileges in Views in Oracle Database 12c Release 1 (12.1) (BEQUEATH CURRENT_USER)
Hope this helps. Regards Tim...