You know the story. A group of blind men come across an elephant and each one feels a part of it. One touches the tusk and goes away thinking elephants are pointy, hard and sharp; another feels its side and is amazed at how wide and featureless elephants are; and one unfortunate man feels the elephant up its backside, and is probably somewhere right now scrubbing his hands with soap!
The story of the blind men and the elephant actually illustrates Virtual Private Databases perfectly. It is functionality that allows different users run the exact same query against a table and each come away with a different, tailored, resultset.
Being able to automatically sculpt the output of a query based on the identity of the end user or any other determinable variables is hugely useful – only limited by the imagination of the developer. Example: imagine you have a table containing staff details including sensitive information such as salaries, wouldn’t it be useful to apply a policy to that table that, while allowing all managers to query the table, will only allow each manager view the salaries of people in their own teams? Example: imagine you are happy to give your users access to a table – but only between 9 and 5 from Monday to Friday. Or perhaps, only when they are using a particular machine.
Mere role-based security using grants would wither in the face of these requirements; and while you may be able to code them into your front-end application, a wily user will then be able to bypass your security by logging onto SQL*Plus.
Virtual Private Databases (VPD) is technology that allows you to tack on an extra clause (an extra predicate) to any query that is run against a specified object, irrespective of the source of the query – a Forms application, an Apex application, SQL*Plus, TOAD or a reporting tool. It is also called Row-Level Security (RLS). It is also called Fine-Grained Access Control (FGAC). It has more nicknames than the rapper Sean Combs (aka Puff Daddy aka Puffy aka P Diddy aka Diddy).
RLS is usually accomplished with the aid of application contexts. Application contexts, which are outside the scope of this article, can be used to obtain defining attributes of a user’s session – from environmental attributes to developer-defined attributes set as part of a database logon trigger.
Row-Level Security is achieved using the DBMS_RLS package. Using the ADD_POLICY procedure in this package, you can add a ‘policy’ to a table. To do this you will need, obviously, to specify the object to which you wish to add the policy; you will need to specify the types of DML statements that should trigger your policy; and you will also need to identify a function your policy should use. This function must return a varchar2, the string that will be tacked on to the DML applied to your object.
|OBJECT_SCHEMA||VARCHAR2||The schema of the object to which you wish to apply the policy|
|OBJECT_NAME||VARCHAR2||The table, view, synonym to which you wish to apply the policy|
|POLICY_NAME||VARCHAR2||A name for your policy|
|FUNCTION_SCHEMA||VARCHAR2||The schema of the function that returns your predicate|
|POLICY_FUNCTION||VARCHAR2||The function that returns the predicate|
|STATEMENT_TYPES||VARCHAR2||Statement types to which the policy should apply: Any combination of SELECT, UPDATE, DELETE, INSERT, INDEX|
Functions that can be used as policy functions must obey certain rules. They must accept 2 parameters – the object schema and the object name – and return a varchar2. A simple example will illustrate this; let us assume we have been asked to ensure that the data in EMP is inaccessible outside of office hours.
FUNCTION work_hours_fnc (pObj_Schema IN VARCHAR2, pObj_Name IN VARCHAR2) RETURN VARCHAR2 is BEGIN If (to_char(sysdate,’DY’) in (‘SAT’,’SUN’) or to_char(sysdate,’HH24’) not between 9 and 17) then Return ‘1 = 2’; Else Return ‘1=1’; End if; END work_hours_fnc;
And now that we have our simple function, we can create a policy that will apply it to the EMP table.
The effect this will have is that every time a select statement is executed against the EMP table our function will be evaluated and an extra predicate will be added on to that query. If the following is run during office hours:
SELECT * FROM EMP;
The actual query that will be executed will be:
SELECT * FROM EMP WHERE 1 = 1;
However, if the exact same query is executed on a Sunday or on a weekday evening, the database will read it as:
SELECT * FROM EMP WHERE 1 = 2;
Which, of course, will result in no data being returned.
This simple example illustrates the power of fine-grained access control, and it should be obvious that the intricacy of the function can be enhanced to make it more, well, fine-grained. Characteristics of the end user can be examined and can be used to structure the data they have access to.
This article could end here and you would be suitably impressed with RLS. However, there is more. Sherlock Holmes once solved a crime because a dog did not bark: sometimes something that does not happen – or is not where you expect it to be – can reveal a lot. Imagine you have a table named MEDICAL_HISTORY and you apply a policy that allows end users select details for all people – except one. Would your end users not immediately deduce that that one person probably has some horrendous disease – adult bed-wetting or something?
The dbms_rls.add_policy procedure allows some parameters that tell the policy to only kick in when certain – presumably sensitive – columns are queried. If the end user queries other columns in that table their query will be fulfilled as normal and they would be none the wiser. But the instant they add a sensitive column to the query, the policy springs to life.
It gets better. Dbms_rls.add_policy gives you the option to return NULLs when named columns are queried. It is the perfect way to mask the contents of sensitive columns without withholding the whole record.
To accomplish this dbms_rls.add_policy accepts two further parameters – sec_relevant_cols and sec_relevant_cols_opt.
Sec_relevant_cols accepts a comma-separated list of columns. The policy will only kick in if any of these columns are selected by the user’s query.
Sec_relevant_cols_opt accepts a binary_integer. Pass it DBMS_RLS.ALL_ROWS and any columns passed to the sec_relevant_cols parameter will be masked, appearing as nulls in any affected queries.
Row-level security is a powerful tool that gives you the ability to practically give each type of user a customised view of your data. Because it works at the database level it is application-agnostic and cannot be easily circumvented. Bear in mind, however, that, once set up, policies work silently in the background; this often makes them easy to forget and therefore difficult to debug. This notwithstanding, it is immensely useful and your database will probably benefit from some implementation of it.