Few words about Oracle VPD.

My last post has been about Oracle Database Vault which enables the security policy enforcement on the database objects level. You can easily restrict access to the table or the view in the context-based way, but ODV's mechanisms will not help you if you want to limit the access to the particular columns or even rows of the table. The fine-grained level of the access control is available within Virtual Private Database (VPD) feature which will be the topic of this post.

At the very beginning I have to mention the fact that people use to say that VPD is just dead, as too complex in the implementation and too much based on somehow ineffective PL/SQL development. On the other hand the same people use to presume that such technologies as Oracle Label Security (OLS) or brand new Oracle 12c Real Application Security (RAS) will just sweep out VPD from the market as too ancient and too much based on the development in contrast to the declarative approach of the modern security features. What is my opinion on this subject? I am pretty sure that Oracle VPD still should be better understood by the community of DBAs. It's because, in many security-related use cases, Oracle VPD still can be considered as the foundations for setup on-prem or within OPC cloud, as it is widely tested and quite well documented. But let's start from the basics...

What is Oracle VPD from the technical point of view?

Oracle VPD is a database security feature that is implemented using Oracle's Row-Level Security (RLS) package DBMS_RLS. As we have said it restricts rows or columns of data from objects accessible via SQL SELECT or DML statements. And this restriction is based on security criteria defined in the policy function, which is just PL/SQL code that you can write to enforce your security policy. After a table has been registered and is under policy control, when a SQL statement is issued against the table, Oracle executes the associated policy function. The function more or less append to the original SQL statements WHERE clause in the case of SELECT, UPDATE and DELETE statements. As a consequence Oracle does not parse and execute the original query and then filter the results during the fetch phase of SQL statement processing. Before that Oracle executes the policy function, modifies the original query, then it parses & executes effect of this modification. There is no need to explain, the results will  be different - limited set of rows or columns on query output.

Some simple example of VPD ...

The picture is worth a thousand words. So to understand VPD better, please look at the diagram below. As I am some kind of veteran on Electronic Health Record (EHR) market, I will propose the example from the medical market, where restricted access to the sensitive data about patient's health is just crucial thing. Let's imagine the PAC5.PATIENTS table which consists medical data of the patients visiting one of the medical clinic. We have developed the application which enables our doctors the access to the patient data. Let's assume that doctor need to have the access only to his own patients. Of course for that purpose we can enable security rules on application layer, but what if for some reporting reasons doctors would like to use just simple SQL*Plus or any kind of SQL*Net based applications? How to enforce the restriction? 

To make it possible first we need to create PL/SQL function patients_doc_id_rls which you can see on the right of a diagram. Then we need to execute DBMS_RLS.ADD_POLICY procedure, which will link the PL/SQL function,  PAC5.PATIENTS table and SELECT operation on it. Just after we have to enable the policy by executing DBMS_ADD.ENABLE_POLICY procedure. And that is all :-)

Simple? Of course this is just the beginning, but it shows some basic rules of Oracle VPD. Rest you need to read in docs if you have found my post enough interesting. :-)

That is all for tonight! Stay tuned.

Best,

Luke.