Oracle Database Security Overview – Part 1 – Virtual Private Database

In this two part series, Saurabh Gupta covers the fundamentals of database security solutions from Oracle. Virtual Private Database enforces row-level security through the policies and restricts the data access for unauthorized users. Depending on a user’s identity and role, the application can set up multitenancy and ensure user isolation as well. Features like Data Redaction, Real Application Security, and Privilege Analysis further strengthen the security offering in Oracle Database.

This article is an excerpt from the book Advanced Oracle PL/SQL Developer’s Guide – Second Edition, from Packt Publishing a handy technical reference for seasoned professionals in the database development space.

Oracle offers a comprehensive suite of database security solutions that aim to secure the databases and applications by protecting sensitive information, detecting threats and attacks, and managing security policies along the lines of organizational compliance. The defense-in-depth approach of Oracle can be categorized under preventive, detective, and administrative controls that ensure all the vulnerable areas of an application are covered. A powerful security strategy can be developed and laid down by using the right mix of features from these three controls. The factors impacting the formulation of a security strategy can be threat anticipation, data sensitivity, data distribution and user classification, compliance, and internal regulations.

Each of the above categories includes various products and technical features. The following diagram bifurcates the security pillars into security measures:
image00.png

Oracle Database Security feature under three categories

The Oracle Database Security solution includes the following database products:

  • Oracle Advanced Security: Oracle Database Security is a licensed option available with the Oracle Database Enterprise Edition. This option includes various features that can be implemented at the database level to prevent data hacking and reduce the exposure of sensitive information to an unauthorized user. It helps protect the data from malicious attacks and comply with the security regulations like Payment Card Industry Data Security Standard (PCI-DSS). For example, you can store a customer’s bank account information in an encrypted format in the storage. Furthermore, whenever the customer support executives retrieve customer details, the data appears in a redacted format. The Oracle Advanced Security option includes:
  • Data Redaction: The Data Redaction feature was introduced in Oracle Database 12c to apply on-the-fly masking policies to the sensitive information in the database. The feature has been backported to Oracle Database 11.2.0.4. Data redaction can be a handy feature as it is cost efficient and dynamically obfuscates the data.
  • Transparent Data Encryption (TDE): The encryption feature prevents the data, which is lying in storage, from outside attacks. The data is stored in an encrypted format, which gets auto-decrypted while reading. It is installed at the time of the Oracle Database software installation. It is an easy-to-implement transparent solution and requires no changes to the existing SQL queries.

Note: Securefile encryption is part of the Advanced Security option.

  • The other features included under the Advanced Security option are Data Pump Export file encryption and RMAN Backup encryption to disk.
  • Virtual Private Database (VPD): It is a security feature available in Oracle Database Enterprise Edition that controls the data visibility and accessibility depending on the database user authorization. First introduced in Oracle 8i, the feature works with VPD policies and session contexts. All queries issued by a user are appended by an additional predicate to restrict the rows in the result set. It is also known as Fine Grained Access Control or Row Level Security.
  • Real Application Security (RAS): This feature was introduced in Oracle Database 12c to support the diversified security requirements in an enterprise application model. It presents an advanced version of the Virtual Private Database technology. Not only the visibility of data to the end user but a Real Application Security policy also allows you to control the permissions on the application objects. It is one of the smartest security solutions that can control the privileges of an application user on the business objects.
  • Oracle Key Vault (OKV): Oracle Key Vault provides a robust key management platform that can be used as the centralized repository of TDE master keys, Oracle wallets, Java key stores, SSH keys, and other security credential files. The OKV store allows the end-to-end administration of the key lifecycle including creation, retention, and key rotation.
  • Oracle Database Vault: Oracle Database Vault enables a security solution to safeguard the application data from privileged database users and ad hoc access. A multifactor database vault policy imposes deterrent controls on the user access to the database and reports violations if any. It is a powerful proactive solution that helps in reducing the attack surface for highly privileged users like SYSTEM, SYSDBA, or a user with a large set of roles and privileges. Oracle provides default vault policies for the Oracle E-Business Suite (versions 11.5.10, 12.0, and 12.1), Oracle PeopleSoft application, Oracle Siebel CRM (version 7.7 and higher), and SAP applications.
  • Privilege Analysis: The new feature in Oracle Database 12c analyzes the privileged users, roles, and database to report the used and unused privileges. Based on the analysis, the security administrator (or the DBA) can determine whether to retain or revoke the privileges of the developers or database users. This feature is quite important from the point of view of application security and helps reduce the risks from internal users.
  • Oracle Data Masking and Subsetting: The Oracle data masking and subsetting pack masks the original copy of the application data by physically obfuscating the sensitive information in either the complete application database or just its subset. The masked data can then be proliferated for analytical, testing, or regulatory purposes. The sensitive information and referential integrity model can be discovered based on the prebuilt data patterns. A portion of a large database can be extracted based on factors like percentage, the number of rows, or column predicates. This pack contains a variety of masking formats and transformations that can be applied to the application data.
  • Oracle Label Security (OLS): Oracle Label Security, first introduced in Oracle 8i, is a database option available in the enterprise edition of the Oracle Database. It enables the classification of data by labels. The security policies control the user access privilege and authorization on the application by the data labels. This option is very useful in implementing multilevel security in large legacy applications.
  • Oracle Audit Vault and Database Firewall (AVDF): This product can be deployed in all the Oracle Database editions and non-oracle products such as Microsoft SQL Server, IBM DB2 for LUW, SAP Sybase ASE, MySQL databases and Oracle Big Data Appliance. You can define the rules to capture and block the unauthorized SQL access to the application data. Another feature of this product is the consolidation of the database audit with the system and network audit information, which then generates alerts and reports.

Note: Strong authentication services like Kerberos, PKI, and RADIUS along with network encryption are available in all the editions of the Oracle Database.

Fine-grained access control

In an enterprise application, user isolation or role-based multitenancy ensures that data is accessed by the authorized users only. In a Software as a Service (SaaS) model application, tenant isolation is one of the prime demands. Application users are authorized to access only their world of data and not peek into other’s data.

Fine-grained access control (FGAC) enables the users to enforce security policies on the access of rows and columns based on their role and authorization. For example, an HR representative is authorized to view the details of employees that belong only to his vertical Similarly, a relationship officer in a bank is authorized to access the account details of those customers that belong to his branch. The FGAC feature provides you with the mechanism to expose the authorized piece of data only to the approved user.

How FGAC works

Fine-grained access control enables the creation of security policies that can be associated with a table or view. Upon invocation, the security policy evaluates the policy function that returns a predicate clause, which is dynamically appended to the SQL queries issued by the user. Thus, the user views the appropriate rows from the table or a view. The security policy can limit either rows or columns or both.

In the following figure, a user from department 10 queries the EMP table. He issues a SELECT statement to view the employee details. Oracle invokes the FGAC policy attached to the EMP table and appends a predicate (that is, the WHERE clause) to the SELECT query. The modified SELECT query is then executed in the HR schema of the database.

image02.png

How Fine-Grained Access Control works

The following are the salient features of fine-grained access control:

  • Oracle provides the DBMS_RLS package for the management of security policies like creation, drop, enable, and disable
  • A policy can be set to be invoked only when given columns are accessed
  • Multiple security policies can be associated with a table or view
  • All security policies and the predicates are appended with the AND clause

Virtual Private Database

The working principle of the Virtual Private Database technology is that users should have isolated and distinguished data access. Virtual Private Database (VPD) is a feature that is built on fine-grained access control and uses application contexts to define and add the predicates to the SQL queries. Similar to FGAC, Virtual Private Database lays down the security framework through the security policies in the Oracle Database and associates them directly to tables, views, or synonyms. The security policies act as a safety net on the objects, and by no means can they be bypassed.

How Virtual Private Database works

Whenever a user issues an SQL query against a Virtual Private Database protected table, Oracle invokes the policy and evaluates the policy function. The policy function is a PL/SQL function that returns a predicate or a WHERE clause. The query is then rewritten using dynamic views to add the predicate clause. The modified query is executed against the database server, and the result set is returned to the user. The predicate or the WHERE clause participates in the execution of the query, that is, the column indexes will impact the query execution plan.

Note that the Virtual Private Database protects only the SELECT, INDEX, and DML operations (INSERT, UPDATE, and DELETE). It does not work with DDL like the TRUNCATE or ALTER TABLE statements.

Column-level Virtual Private Database

Row-level security can also be imposed on tables and views whenever a user selects sensitive columns. The sensitive columns can be specified at the time of the policy creation, that is, the SEC_RELEVANT_COLS parameter of the DBMS_RLS.ADD_POLICY procedure.

The column-level policy works only for the SELECT queries. When a user selects sensitive columns (implicitly or explicitly), the non-authorized rows are excluded from the result set. For example, if an HR representative queries public information like the name and department of employees, the query will list all the records from the table. If he attempts to select the secured columns like SAL, the result set will be limited by the row-level security condition.

You can include all the rows with the sensitive column values marked as NULL. It can be achieved by specifying an attribute known as SEC_RELEVANT_COLS_OPT in the DBMS_RLS.ADD_POLICY procedure.

Virtual Private Database with Oracle Database 12c multitenant

Virtual Private Database is supported within a pluggable database. You cannot create a security policy in the root container, that is, the container database.

Virtual Private Database components

The VPD technology makes use of the application package, policy function, application context, and the DBMS_RLS package to add policies. In the following section, we will discuss the building blocks of the Virtual Private Database implementation.

Application context

A Virtual Private Database implementation with application contexts is useful when working with multiple security attributes. For example, in an organization, the data view authorization may be different for different job roles. In a manufacturing unit, a supervisor can be authorized to query the global parts while an executive can see only the local parts. In such cases, the job role can be a context attribute whose value can be used in the predicate clause returned by the policy function.

Application contexts are session variables that hold the user and session information securely. Context attributes are stored as key-value pairs under a given namespace, which is also known as a label. USERENV is a system-defined namespace in the Oracle Database that can provide session information like DB_NAME, SESSION_USER, and others. User-defined application contexts can be created by a SYSDBA or users with the CREATE ANY CONTEXT privilege by using a trusted application package.

An application context can be either database session-based, client session-based, or global.

A database session-based application context is accessible only within a database user’s session. Its attributes and values are stored in a session cache, User Global Area (UGA). The database session-based application contexts can be either initialized locally from the session, externally using a database link, OCI interface or job queue process, or globally from a centralized directory using the Oracle Internet Directory. You can query the database session-based context attributes from the SESSION_CONTEXT dictionary view.

A client session-based application context is initialized using OCI functions (OCIAppCtxSet) and is stored in User Global Area (UGA) under the CLIENTCONTEXT namespace. The application can set the non-database attributes within the contexts and use them during policy creation. Ensure that the database session and client session-based application contexts do not conflict with each other.

A global application context can be accessed by all the database user sessions in a single instance or RAC environment. The context attributes and their values are stored in System Global Area (SGA), which can be queried from the GLOBAL_CONTEXT dictionary view.

The following SQL queries the database name from the USERENV namespace using the SYS_CONTEXT function:

/*Query the database name from USERENV namespace*/

SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’)

FROM dual

/

SYS_CONTEXT(‘USERENV’,’DB_NAME’)

——————————————————-

orcl

You can create database session-based application contexts using the CREATE CONTEXT statement, as per the following syntax:

CREATE CONTEXT [context_name]

USING [trusted_package]

INITIALIZED [EXTERNALLY | GLOBALLY]

ACCESSED [LOCALLY | GLOBALLY]

Trusted package is a PL/SQL package or stored procedure that controls the values of the context attributes. A database session-based context can be initialized locally, externally or globally. The context type can be ACCESSED GLOBALLY for global application contexts or ACCESSED LOCALLY (default) for session-based application contexts. The following example shows the creation of a context using a trusted package:

/*Connect as SYSDBA*/

CONN sys/oracle AS SYSDBA

Connected.

/*Grant CREATE ANY CONTEXT, DROP ANY CONTEXT privileges to SCOTT*/

GRANT CREATE ANY CONTEXT, DROP ANY CONTEXT TO SCOTT

/

Grant succeeded.

/*Connect to SCOTT user*/

CONN scott/tiger

Connected.

You can create the trusted PL/SQL program before or after creating the context. The following PL/SQL procedure P_APP_CONTEXT uses the Oracle supplied package DBMS_SESSION to create a context namespace and member attributes.

/*Create the stored procedure to set the context attribute*/

CREATE OR REPLACE PROCEDURE p_app_context (p_val VARCHAR2)

IS

BEGIN

/*Create a namespace DEMO_CONTEXT*/

DBMS_SESSION.SET_CONTEXT(

NAMESPACE => ‘DEMO_CONTEXT’,

ATTRIBUTE => ‘COUNTRY’,

VALUE => P_VAL);

END;

/

The PL/SQL procedure can now be used to create the application context.

/*Create the context*/

CREATE CONTEXT demo_context USING p_app_context

/

Context created.

Now, use the trusted program to set the value of the COUNTRY context key. Use the same program to modify the context attribute value.

/*Call P_APP_CONTEXT to set context value*/

EXEC p_app_context(‘LUXEMBOURG’)

/

PL/SQL procedure successfully completed.

/*Query the COUNTRY context*/

SELECT SYS_CONTEXT(‘DEMO_CONTEXT’,’COUNTRY’)

FROM DUAL

/

SYS_CONTEXT(‘DEMO_CONTEXT’,’COUNTRY’)

LUXEMBOURG

Virtual Private Database policy function

The Virtual Private Database policy function is a PL/SQL function that is used to construct the predicate (or WHERE) clause. The policy function is specified during policy creation that gets executed at the time of policy enforcement. Note that the function execution performance directly impacts the query performance.

The following are the features of the policy function:

  • The schema name and database object name are mandatory parameters (and in the same sequence) to the policy function. Upon execution, the parameter values are supplied by the DBMS_RLS package.
  • The function must return a valid predicate in string format.
  • The function must not perform SELECT or DML on the table to be protected through the Virtual Private Database.
Policy type

You can specify a policy type to optimize the performance of a Virtual Private Database security policy. The policy performance is limited by the resources consumed during the execution of the policy function and frequency of its execution.

A Virtual Private Database policy can be STATIC, DYNAMIC, SHARED_STATIC, CONTEXT_SENSITIVE, or SHARED_CONTEXT_SENSITIVE. The following table shows the different VPD policy types:

Policy type Comments When to use
STATIC
  • Same predicate for all the queries.
  • Predicate clause is cached in SGA.
When all the queries on a table have a mandatory predicate and performance is the priority.
DYNAMIC (Default)
  • Policy function is executed for every query.
  • No performance optimization.
Used when the predicate condition changes for each query issued on the Virtual Private Database protected object or table.
SHARED_STATIC
  • Policy shareable across schema objects.
  • Predicate clause is cached in SGA.
Used when multiple tables or views have the same columns.
CONTEXT_SENSITIVE
  • Policy is applicable only to a fixed value of the application context.
  • Include the namespace and attribute parameters in the policy definition.
Used when the predicate varies by the user or group.
SHARED_CONTEXT_SENSITIVE
  • Same as the context-sensitive policy.
  • Policy can be shared among multiple objects.
Used when the predicate varies by the user and can be shared by multiple database objects.
The DBMS_RLS package

The security policies are enforced using the Oracle-supplied DBMS_RLS package. It can be used to add, drop, or refresh a security policy, enable or disable a policy, and handle the policy groups. It is owned by the SYSTEM user. For complete details, it is recommended that you refer to the Oracle Database 12c documentation at https://docs.oracle.com/database/121/ARPLS/d_rls.html.

Demonstration

This section demonstrates the implementation of a Virtual Private Database to enforce row-level and column-level security.

Let’s suppose that SCOTT is a master user that owns the employee details. All other sub users are authorized to view the details of the employees that belong to their job roles. For example, CLERK is authorized to view only the clerk’s details while SALES is authorized to view only salesman data.

Let’s prepare the test environment by creating the test users and granting the required privileges:

/*Create user CLERK, MGR, SALES. Grant CONNECT, RESOURCE roles*/

CONN sys/oracle as sysdba

CREATE USER clerk IDENTIFIED BY clerk

/

CREATE USER mgr IDENTIFIED BY mgr

/

CREATE USER sales IDENTIFIED BY sales

/

GRANT CONNECT, RESOURCE TO clerk

/

GRANT CONNECT, RESOURCE TO mgr

/

GRANT CONNECT, RESOURCE TO sales

/

GRANT EXECUTE ON dbms_rls TO public

/

GRANT CREATE ANY CONTEXT TO scott

/

GRANT CREATE PUBLIC SYNONYM TO scott

/

Let’s create the MYJOB context using a trusted program:

CONN scott/tiger

/*Create the context MYJOB*/

CREATE CONTEXT myjob USING scott.job_context

/

The following JOB_CONTEXT procedure sets the MYJOB context attributes, based on the current session user. The context attribute values should match the expected value to be used in the query predicates:

/*Create the PL/SQL procedure */

CREATE OR REPLACE PROCEDURE job_context IS

BEGIN

/*Set the context based on the session user*/

IF SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) = ‘CLERK’ THEN

DBMS_SESSION.SET_CONTEXT (‘myjob’,’role’,’CLERK’);

ELSIF SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) = ‘SALES’ THEN

DBMS_SESSION.SET_CONTEXT (‘myjob’,’role’,’SALESMAN’);

ELSIF SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) = ‘MGR’ THEN

DBMS_SESSION.SET_CONTEXT (‘myjob’,’role’,’MANAGER’);

END IF;

END;

/

As this procedure will be accessed by SALES, CLERK, and MGR, let’s create a public synonym and grant the execute privilege to the users:

/*Create synonym for JOB_CONTEXT procedure*/

CREATE PUBLIC SYNONYM job_context FOR scott.job_context

/

GRANT EXECUTE ON scott.job_context TO clerk, mgr, sales

/

/*Grant select privilege on EMP table to the users */

GRANT SELECT ON emp TO clerk, mgr, sales

/

To set the context at the time of the database logon, let’s create a logon trigger. This trigger calls the context setting procedure:

CONN sys/oracle AS SYSDBA

/*Create a logon trigger*/

CREATE OR REPLACE TRIGGER scott.set_security_context

AFTER LOGON ON DATABASE

BEGIN

scott.job_context;

END;

/

Now, it is time to create the Virtual Private Database policy function. It lays down the logic of row-level security by constructing the predicate clause. In this case, the security policy virtually partitions the data view by job roles. The predicate uses the SYS_CONTEXT function to retrieve the job role corresponding to the session user.

CONN scott/tiger

/*Create policy function */

CREATE OR REPLACE FUNCTION fun_vpd

(schemaowner VARCHAR2, objectname VARCHAR2)

RETURN VARCHAR2 IS

BEGIN

/*Return the predicate clause*/

RETURN ‘job = SYS_CONTEXT(”myjob”,”role”)’;

END;

/

/*Create public synonym for the policy function */

CREATE PUBLIC SYNONYM fun_vpd FOR scott.fun_vpd

/

GRANT EXECUTE ON scott.fun_vpd TO clerk, mgr, sales

/

The final step is the creation of policy on the object using the DBMS_RLS.ADD_POLICY procedure:

conn scott/tiger

BEGIN

DBMS_RLS.add_policy(

object_schema =>’SCOTT’,

object_name =>’EMP’,

policy_name =>’VPD_RLS’,

function_schema =>’SCOTT’,

policy_function =>’FUN_VPD’,

statement_types =>’SELECT’);

END;

/

Let’s connect to the MGR user and verify the Virtual Private Database in operation:

conn mgr/mgr

SELECT empno, ename, job, mgr, deptno

FROM scott.emp

/

EMPNO ENAME JOB MGR DEPTNO

——– —– ——- ————– ——

7566 JONES MANAGER 7839 20

7698 BLAKE MANAGER 7839 30

7782 CLARK MANAGER 7839 10

The CLERK user can query the details of only those employees that are clerks. Similarly, the other users can try to log in the database and verify the data view:

conn clerk/clerk

SELECT empno, ename, job, mgr, deptno

FROM scott.emp

/

EMPNO ENAME JOB MGR DEPTNO

——– ———- ——- ———- ——

7369 SMITH CLERK 7902 20

7876 ADAMS CLERK 7788 20

7900 JAMES CLERK 7698 30

7934 MILLER CLERK 7782 10

And then the SALES user:

conn sales/sales

SELECT empno, ename, job, mgr, deptno

FROM scott.emp

/

EMPNO ENAME JOB MGR DEPTNO

——– ———- ———- ———- ——

7499 ALLEN SALESMAN 7698 30

7521 WARD SALESMAN 7698 30

7654 MARTIN SALESMAN 7698 30

7844 TURNER SALESMAN 7698 30

You can enforce a column-level policy by specifying the sensitive columns at the time of the policy creation. The following PL/SQL block drops the last Virtual Private Database policy and creates a new one to enforce the row-level security:

conn scott/tiger

BEGIN

/*Drop the row-level policy*/

DBMS_RLS.DROP_POLICY (‘SCOTT’,’EMP’,’VPD_RLS’);

/*Create a column level VPD policy*/

DBMS_RLS.ADD_POLICY(

object_schema => ‘SCOTT’,

object_name => ‘EMP’,

policy_name => ‘VPD_COLUMN’,

policy_function => ‘FUN_VPD’,

sec_relevant_cols => ‘sal,comm’);

END;

/

The preceding security policy specifies that whenever a user will try to query the SAL and COMM columns of the EMP table, the row-level security will be enforced. Let’s verify this statement by connecting to the MGR user and querying the employee records:

CONN mgr/mgr

/*Select non-sensitive columns from EMP table*/

SELECT empno, deptno, job

FROM scott.emp

/

EMPNO DEPTNO JOB

——– —— ———-

7369 20 CLERK

7499 30 SALESMAN

7521 30 SALESMAN

7566 20 MANAGER

7654 30 SALESMAN

7698 30 MANAGER

7782 10 MANAGER

7788 20 ANALYST

7839 10 PRESIDENT

7844 30 SALESMAN

7876 20 CLERK

7900 30 CLERK

7902 20 ANALYST

7934 10 CLERK

14 rows selected.

Note that the preceding query displays all the employees because the SELECT query did not project the SAL and COMM sensitive columns. If the query had contained the SAL column, the output would have been as follows:

/*Select sensitive columns from EMP table*/

SELECT empno, deptno, sal, job

FROM scott.emp

/

EMPNO DEPTNO SAL JOB

——– —— ———- ———-

7566 20 2975 MANAGER

7698 30 2850 MANAGER

7782 10 2450 MANAGER

In the preceding output, the employee records with the sensitive columns are restricted in the query output. However, you can still list all the employees and mask the SAL and COMM sensitive columns with NULLs by setting the SEC_RELEVANT_COLS_OPT attribute in DBMS_RLS.ALL_ROWS:

CONN scott/tiger

BEGIN

/*Drop the previous policy*/

DBMS_RLS.DROP_POLICY (‘SCOTT’,’EMP’,’VPD_COLUMN’);

/*Create a new column-level policy*/

DBMS_RLS.ADD_POLICY(

object_schema => ‘SCOTT’,

object_name => ‘EMP’,

policy_name => ‘VPD_COLUMN’,

policy_function => ‘FUN_VPD’,

sec_relevant_cols => ‘sal,comm’,

sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);

END;

/

With the preceding security policy, the output of the SELECT query will be as follows:

CONN mgr/mgr

/*Select sensitive columns from EMP table*/

SELECT empno, ename, job, sal, deptno

FROM scott.emp

ORDER BY job

/

EMPNO ENAME JOB SAL DEPTNO

————————————– ————– ——–

7788 SCOTT ANALYST 20

7902 FORD ANALYST 20

7934 MILLER CLERK 10

7900 JAMES CLERK 30

7369 SMITH CLERK 20

7876 ADAMS CLERK 20

7698 BLAKE MANAGER 2850 30

7566 JONES MANAGER 2975 20

7782 CLARK MANAGER 2450 10

7839 KING PRESIDENT 10

7844 TURNER SALESMAN 30

7654 MARTIN SALESMAN 30

7521 WARD SALESMAN 30

7499 ALLEN SALESMAN 30

14 rows selected.

Virtual Private Database features and best practices

The following are the features and best practices that can be followed while working with a Virtual Private Database:

  • You cannot query a Virtual Private Database protected table as SELECT FOR UPDATE because of the query rewrite implementation. However, the FOR UPDATE user query may work provided the VPD implied inline view for the query is not a complex one, that is, a non-analytic query, no DISTINCT, no cursor expression, no SET operator.
  • SYS user is exempt from the Virtual Private Database security policies. However, the SYSDBA actions can be monitored using Database Vault.
  • Users with the EXEMPT ACCESS PRIVILEGE system privilege are exempt from the Virtual Private Database security policies. This privilege must be used judiciously as it bypasses all the fine-grained security controls.
  • The policy function should be executed with definer’s rights in major cases to avoid any uncertainty due to privileges.
  • Avoid using outer joins and the ANSI join operations on the Virtual Private Database protected tables.
  • If you are using edition-based redefinition in your application, you can associate an editioning view to apply the Virtual Private Database policies across all the editions.
  • Virtual Private Database works with flashback query. You can query the business data to a past time or older SCN using the most recent security policies.
  • During a direct path export operation, the Oracle Virtual Private Database policies are not enforced.
  • For a schema-level export, the user must be granted the EXP_FULL_DATABASE role which includes the EXEMPT ACCESS POLICY privilege to ignore the Virtual Private Database policies.

Virtual Private Database policy metadata

Oracle captures the static and dynamic details of the VPD policies in dictionary views. You can query the following dictionary views to find the metadata about the Virtual Private Database security policies:

Dictionary view Comments
[ALL | USER | DBA]_POLICIES The view captures the security policy on objects accessible to a user, owned by a user, or within a database.
[ALL | USER | DBA]_POLICY_ATTRIBUTES The view captures the application context namespaces, their attributes, and their association with the Virtual Private Database policy.
[ALL | USER | DBA]_POLICY_CONTEXTS The view captures information about the driving contexts for the objects.
[ALL | USER | DBA]_POLICY_GROUPS The view captures information about the policy groups on the objects.
[ALL | USER | DBA]_SEC_RELEVANT_COLS The view captures the specifications of the column-level Virtual Private Database policy on the objects.
V$VPD_POLICY The dynamic view captures information about the security policy associated with the cached cursors. In a multitenant environment, the view shows information about the current pluggable database. It is extremely useful in troubleshooting policy execution.

The following query gives the predicate applied to the queries executed against the Virtual Private Database protected table owned by the SCOTT user:

connect sys/oracle as sysdba

SELECT object_name,

policy,

s.sql_text,

predicate

FROM v$vpd_policy vp, v$sql s

WHERE vp.sql_id=s.sql_id

AND object_owner=’SCOTT’

/

OBJECT_NAME POLICY SQL_TEXT PREDICATE

———– ———— ————————- —————

EMP VPD_COLUMN select * from scott.emp job = SYS_CONTEXT(‘myjob’,’role’)

For further information about the VPD_COLUMN policy, you can refer to the USER_POLICIES dictionary view:

connect scott/tiger

SELECT policy_name,

policy_type,

static_policy,

function,

pf_owner

FROM USER_POLICIES

/

POLICY_NAME POLICY_TYPE STA FUNCTION PF_OWNER

————— ————— — ———- ———

VPD_COLUMN DYNAMIC NO FUN_VPD SCOTT

Continued at…


This article is an excerpt from the book Advanced Oracle PL/SQL Developer’s Guide – Second Edition, from Packt Publishing by Saurabh K. Gupta.

Saurabh Gupta

Saurabh K. Gupta is a seasoned database technologist with extensive experience in designing high performance and highly available database applications. His technology focus has been centered around Oracle Database architecture, Oracle Cloud platform, Database In-Memory, Database Consolidation, Multitenant, Exadata, Big Data, and Hadoop. He has authored the first edition of the book titled "Oracle Advanced PL/SQL Developer Professional Guide" in the year 2012. He is an active speaker at technical conferences from Oracle Technology Network, IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his twitter handle (@SAURABHKG) or through his technical blog, with comments, suggestions, and feedback regarding this book.