PL/SQL White Lists

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

The ACCESSIBLE BY clause can be added to packages, procedures, functions and types to specify which objects are able to reference the PL/SQL object directly. This ability to create so called white lists is a new feature introduced in Oracle Database 12c Release 1 (12.1) to allow you to add an extra layer of security to your PL/SQL objects.

• Setup
• Basic Usage
• Cross-Schema Usage
• Unit Type
• Package White Lists
• Package White Lists (12.2 Update)

Setup

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

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;

Basic Usage

The procedure below includes an ACCESSIBLE BY clause, indicating it can only be called by an object called calling_proc.

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>

Notice we have not created the calling_proc procedure yet, but no error is produced. This is because the objects referenced by the ACCESSIBLE BY clause are not checked at compile time. Only the syntax of the clause is checked.
We can create the calling_proc procedure and use it to call the protected_proc procedure.

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>

If we attempt to call the protected_proc procedure directly, we get an error.

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>

If we try to create a new object that references the protected_proc procedure and the new object is not in the white list, we get a compilation error.

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>

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 ACCESSIBLE BY clause. For example, if we switch to another user and create a procedure called calling_proc that accesses test1.protected_proc, it will not work.

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>

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.

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>

Now, we can reference and execute the protected_proc procedure from the other user.

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>

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.

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)

There seem to be some discrepancies about which unit types can access PL/SQL objects that use the ACCESSIBLE BY clause. The New Features Guide suggests tables, indexes and views can also be referenced in the white list, but the unit types TABLE, INDEX and VIEW are not allowed. The following example shows that function-based indexes and views do not work against a function using the ACCESSIBLE BY clause.

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>

Package White Lists

In Oracle 12.1 the ACCESSIBLE BY 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.

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

Package created.

SQL>

Trying to apply the white list to the packaged procedure, rather than the top-level package, results in an error.

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>

The ACCESSIBLE CLAUSE is not valid in the package body. It can only be defined in the package specification.

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>

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.

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;
/