Creating a row-level Virtual Private Database (VPD)

Oracle provides the framework to provide custom row-level security. This needs to be defined and built using PL/SQL and some of the default Oracle packages.

Getting ready

In order to create the VPD functions, we will need to have a sample schema. This sample schema will be used throughout the recipes. Open SQL Developer and log in to the database using an administrator user (SYS, system, and others) and create a schema owner for the sample objects. Ensure you grant the following permissions to the schema owner; replace<Schema> with your schema owner name:

GRANT CONNECT, RESOURCE TO <Schema>;
GRANT select_catalog_role TO <Schema>; -- Included to enable the use of autotrace
GRANT SELECT ANY dictionary TO <Schema>; -- Included to enable the use of autotrace
GRANT execute on DBMS_RLS to <Schema>; -- Included to enable the creation of policies
GRANT CREATE ANY CONTEXT to <Schema>; -- Included to enable the...