DBA Hub

📋Steps in this guide1/3

Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1 (12.1)

Discover how Oracle Database 12c allows you to grant roles directly to PL/SQL program units.

oracle 12cconfigurationintermediate
by OracleDba
12 views
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. Connect to the user and create two test tables. 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 table (T1Count) and the number of rows in the table (T2Count). Since this is a definer rights function we know access to both tables will be possible. Create a similar function, but this time make it invoker rights. Let's assume access to is mandatory, but access to is optional, hence the additional exception handler. Grant access to both functions to the user. Connect to the user and attempt to use the functions. The definer rights function worked as expected. Notice that although the current user is , the procedure is running with the privileges of the user, hence it has access to both test tables. The 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 user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
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;

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;

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 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 EXECUTE ON get_count_definer TO cbac_user_2;
GRANT EXECUTE ON get_count_invoker TO cbac_user_2;

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>

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 user, either directly or via a role. The following role only grants access to the mandatory table ( ). With the role granted to the user, we can test the functions again. The invoker rights function now works as expected, using the privileges granted to the current user. The problem is, we have exposed the table to the user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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;

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>

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 user and instead grant it against the function. The user still gets the expected results from the invoker rights function, but we have no longer exposed the table to the calling user. 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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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;

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>

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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!