@Oracle 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

PL/SQL Security Coding Practices. Introduction to a better architecture part 1.

I have been seeing this database architecture for over thirty years and it’s high time we stopped using it. Before I go too far, let me tell you I get it, you have pressure to get the application out the door and working in a defined timeframe. I still design and develop systems and the pressure to take shortcuts can be great. This short cut is a security killer.

So what have we been doing wrong for all these decades? Put all of the database objects and application code into once schema. This is just a bad idea all around. All it takes is one security bug and the bad guy owns your database. You might as well, put pretty gold wrapping paper with a bow around it and write the bad guy a gift card. If you come to any of my talks, I’ll be happy to demonstrate owning a database, including all your source code and all your data in just a couple of easy commands. But because this is not intended on being a lesson in hacking a database, I wont go into it here.

The power and security configuration of using an API (1)

There is an architecture, that will drastically improve the security of your database. By segmenting your application code from your data and use an API to access the data. Oracle 12c has several PL/SQL enhancements that will make your code much more secure. Oracle 12c PL/SQL now allows you to assign roles to packages, procedures and functions (But you should only be using packages). PL/SQL also now allows you to white list what can execute code. For years, we granted execute to a user, but now you can define what PL/SQL package can call another PL/SQL package using the accessible by clause. We are going to leverage these new features along with authid to define a trusted path that is controllable, fast and secure.

My next several post will move through this architecture, and explaining how to implement it effectively.

The power and security configuration of using an API