DBA Hub

📋Steps in this guide1/6

PL/SQL White Lists Using the ACCESSIBLE BY Clause in Oracle Database 12c Release 1 (12.1)

Use the ACCESSIBLE BY clause to create white lists, adding an extra layer of security to your PL/SQL objects.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Setup

The following examples will use the two database users defined below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
CONN sys/password@pdb1 AS SYSDBA

CREATE USER test1 IDENTIFIED BY test1;
GRANT CREATE SESSION, CREATE PROCEDURE, CREATE TABLE TO test1;

CREATE USER test2 IDENTIFIED BY test2;
GRANT CREATE SESSION, CREATE PROCEDURE TO test2;
2

Basic Usage

The procedure below includes an clause, indicating it can only be called by an object called . Notice we have not created the procedure yet, but no error is produced. This is because the objects referenced by the clause are not checked at compile time. Only the syntax of the clause is checked. We can create the procedure and use it to call the procedure. If we attempt to call the procedure directly, we get an error. If we try to create a new object that references the procedure and the new object is not in the white list, we get a compilation error.

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
CONN test1/test1@pdb1

CREATE OR REPLACE PROCEDURE protected_proc
ACCESSIBLE BY (calling_proc)
AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/

Procedure created.

SQL>

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : calling_proc');
  protected_proc;
END;
/

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST1 : calling_proc
TEST1 : protected_proc

PL/SQL procedure successfully completed.

SQL>

SQL> EXEC protected_proc;
BEGIN protected_proc; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object PROTECTED_PROC
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

CREATE OR REPLACE PROCEDURE another_calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : another_calling_proc');
  protected_proc;
END;
/
Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE ANOTHER_CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3	 PL/SQL: Statement ignored
4/3	 PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>
3

Cross-Schema Usage

If no schema is explicitly mentioned in the white list, it is assumed the object listed is in the same schema as the object with the clause. For example, if we switch to another user and create a procedure called that accesses , it will not work. For this to be successful, we must add the reference into the white list using the fully qualified object name. The example below includes an object reference without a schema prefix, signifying current schema (TEST1) and one with an explicit schema reference. Now, we can reference and execute the procedure from the other 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
CONN test1/test1@pdb1
GRANT EXECUTE ON protected_proc TO test2;

Grant succeeded.

SQL> 


CONN test2/test2@pdb1

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST2 : calling_proc');
  test1.protected_proc;
END;
/

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3	 PL/SQL: Statement ignored
4/3	 PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>

CONN test1/test1@pdb1

CREATE OR REPLACE PROCEDURE protected_proc
ACCESSIBLE BY (calling_proc, test2.calling_proc)
AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/

Procedure created.

SQL>

CONN test2/test2@pdb1

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST2 : calling_proc');
  test1.protected_proc;
END;
/

Procedure created.

SQL>


SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST2 : calling_proc
TEST1 : protected_proc

PL/SQL procedure successfully completed.

SQL>
4

Unit Type

The examples so far have not specified the unit type (object type) when defining the white list references, which means any compatible object type with the correct name in the correct schema will pass the white list test. If we want to make the test more stringent, we can specify not just the name, but the unit type also. Some examples are shown below. There seem to be some discrepancies about which unit types can access PL/SQL objects that use the clause. The New Features Guide suggests tables, indexes and views can also be referenced in the white list, but the unit types , and are not allowed. The following example shows that function-based indexes and views do not work against a function using the clause.

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
ACCESSIBLE BY (PACKAGE calling_pkg)
ACCESSIBLE BY (PROCEDURE calling_proc)
ACCESSIBLE BY (FUNCTION calling_func)
ACCESSIBLE BY (TYPE calling_type)
ACCESSIBLE BY (TRIGGER calling_trg)

CONN test1/test1@pdb1

CREATE OR REPLACE FUNCTION protected_func (id IN NUMBER)
  RETURN NUMBER
  ACCESSIBLE BY (t1_fbi, t1_vw)
AS
BEGIN
  RETURN id;
END;
/

CREATE TABLE t1 (
  id NUMBER
);


SQL> CREATE INDEX t1_fbi ON t1(protected_func(id));
CREATE INDEX t1_fbi ON t1(protected_func(id))
                          *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC

SQL>


-- Views are created normally, but fail when run.
CREATE OR REPLACE VIEW t1_vw AS
SELECT protected_func(id) AS id_vw
FROM   t1;

View created.

SQL> SELECT * FROM t1_vw;
SELECT * FROM t1_vw
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC

SQL>
5

Package White Lists

In Oracle 12.1 the clause is only valid at the top-level of the package specification. It can not be applied to individual packaged procedures, functions or types within the package. The following example shows a white list applied to a package specification. Trying to apply the white list to the packaged procedure, rather than the top-level package, results in an error. The is not valid in the package body. It can only be defined in the package specification.

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
CREATE OR REPLACE PACKAGE protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc;
END;
/

Package created.

SQL>

CREATE OR REPLACE PACKAGE protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc);
END;
/

Warning: Package created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE PROTECTED_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0	 PLS-00157: Only schema-level programs allow ACCESSIBLE BY
SQL>

CREATE OR REPLACE PACKAGE protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc;
END;
/

Package created.

SQL>


CREATE OR REPLACE PACKAGE BODY protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc AS
  BEGIN
    NULL;
  END;
END;
/

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY PROTECTED_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3	 PLS-00103: Encountered the symbol "ACCESSIBLE" when expecting one
	 of the following:
	 is as compress compiled wrapped

SQL>
6

Package White Lists (12.2 Update)

In Oracle database 12.1 it was only possible to use a white list for a whole package. In Oracle 12.2 this limitation is no longer present, making the concept of white lists for packages much more granular. It is now possible to white list individual subprograms or a package. For more information see: - ACCESSIBLE BY Clause - CREATE PACKAGE - CREATE PROCEDURE - CREATE FUNCTION - CREATE TYPE 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
CREATE OR REPLACE PACKAGE protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc);

  FUNCTION protected_func RETURN NUMBER
    ACCESSIBLE BY (PROCEDURE calling_func);
END;
/

CREATE OR REPLACE PACKAGE BODY protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc)
  AS
  BEGIN
    NULL;
  END;

  FUNCTION protected_func RETURN NUMBER
    ACCESSIBLE BY (PROCEDURE calling_func)
  AS
  BEGIN
    RETURN NULL;
  END;
END;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!