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
Partner Perspectives
What's This?
In a digital world inundated with advanced security threats, Intel Security seeks to transform how we live and work to keep our information secure. Through hardware and software development, Intel Security delivers robust solutions that integrate security into every layer of every digital device. In combining the security expertise of McAfee with the innovation, performance, and trust of Intel, this vision becomes a reality.

As we rely on technology to enhance our everyday and business life, we must too consider the security of the intellectual property and confidential data that is housed on these devices. As we increase the number of devices we use, we increase the number of gateways and opportunity for security threats. Intel Security takes the “security connected” approach to ensure that every device is secure, and that all security solutions are seamlessly integrated.
Featured Writers
White Papers
Cartoon
Current Issue
Dark Reading's October Tech Digest
Fast data analysis can stymie attacks and strengthen enterprise security. Does your team have the data smarts?
Flash Poll
Video
Slideshows
Twitter Feed
Dark Reading - Bug Report
Bug Report
Enterprise Vulnerabilities
From DHS/US-CERT's National Vulnerability Database
CVE-2013-4594
Published: 2014-10-25
The Payment for Webform module 7.x-1.x before 7.x-1.5 for Drupal does not restrict access by anonymous users, which allows remote anonymous users to use the payment of other anonymous users when submitting a form that requires payment.

CVE-2014-0476
Published: 2014-10-25
The slapper function in chkrootkit before 0.50 does not properly quote file paths, which allows local users to execute arbitrary code via a Trojan horse executable. NOTE: this is only a vulnerability when /tmp is not mounted with the noexec option.

CVE-2014-1927
Published: 2014-10-25
The shell_quote function in python-gnupg 0.3.5 does not properly quote strings, which allows context-dependent attackers to execute arbitrary code via shell metacharacters in unspecified vectors, as demonstrated using "$(" command-substitution sequences, a different vulnerability than CVE-2014-1928....

CVE-2014-1928
Published: 2014-10-25
The shell_quote function in python-gnupg 0.3.5 does not properly escape characters, which allows context-dependent attackers to execute arbitrary code via shell metacharacters in unspecified vectors, as demonstrated using "\" (backslash) characters to form multi-command sequences, a different vulner...

CVE-2014-1929
Published: 2014-10-25
python-gnupg 0.3.5 and 0.3.6 allows context-dependent attackers to have an unspecified impact via vectors related to "option injection through positional arguments." NOTE: this vulnerability exists because of an incomplete fix for CVE-2013-7323.

Best of the Web
Dark Reading Radio
Archived Dark Reading Radio
Follow Dark Reading editors into the field as they talk with noted experts from the security world.