Oracle Database Security Overview – Part 1 – Virtual Private 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:
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.
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 |
|
When all the queries on a table have a mandatory predicate and performance is the priority. |
| DYNAMIC (Default) |
|
Used when the predicate condition changes for each query issued on the Virtual Private Database protected object or table. |
| SHARED_STATIC |
|
Used when multiple tables or views have the same columns. |
| CONTEXT_SENSITIVE |
|
Used when the predicate varies by the user or group. |
| SHARED_CONTEXT_SENSITIVE |
|
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…
