Risk
8/1/2008
00:00 AM
Commentary
Commentary
Commentary
Connect Directly
RSS
E-Mail
50%
50%

VPD and Rule-Based Data Access Control

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

Comment  | 
Print  | 
More Insights
Register for Dark Reading Newsletters
White Papers
Flash Poll
Current Issue
Cartoon
Video
Slideshows
Twitter Feed
Dark Reading - Bug Report
Bug Report
Enterprise Vulnerabilities
From DHS/US-CERT's National Vulnerability Database
CVE-2013-6117
Published: 2014-07-11
Dahua DVR 2.608.0000.0 and 2.608.GV00.0 allows remote attackers to bypass authentication and obtain sensitive information including user credentials, change user passwords, clear log files, and perform other actions via a request to TCP port 37777.

CVE-2014-0174
Published: 2014-07-11
Cumin (aka MRG Management Console), as used in Red Hat Enterprise MRG 2.5, does not include the HTTPOnly flag in a Set-Cookie header for the session cookie, which makes it easier for remote attackers to obtain potentially sensitive information via script access to this cookie.

CVE-2014-3485
Published: 2014-07-11
The REST API in the ovirt-engine in oVirt, as used in Red Hat Enterprise Virtualization (rhevm) 3.4, allows remote authenticated users to read arbitrary files and have other unspecified impact via unknown vectors, related to an XML External Entity (XXE) issue.

CVE-2014-3499
Published: 2014-07-11
Docker 1.0.0 uses world-readable and world-writable permissions on the management socket, which allows local users to gain privileges via unspecified vectors.

CVE-2014-3503
Published: 2014-07-11
Apache Syncope 1.1.x before 1.1.8 uses weak random values to generate passwords, which makes it easier for remote attackers to guess the password via a brute force attack.

Best of the Web
Dark Reading Radio
Archived Dark Reading Radio
Marilyn Cohodas and her guests look at the evolving nature of the relationship between CIO and CSO.