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.
The ACCESSIBLE BY clause can appear in the following SQL statements :-
CREATE FUNCTION Statement
CREATE PROCEDURE Statement
CREATE PACKAGE Statement
CREATE PACKAGE BODY Statement
CREATE TYPE Statement
CREATE TYPE BODY Statement
ALTER TYPE Statement
SQL> CONN
Enter user-name: test1
Enter password:
Connected.
SQL> CREATE OR REPLACE PROCEDURE protected_proc
2 ACCESSIBLE BY (calling_proc)
3 AS
4 BEGIN
5 DBMS_OUTPUT.put_line('TEST1 : protected_proc');
6 END;
7 /
Procedure created.
/*
The procedure above includes an ACCESSIBLE BY clause, indicating it can only be called by an object called calling_proc.
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.
*/
SQL> CREATE OR REPLACE PROCEDURE calling_proc AS
2 BEGIN
3 DBMS_OUTPUT.put_line('TEST1 : calling_proc');
4 protected_proc;
5 END;
6 /
Procedure created.
SQL> EXEC calling_proc;
TEST1 : calling_proc
TEST1 : protected_proc
PL/SQL procedure successfully completed.
-- 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
--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.
SQL> CREATE OR REPLACE PROCEDURE another_calling_proc AS
2 BEGIN
3 DBMS_OUTPUT.put_line('TEST1 : another_calling_proc');
4 protected_proc;
5 END;
6 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERR
Errors for PROCEDURE ANOTHER_CALLING_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PLS-00904: insufficient privilege to access object PROTECTED_PROC
-- 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.
SQL> GRANT EXECUTE ON protected_proc TO test2;
Grant succeeded.
SQL> CONN
Enter user-name: test2
Enter password:
Connected.
SQL> CREATE OR REPLACE PROCEDURE calling_proc AS
2 BEGIN
3 DBMS_OUTPUT.put_line('TEST2 : calling_proc');
4 test1.protected_proc;
5 END;
6 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERR
Errors for PROCEDURE CALLING_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PLS-00904: insufficient privilege to access object PROTECTED_PROC
-- 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 (TEST2).
SQL> CONN
Enter user-name: test1
Enter password:
Connected.
SQL> CREATE OR REPLACE PROCEDURE protected_proc
2 ACCESSIBLE BY (calling_proc, test2.calling_proc)
3 AS
4 BEGIN
5 DBMS_OUTPUT.put_line('TEST1 : protected_proc');
6 END;
7 /
Procedure created.
SQL> CONN
Enter user-name: test2
Enter password:
Connected.
SQL> CREATE OR REPLACE PROCEDURE calling_proc AS
2 BEGIN
3 DBMS_OUTPUT.put_line('TEST2 : calling_proc');
4 test1.protected_proc;
5 END;
6 /
Procedure created.
SQL> EXEC calling_proc;
TEST2 : calling_proc
TEST1 : protected_proc
PL/SQL procedure successfully completed.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 object types can access PL/SQL objects that use the ACCESSIBLE BY clause.In fact tables, indexes and views can also be referenced in the white list, but the object 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.
SQL> CONN
Enter user-name: test1
Enter password:
Connected.
SQL> CREATE OR REPLACE FUNCTION protected_func (id IN NUMBER)
2 RETURN NUMBER
3 ACCESSIBLE BY (t1_fbi, t1_vw)
4 AS
5 BEGIN
6 RETURN id;
7 END;
8 /
Function created.
SQL> CREATE TABLE t1
2 (
3 id NUMBER
4 );
Table created.
SQL> CREATE INDEX t1_fbi ON t1(protected_func(id));
CREATE INDEX t1_fbi ON t1(protected_func(id))
*
ERROR at line 1:
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC
SQL> CREATE OR REPLACE VIEW t1_vw AS
2 SELECT protected_func(id) AS id_vw
3 FROM t1;
SELECT protected_func(id) AS id_vw
*
ERROR at line 2:
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC-- 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.
SQL> CONN
Enter user-name: test1
Enter password:
Connected.
SQL> CREATE OR REPLACE PACKAGE protected_pkg
2 ACCESSIBLE BY (PROCEDURE calling_proc)
3 AS
4 PROCEDURE protected_proc;
5 END;
6 /
Package created.
-- Trying to apply the white list to the packaged procedure, rather than the top-level package, results in an error.
SQL> CREATE OR REPLACE PACKAGE protected_pkg
2 AS
3 PROCEDURE protected_proc
4 ACCESSIBLE BY (PROCEDURE calling_proc);
5 END;
6 /
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
-- The ACCESSIBLE CLAUSE is not valid in the package body. It can only be defined in the package specification.
SQL> CREATE OR REPLACE PACKAGE protected_pkg
2 ACCESSIBLE BY (PROCEDURE calling_proc)
3 AS
4 PROCEDURE protected_proc;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY protected_pkg
2 ACCESSIBLE BY (PROCEDURE calling_proc)
3 AS
4 PROCEDURE protected_proc AS
5 BEGIN
6 NULL;
7 END;
8 END;
9 /
Warning: Package Body created with compilation errors.
SQL> SHOW ERR
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
-- 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.
SQL> CREATE OR REPLACE PACKAGE protected_pkg
2 AS
3 PROCEDURE protected_proc
4 ACCESSIBLE BY (PROCEDURE calling_proc);
5
6 FUNCTION protected_func RETURN NUMBER
7 ACCESSIBLE BY (PROCEDURE calling_func);
8 END;
9 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY protected_pkg
2 AS
3 PROCEDURE protected_proc
4 ACCESSIBLE BY (PROCEDURE calling_proc)
5 AS
6 BEGIN
7 NULL;
8 END;
9
10 FUNCTION protected_func RETURN NUMBER
11 ACCESSIBLE BY (PROCEDURE calling_func)
12 AS
13 BEGIN
14 RETURN NULL;
15 END;
16 END;
17 /
Package body created.
