Virtual Private Database

Virtual Private Database (VPD) is also known as Fine Grained Access Control (FGAC) or Row-level Security (RLS). It provides added security capabilities to the Oracle database by masking data so that users only see their private information. Data for separate sites, departments and individuals can be stored together in a single database without the knowledge of the users.

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 rows the user is not supposed to see.

History

VPD was first introduced with Oracle 8i (8.1.5) in February 1999.

Examples

Prevent DML against a table

Prevent DML against a table, similar to the Oracle 11g's ALTER TABLE ... READ ONLY; command:

SQL> CREATE OR REPLACE FUNCTION lockfunc(object_schema VARCHAR2, object_name VARCHAR2)
  2  RETURN VARCHAR2
  3  AS
  4  BEGIN
  5     RETURN '1 = 2';
  6  END;
  7  /
Function created.

SQL> EXEC dbms_rls.add_policy('SCOTT', 'MYTAB', 'LOCKIT', 'SCOTT', 'LOCKFUNC','INSERT,UPDATE', TRUE);
PL/SQL procedure successfully completed.

SQL> SELECT policy_name, sel, ins, upd, del, enable
  2    FROM user_policies
  3  /
POLICY_NAME                    SEL INS UPD DEL ENA
------------------------------ --- --- --- --- ---
LOCKIT                         NO  YES YES NO  YES

SQL> INSERT INTO locktab SELECT 'two', 'twot' from dual;
INSERT INTO mytab SELECT 'two', 'twot' from dual
            *
ERROR at line 1:
ORA-28115: policy with check option violation