Virtual Private Database

Virtual Private Database (VPD), also known as Fine Grained Access Control, provides powerful row-level security capabilities. Introduced in Oracle8i, it has become widely popular and can be found in a variety of applications ranging from education software to financial services.

VPD works by transparently modifying requests for data to present a partial view of the tables to the users based on a set of defined criteria. During runtime, predicates are appended to all the queries to filter any rows the user is supposed to see. For example, if the user is supposed to see only accounts of account manager SCOTT, the VPD setup automatically rewrites the query:

select * from accounts;

to:

select * from accounts
where am_name = 'SCOTT';

The DBA sets a security policy on the table ACCOUNTS. The policy has an associated function called policy function, which returns the string where am_name = 'SCOTT', which is applied as a predicate. If you are not familiar with the full functionality of the feature, I encourage you to read the Oracle Magazine article "Keeping Information Private with VPD."

Policy Types

In addition to retaining dynamic policy, Oracle Database 10g introduces several new types of policies based on how the predicate is constructed providing better controls for improving performance: context_sensitive, shared_context_sensitive, shared_static, and static. Now, let's what each policy type means and how to use it in appropriate situations.

Dynamic Policy. To retain backward compatibility, the default policy type in 10g is "dynamic"—just as it was in Oracle9i. In this case, the policy function is re-evaluated each time the table is accessed, for each row and for every user. Let's examine the policy predicate closely:
where am_name = 'SCOTT'

Ignoring the where clause, the predicate has two distinct parts: the portion before the equality operator (am_name) and the one after it ('SCOTT'). In most cases, the one after is more like a variable in that it is supplied from the user's data (if the user is SCOTT, the value would be 'SCOTT'.) The part before the equality sign is static. So, even though the function does have to evaluate the policy function for each row to generate the appropriate predicate, the knowledge about the static-ness of the before-part and dynamic-ness of the after-part can be used to improve performance. This approach is possible in 10g using a policy of type "context_sensitive" as a parameter in the dbms_rls.add_policy call:
policy_type => dbms_rls.context_sensitive

In another example scenario, we have a table called ACCOUNTS with several columns, one of which is BALANCE, indicating the account balance. Let's assume that a user is allowed to view accounts below a certain balance that is determined by an application context. Instead of hard-coding this balance amount in a policy function, we can use an application context as in:

create or replace vpd_pol_func
(
p_schema in varchar2,
p_table in varchar2
)
return varchar2
is
begin
return 'balance < sys_context(''vpdctx'', ''maxbal'')';
end;

The attribute MAXBAL of the application context VPDCTX can be set earlier in the session and the function can simply get the value at the runtime.

Note the example carefully here. The predicate has two parts: the one before the less-than sign and the other after it. The one before, the word "balance," is a literal. The one after is more or less static because the application context variable is constant until it is changed. If the application context attribute does not change, the entire predicate is constant, and hence the function need not be re-executed. Oracle Database 10g recognizes this fact for optimization if the policy type is defined as context sensitive. If no session context changes have occurred in the session, the function is not re-executed, significantly improving performance.

Static Policy. Sometimes a business operation may warrant a predicate that is more static. For instance, in the context-sensitive policy type example, we defined the maximum balance seen by a user as a variable. This approach is useful in the case of web applications where an Oracle userid is shared by many web users and based on their authority this variable (application context) is set by the application. Therefore web users TAO and KARTHIK, both connecting to the database as user APPUSER, may have two different values of the application context in their session. Here the value of MAXBAL is not tied to the Oracle userid, but rather to the individual session of TAO and KARTHIK.

In the static policy case the predicate is more predictable, as described below.

LORA and MICHELLE are account managers for Acme Bearings and Goldtone Bearings respectively. When they connect to the database, they use their own id and should only see the rows pertaining to them. In Lora's case, the predicate becomes where CUST_NAME = 'ACME'; for Michelle, where CUST_NAME = 'GOLDTONE'. Here the predicate is tied to their userids, and hence any session they create will always have the same value in the application context.

This fact can be exploited by 10g to cache the predicate in the SGA and reuse that in the session without ever re-executing the policy function. The policy function looks like this:

create or replace vpd_pol_func
(
p_schema in varchar2,
p_table in varchar2
)
return varchar2
is
begin
return 'cust_name = sys_context(''vpdctx'', ''cust_name'')';
end;

And the policy is defined as:

policy_type => dbms_rls.static

This approach ensures that the policy function is executed only once. Even if the application contexts are changed in the session, the function is never re-executed, making this process extremely fast.

Static policies are recommended for hosting your applications across several subscribers. In this case a single database has data for several users or subscribers. When each subscriber logs in, an after-logon trigger can set the application context to a value that is used in the policy function to very quickly generate a predicate.

However, defining a policy as static is also a double-edged sword. In the above example, we assumed that the value of the application context attribute VPDCTX.CUST_NAME does not change inside a session. What if that assumption is incorrect? If the value changes, the policy function will not be executed and therefore the new value will not be used in the predicate, returning wrong results! So, be very careful in defining a policy as static; you must be absolutely certain that the value will not change. If you can't make that assumption, better to define the policy as context sensitive instead.

Shared Policy Types. To reuse code and maximize the usage of parsed code, you might decide to use a common policy function for several tables. For instance, in the above example, we may have different tables for different types of accounts—SAVINGS and CHECKING—but the rule is still the same: users are restricted from seeing accounts with balances more than they are authorized for. This scenario calls for a single function used for policies on CHECKING and SAVINGS tables. The policy is created as context_sensitive.

Suppose this is the sequence of events:

1. Session connected
2. Application context is set
3. select * from savings;
4. select * from checking;

Even though the application context does not change between steps 3 and 4, the policy function will be re-executed, simply because the tables selected are different now. This is not desirable, as the policy function is the same and there is no need to re-execute the function.

New in 10g is the ability to share a policy across objects. In the above example, you would define the policy type of these policies as: policy_type => dbms_rls.shared_context_sensitive

Declaring the policies as "shared" improves performance by not executing the function again in the cases as shown above.

Selective Columns

ACCTNO ACCT_NAME    BALANCE
------ ------------ -------
     1 BILL CAMP    1000
     2 TOM CONNOPHY 2000
     3 ISRAEL D     1500    
	 

she sees: 
ACCTNO ACCT_NAME    BALANCE
------ ------------ -------
     1 BILL CAMP    1000
     3 ISRAEL D     1500  
	 
	 

which simply counts the number of records from the table, the output is two, not three. However, here we may decide to relax the security policy a bit. In this query Michelle can't view confidential data such as account balance; she merely counts all the records in the table. Consistent with the security policy, we may allow this query to count all the records whether or not she is allowed to see them. If this is the requirement, another parameter in the call to dbms_rls.add_policy in 10g allows that function:

sec_relevant_cols => 'BALANCE'

Now when the user selects the column BALANCE, either explicitly or implicitly as in select *, the VPD policy will kick in to restrict the rows. Otherwise all rows of the table will be selected, as in the query where the user has selected only the count of the total rows, not the column BALANCE. If the above parameter is set as shown, then the query

select count(*) from accounts;

will show three columns, not two. But the query:
select * from accounts;

will still return only two records, as expected.

Column Masking

Michelle is not supposed to see accounts with balances over 1,600. When she issues a query like the following: select * from accounts;

she would have seen only two rows, acctnos 1 and 3. But, instead, we may want her to see:

ACCTNO ACCT_NAME    BALANCE
------ ------------ -------
     1 BILL CAMP    1000
     2 TOM CONNOPHY 
     3 ISRAEL D     1500