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.

CREATE OR REPLACE PACKAGE "SECURITY_POLICIES" AS

-- context management functions

procedure SET_SEARCH_CONTEXT;

procedure CLEAR_SEARCH_CONTEXT;


-- security policies

function PERSONS_POLICY
( schema IN VARCHAR2
, tab IN VARCHAR2
) RETURN VARCHAR2;

END SECURITY_POLICIES;
/

CREATE OR REPLACE PACKAGE BODY "SECURITY_POLICIES" AS

procedure SET_SEARCH_CONTEXT
AS
BEGIN
dbms_session.set_context('security_ctx', 'searchcontext', 'SEARCH');
END SET_SEARCH_CONTEXT;

procedure CLEAR_SEARCH_CONTEXT as
BEGIN
dbms_session.clear_context('security_ctx', NULL, 'searchcontext');
NULL;
end CLEAR_SEARCH_CONTEXT;
function PERSONS_POLICY
( schema IN VARCHAR2
, tab IN VARCHAR2
) RETURN VARCHAR2 AS

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

begin

/* 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
if v('VPD_SEARCH_OVERRIDE_FLAG') = 'SEARCH' then
out_string := out_string || ' or 1=1 ';
end if;

return out_string;
END persons_policy;

END SECURITY_POLICIES;
/

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.

prompt