About Oracle Virtual Private Database

Oracle Virtual Private Database (VPD) provides row-level security at the database table or view level. You can extend it to provide column-level security as well. Essentially, Virtual Private Database inserts an additional WHERE clause to any SQL statement that is used on any table or view to which a Virtual Private Database security policy has been applied. (A security policy is a function that allows or prevents access to data.) The WHERE clause allows only users whose identity passes the security policy, and hence, have access to the data that you want to protect.

An Oracle Virtual Private Database policy has the following components, which are typically created in the schema of the security administrator:

• A PL/SQL function to append the dynamic WHERE clause to SQL statements that affect the Virtual Private Database tables. For example, a PL/SQL function translates the following SELECT statement:

  SELECT * FROM ORDERS;
  

  to the following:

  SELECT * FROM ORDERS
    WHERE SALES_REP_ID = 159;
  

  In this example, the user can only view orders by Sales Representative 159. The PL/SQL function used to generate this WHERE clause is as follows:

  1 2 3 4 5 6 7 8 9 10 11 12

  CREATE OR REPLACE FUNCTION auth_orders( schema_var IN VARCHAR2, table_var IN VARCHAR2 ) RETURN VARCHAR2 IS return_val VARCHAR2 (400); BEGIN return_val := 'SALES_REP_ID = 159'; RETURN return_val; END auth_orders; /

  
  

  In this example:

  • Lines 2–3: Create parameters to store the schema name, OE, and table name, ORDERS. (The second parameter, table_var, for the table, can also be used for views and synonyms.) Always create these two parameters in this order: create the parameter for the schema first, followed by the parameter for the table, view, or synonym object. Note that the function itself does not specify the OE schema or its ORDERS table. The Virtual Private Database policy you create uses these parameters to specify the OE.ORDERS table.

  • Line 5: Returns the string that will be used for the WHERE predicate clause.

  • Lines 6–10: Encompass the creation of the WHERE SALES_REP_ID = 159 predicate.

  You can design the WHERE clause to filter the user information based on the session information of that user, such as the user ID. To do so, you create an application context. Application contexts can be used to authenticate both database and nondatabase users. An application context is a name-value pair. For example:

  SELECT * FROM oe.orders 
   WHERE sales_rep_id = SYS_CONTEXT('userenv','session_user'); 
  

  In this example, the WHERE clause uses the SYS_CONTEXT PL/SQL function to retrieve the user session ID (session_user) designated by the userenv context. See Oracle Database Security Guide for detailed information about application contexts.

• A way to attach the policy the package. Use the DBMS_RLS.ADD_POLICY function to attach the policy to the package. Before you can use the DBMS_RLS PL/SQL package, you must be granted EXECUTE privileges on it. User SYS owns the DBMS_RLS package.

The advantages of enforcing row-level security at the database level rather than at the application program level are enormous. Because the security policy is implemented in the database itself, where the data to be protected is, this data is less likely to be vulnerable to attacks by different data access methods. This layer of security is present and enforced no matter how users (or intruders) try to access the data it protects. The maintenance overhead is low because you maintain the policy in one place, the database, rather than having to maintain it in the applications that connect to this database. The policies that you create provide a great deal of flexibility because you can write them for specific DML operations.