Virtual Private Database (VPD) is the aggregation of server-enforced fine-grained access control and secure application context in the Oracle database. VPD enables you to build applications that enforce your security policies at the row level. When a user directly or indirectly accesses a table, a view, or a synonym associated with a VPD security policy, the server dynamically modifies the user's SQL statement. The modification is based on a WHERE clause returned by a function which implements the security policy. The database modifies the statement dynamically (transparently to the user) using any condition that can be expressed in, or returned by, a function.
The DBMS_RLS.ADD_POLICY has the new argument LONG_PREDICATE. Its default value is FALSE so that the policy function may return up to 4000 bytes of predicate length. Setting this value to TRUE allows the function to return up to 32 KB of predicate text string.
There is a new security policy on INDEX maintenance operations performed with the create index and alter index statements. This is important because users need full access to tables to create indexes. Consequently, a user who has privileges to maintain an index can see all the row data although the user does not have full table access under a regular query.
The execution of policy functions can consume a significant amount of system resources. If you can minimize the number of times that policy functions must execute, then you can optimize your database performance.
In previous releases, policies were dynamic by default. This means that the Oracle database executed the policy function for each DML statement. In addition to dynamic policies, Oracle Database 10g provides static and context-sensitive policies. These policy types provide a way to improve server performance because they do not always reexecute policy functions for each DML statement, and they can be shared across multiple database objects.
Oracle 10g includes improvements to Oracle's Virtual Private Database (VPD), including the following features:
The benefit of VPD is that it provides for row-level security in your Oracle database. Oracle 10g offers a feature that allows you to indicate that a VPD policy should only be enforced if specific columns are accessed or referenced. One or more columns can be defined within a policy. If you don't specify any columns, VPD will operate just as in Oracle9i.
As a result, you can now provide varying levels of security for database tables. For example, you may not need to secure queries against certain columns, such as the name of an employee, but you may require some level of access control for queries against the social security number, because of privacy issues. In this case, you would create a VPD policy that references the column containing the social security number. The policy would be effective for any query that includes the SSN column. This allows you to define privacy policies for certain types of data, such as personal data, while making other data available.
This feature is supported with the addition of the sec_relevant_cols parameter in the dbms_rls.add_policy PL/SQL package supplied by Oracle. The following example usea dbms_rls.add_policy to create a policy on a table called RET_SCHEMA.RETIREE:
The steps to implement a VPD policy using this new feature are basically the same as in Oracle9i, with the exception of the sec_relevant_cols parameter in dbms_rls.add_policy (which is optional). If you do not include the sec_relevant_cols parameter, then the policy will apply to all columns, just as it did prior to Oracle 10g.
In the following example, we are applying the policy implemented by the RUN function to the EMP table. You also set the policy to only apply the VPD predicate for SELECT statements. The three security-relevant columns in the EMP table are SAL, HIREDATE and COMM.
Connect as scott user on sqlplus.
create or replace function scott.run
(object_schema in varchar2, object_name in varchar2)
return varchar2 is d_predicate varchar2(2000);
d_predicate := '(ename = sys_context (''USERENV'', ''SESSION_USER''))';
After creating the function to implement policy, add the policy in database. Te user should have enough permission to execute DBMS_RLS package.
dbms_rls.add_policy(object_schema => 'scott',
object_name => 'emp',
policy_name => 'scott_policy',
function_schema => 'scott',
policy_function => 'run',
statement_types => 'select',
sec_relevant_cols => 'sal,hiredate,comm');
With reference to the above example, Oracle database will not enforce the VPD policy when you select only the LAST_NAME column from the EMP table because LAST_NAME column is not a security relevant column and Oracle will not apply security policy on this column. So employees cannot access sensitive information in the EMP Table. However, when you issue queries that access columns considered as security-relevant (sal, hiredate and comm), then VPD applies the fine-grained access control defined by the policy function.
Here is an example of the way that the column level VPD policy created earlier would be applied. In this first example, we are querying the EMP table. We reference the FIRSTNAME column which is not a secured column. As a result, the VPD policy we created will not apply to this query and all rows in the table will be available to the query:
SELECT COUNT(*) FROM EMP WHERE FIRSTNAME LIKE ‘%’;
In contrast, the following query uses a secured column (SAL) in the WHERE clause. As a result, the column level VPD policy will be applied to the statement and only the rows/columns that the user has access to will be included in the result set:
SELECT COUNT(*) FROM EMP WHERE SAL > 0 OR SAL < 0 or SAL=0;
In processing the second statement, Oracle will affix an additional predicate that will restrict the result set such that it conforms to the VPD policy that is defined on the EMP table (which in our case means that the Oracle user will only be able to see their own individual record, and no one else's).
The only type of database policy offered in earlier versions of Oracle was a dynamic one. Oracle 10g offers five VPD policy types to choose from, which are listed and described below.
Static policy predicates execute quickly because they are stored in the SGA. Static policies also have the same predicate that is applied to all SQL statements accessing the objects assigned to the policy. Dynamic policies are re-created via the policy function each time the defined columns are accessed, and thus dynamic policy execution can be slower than static policy execution.
Note that some policy types can be shared between different objects. This allows a single policy to scale better, and keeps business rules more consistent. To define the policy type, use the policy_type argument to the dbms_rls.add_policy procedure for the correct policy type, as shown in this example:
Oracle 10g now supports the use of parallel query within the VPD framework. This makes VPD much more scalable.