IndicThreads

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:

Oracle Database Security feature under three categories

The Oracle Database Security solution includes the following database products:

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

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:

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:

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:

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.