@Oracle 12.2.0.1 Cool new features to improve security. Part 1 Enhanced Whitelists PL/SQL

In Oracle 12.1 the ACCESSIBLE BY clause was introduced to the PL/SQL language. This gives the developer the ability mark a package, procedure, function, or type with what was allowed to call it. 12.2 gives us fine grained control over what can the specific functions and procedures in a package.

Here is what 12.1 gave us. As you can see in this example the package getEmpInfo and EmpMaint can both call the package emp_api. I love it, now we have a way to limit what can call a piece of code. But wait, in 12.2 it gets even better, look at example for 12.2

 1 CREATE OR REPLACE PACKAGE emp_api
 2 ACCESSIBLE BY (getEmpInfo, EmpMaint)
 3 AUTHID CURRENT_USER AS
 4         FUNCTION fGetEmpPhone( pFname IN VARCHAR2,
 5                                 pLname IN VARCHAR2)
 6         RETURN VARCHAR2;
 7 
 8         FUNCTION fGetEmpManager(pEmployeeId IN NUMBER) RETURN NUMBER;
 9 
10         FUNCTION fInsEmp(pFirstName IN VARCHAR2,
11                         pLastName IN VARCHAR2,
12                         pEmail IN VARCHAR2,
13                         pPhoneNumber IN VARCHAR2,
14                         pHireDate IN DATE,
15                         pJobId IN NUMBER,
16                         pSalary IN NUMBER,
17                         pCommissionPct IN NUMBER,
18                         pManagerId IN NUMBER,
19                         pDempartmentId IN NUMBER)
20         RETURN BOOLEAN;
21 
22         FUNCTION fDelEmp(pEmployeeId IN NUMBER)
23         RETURN BOOLEAN;
24 
25         FUNCTION fUpdateEmp(pEmployeeId IN NUMBER,
26                         pFirstName IN VARCHAR2,
27                         pLastName IN VARCHAR2,
28                         pEmail IN VARCHAR2,
29                         pPhoneNumber IN VARCHAR2,
30                         pHireDate IN DATE,
31                         pJobId IN NUMBER,
32                         pSalary IN NUMBER,
33                         pCommissionPct IN NUMBER,
34                         pManagerId IN NUMBER,
35                         pDempartmentId IN NUMBER)
36         RETURN BOOLEAN;
37 
38 END;

In 12.2 we now have fine grained control over what can call the individual functions and procedures in our package. In the emp_api package the package getEmpInfo can call the functions fGetEmpPhone and fGetEmpManager. The package EmpMaint can call the functions, fDelEmp, fInsEmp, and fUpdateEmp. Now we have fine grained control over what can call the functions and procedures in a specific package.

 1 create or replace PACKAGE emp_api
 2 AUTHID CURRENT_USER
 3 AS
 4         FUNCTION fGetEmpPhone(pFname IN VARCHAR2,
 5                               pLname IN VARCHAR2)
 6         RETURN VARCHAR2 ACCESSIBLE BY (PACKAGE getEmpInfo);
 7 
 8         FUNCTION fGetEmpManager(pEmployeeId IN NUMBER)
 9         RETURN NUMBER ACCESSIBLE BY (PACKAGE getEmpInfo);
10 
11         FUNCTION fInsEmp(pFirstName IN VARCHAR2,
12                         pLastName IN VARCHAR2,
13                         pEmail IN VARCHAR2,
14                         pPhoneNumber IN VARCHAR2,
15                         pHireDate IN DATE,
16                         pJobId IN NUMBER,
17                         pSalary IN NUMBER,
18                         pCommissionPct IN NUMBER,
19                         pManagerId IN NUMBER,
20                         pDempartmentId IN NUMBER)
21         RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint);
22 
23         FUNCTION fDelEmp(pEmployeeId IN NUMBER)
24         RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint);
25 
26         FUNCTION fUpdateEmp(pEmployeeId IN NUMBER,
27                 pFirstName IN VARCHAR2,
28                 pLastName IN VARCHAR2,
29                 pEmail IN VARCHAR2,
30                 pPhoneNumber IN VARCHAR2,
31                 pHireDate IN DATE,
32                 pJobId IN NUMBER,
33                 pSalary IN NUMBER,
34                 pCommissionPct IN NUMBER,
35                 pManagerId IN NUMBER,
36                 pDempartmentId IN NUMBER)
37         RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint);
38 
39 END;

Reference: http://docs.oracle.com/database/122/LNPLS/ACCESSIBLE-BY-clause.htm#LNPLS-GUID-9720619C-9862-4123-96E7-3E85F240FF36