PL/SQL applications consist – generally – of many procedures, functions and packages. A group of them can be invoked directly by applications as entry points of their user requirements while others are invoked by other packages as part of application business design.

Before Oracle 12c – in terms of database security – there was no difference between these two groups, and a granted user was able to execute any of these many units irrestrictively.

Oracle 12c introduces a new optional ACCESSIBLE BY clause that enables you to prevent package executions from outside the business logic, specifying a whitelist of predefined known elements that can only access a specific PL/SQL unit.

In order to show how this new clause works, let’s create the following PL/SQL functions:

  1. “granted_function” which will be created using the new accessible_by clause.

  2. “known_function” which will be granted to invoke “granted_function”.

  3. “unknown_function” which will not have permissions to invoke “granted_function”.

Let’s code “granted_function” as follows:

SQL> CREATE OR REPLACE FUNCTION granted_function
  2  RETURN VARCHAR2
  3    ACCESSIBLE BY
  4    (FUNCTION known_function,
  5     PROCEDURE known_procedure
  6    )
  7  IS
  8  BEGIN
  9    RETURN 'Execution Granted!';
 10  END;
 11  /

Function created.

Now, code “known_function” as follows to make use of the “granted_function”:

SQL> CREATE OR REPLACE FUNCTION known_function
  2  RETURN VARCHAR2
  3  IS
  4    received VARCHAR2(50);
  5  BEGIN
  6    received := granted_function();
  7    DBMS_OUTPUT.PUT_LINE('Received: ' || received);
  8    RETURN '';
  9  END;
 10  /

Function created.

We can now execute known_function() to validate if “granted_function()” is accessible:

SQL> SET SERVEROUTPUT ON
SQL> SELECT known_function() FROM dual;

KNOWN_FUNCTION()                                                                
-----------------------------------------------------------------
Received: Execution Granted!

As expected, granted_function() can be invoked by known_function() successfully.

But, what happens if another PL/SQL unit tries to use granted_function() without being defined in the accessible_by clause?

Let’s see. Code the following “unkown_function()” as follows:

SQL> CREATE OR REPLACE FUNCTION unknown_function
  2  RETURN VARCHAR2
  3  IS
  4    received VARCHAR2(50);
  5  BEGIN
  6    received := granted_function();
  7    DBMS_OUTPUT.PUT_LINE('Received: ' || received);
  8    RETURN '';
  9  END;
 10  /

Warning: Function created with compilation errors.

This time, a warning message will appear indicating that was not possible to perform the “create function” operation.

 

Execute “show errors” command to display further information about the warning received:

SQL> SHOW ERRORS
Errors for FUNCTION UNKNOWN_FUNCTION:

LINE/COL ERROR                                                                  
-------- -----------------------------------------------------------------      
6/4      PL/SQL: Statement ignored                                              
6/16     PLS-00904: insufficient privilege to access object                     
         GRANTED_FUNCTION

And as expected, we have no permissions to access granted_function() from this PL/SQL unit.

Note how not only database security was significantly improved using the new accessible_by clause, but also business logic was improved too preventing unwanted code access.

Link to script that contains the examples in this post.


1 Comment