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

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

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

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