Channels ▼

Community Voices

Dr. Dobb's Bloggers

VPD and Rule-Based Data Access Control

August 01, 2008

I build Oracle-based applications that protect and control access to selected data elements, generally the SSN as I have blogged about previously. One of the ongoing challenges I have is dealing with who owns and who can access/update the SSN when there can be multiple systems and administrators each in their own virutal envrionment within a single database.

For instance, for a university environment, HR data including the SSN can be viewed and updated by the HR Administrator, but the Student System Administrator generally has read only access and cannot view the SSN. The vice versa is also true. Security models get really interesting when data is affiliated with both HR and Student, but that is a future story requiring many beers, but it is solvable.

To achieve rule driven data access control, I use Oracle Virtual Private Database (VPD) functionality that is built into the Enterprise Edition of the Oracle database. In a nutshell, VPD is achieved though a security function associated with a table with a security policy that modifies the where clause using the business rules in the security function to control if a row is returned. The policy can also set access control to return a restricted row and blank the controlled column when access is disallowed. There is a lot of background and example on VPD on Oracle's OTN.

The advantage of VPD is that the security policy is applied uniformly to all table access at the database level. Applications can take advantage of VPD without modification for applications that retrieve and display data. The logic for selecting access to the data is in the security function.

The VPD security policy returns a string that is ‘1=1' when access is allowed and ‘1=2' when access is disallowed. The logic can perform multiple tests where the results are strung together with ‘or'. The resulting string is then applied to the DML to determine data access and interpreted as ‘existing where clause' and VPD clause. Looks strange but works great. For programmatic business rules, many of my rules look up a code using criteria based on user roles then equates that to the same code provided in the data with the resulting logic being ‘code=code'.

The challenge I ran into recently was that the SSN is used for identifying persons but searching the SSN in an active VPD environment only returns the subset of records allowed by the VPD for the user performing the search. What this means is that a student who is being also converted to an employee when searched using SSN would not be found by an HR Administrator while the person's data is clearly in the system and accessible by the Student Administrator. If the search is done using name only, the person is found.

To get around this, the security functions  were altered to add additional logic to remove VPD restrictions when performing searches only. I have two scenarios - procedures that set this access using a database context and user applications using Apex that access using an Apex Application Item. In the case of the procedure, the context is set by the calling procedure then retrieved by the security function when the data is accessed. For Apex, the application item has a value set and the security function retrieves the value using the ‘v' function. I set the context for search, call the functionality, then clear the search context.

I included some of my code below for example. All the code was simplified to illustrate my points but is otherwise the real code I am using. This first block shows the package that defines both the context functionality and the security function.


-- context management functions



-- security policies

( schema IN VARCHAR2



dbms_session.set_context('security_ctx', 'searchcontext', 'SEARCH');

dbms_session.clear_context('security_ctx', NULL, 'searchcontext');

( schema IN VARCHAR2

out_string varchar2(2000) default '1=2 ';
-- out_string will be the return value.
-- It is initialized to '1=2' because 'WHERE 1=2' means
-- 'Nothing to access' and this can be combined with
-- other conditions by OR


/* other data and context-sensitive VPD logic
all of which appends to the out_string variable
' or 1=1 ' to allow access to the row or column or
' or 1=2 ' to disallow access to the row or column
based on data-driven business rules.

-- search override flag. Only invoked for search so that SSN can be included in search criteria
if SYS_CONTEXT('security_ctx', 'searchcontext') = 'SEARCH' then
out_string := out_string || ' or 1=1 ';
end if;

-- search override flag for application searches
out_string := out_string || ' or 1=1 ';
end if;

return out_string;
END persons_policy;


Setting either the context value searchcontext or the Apex application item VPD_SEARCH_OVERRIDE_FLAG to ‘SEARCH' is then used by the security function to return all rows.

For the procedure, the code is modified to call the package functions to set and clear the context. This is shown in below, again simplified for clarity. Prior to modification, this code would not correctly find persons when the VPD was set and the search criteria included the SSN.


Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.