Virtual Private database of Oracle 10g new features

Five types of policies, column-related policies, and column masks make VPD a more powerful tool in the DBA's security toolbox

The Virtual Private database (VPD), also known as fine-grained access control, provides powerful row-level security features. It was launched in Oracle8i and has been widely welcomed and used in a variety of applications, from educational software to financial services.

VPD's approach is to provide a partial view of the table to the user based on a defined set of criteria by transparently changing requests for data. At run time, all queries append predicates to filter out rows that are allowed to be seen by the user. For example, if the user is allowed to view only account manager SCOTT's account, the VPD setting automatically queries:

SELECT * from Accounts;

Rewrite as:

SELECT * from Accounts

where am_name = ' SCOTT ';

The DBA has set up a security policy on the table ACCOUNTS. The policy has an associated function called the policy function, which returns a string to use as a predicate where am_name = ' SCOTT '. If you are unfamiliar with the full functionality of this feature, I recommend that you read Oracle magazine's article "Use VPD to keep information private".

Policy type

The repetitive analysis required to generate a predicate is an overhead that can be trimmed in some cases. For example, in most cases the predicate is not static as Am_name = ' SCOTT ', and it may be more dynamic based on the user's identity, the user's level of authority, and the account manager is reporting to. The string created and returned by the policy function can be very dynamic, and to ensure its outcome, Oracle must rerun the policy function each time, wasting resources and reducing performance. In this type of policy, the predicate can be very different each time it is executed, which is called a "dynamic" policy and has been provided in the Oracle9i database and in previous versions.

In addition to retaining dynamic policies, Oracle database 10g has introduced several new types of policies based on predicate constructs, providing better control over performance: Context_sensitive, Shared_context_sensitive, Shared_ static and Static. Now, let's look at the meaning of each policy type and how to use them in the right situations.

Dynamic policy. To maintain backward compatibility, the default policy type in 10g is "dynamic"-as in oracle9i. In this case, the policy function is evaluated each time the table is accessed for each row and for each user. Let's analyze the policy predicate in detail:

where am_name = ' SCOTT '

Ignoring the WHERE clause, the predicate has two different parts: the portion before the equality operator (Am_name) and the part of the equality operator (' SCOTT '). In most cases, the latter section is more like a variable because it is provided by the user's data (the value is ' Scott ' if the user is Scott). The portion preceding the equal sign is static. Therefore, even if a function does not have to work out the value of a policy function for each row to generate the appropriate predicate, performance can be improved by understanding the static of the previous section and the dynamics of the latter part. In 10g, you can use the "context_sensitive" type of policy as a parameter in the Dbms_rls.add_policy call to implement this method:

Policy_type => dbms_rls.context_sensitive

In another example, we have a table called ACCOUNTS, which has several columns, one of which is BALANCE, which represents the account balance. Suppose a user is allowed to view accounts below a specific balance that is determined by the application context. We do not fix this balance value in the policy function, but 3 is determined according to the application context, such as:

　　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;