8 Using Oracle Virtual Private Database to Control Data Access

8.1.2.1 Basing Security Policies on Database Objects Rather Than Applications

Attaching Oracle Virtual Private Database security policies to database tables, views, or synonyms, rather than implementing access controls in all your applications, provides the following benefits:

• Security. Associating a policy with a database table, view, or synonym can solve a potentially serious application security problem. Suppose a user is authorized to use an application, and then drawing on the privileges associated with that application, wrongfully modifies the database by using an ad hoc query tool, such as SQL*Plus. By attaching security policies directly to tables, views, or synonyms, fine-grained access control ensures that the same security is in force, no matter how a user accesses the data.

• Simplicity. You add the security policy to a table, view, or synonym only once, rather than repeatedly adding it to each of your table-based, view-based, or synonym-based applications.

• Flexibility. You can have one security policy for SELECT statements, another for INSERT statements, and still others for UPDATE and DELETE statements. For example, you might want to enable Human Resources clerks to have SELECT privileges for all employee records in their division, but to update only salaries for those employees in their division whose last names begin with A through F. Furthermore, you can create multiple policies for each table, view, or synonym.

8.1.2.2 Controlling How Oracle Database Evaluates Policy Functions

Running policy functions multiple times can affect performance. You can control the performance of policy functions by configuring how Oracle Database caches the Oracle Virtual Private Database predicates. The following options are available:

• Evaluate the policy once for each query (static policies).

• Evaluate the policy only when an application context within the policy function changes (context-sensitive policies).

• Evaluate the policy each time it is run (dynamic policies).

See "Optimizing Performance by Using Oracle Virtual Private Database Policy Types" for information configuring these policy types.

8.3.4.1 Adding Policies for Column-Level Oracle Virtual Private Database

Column-level policies enforce row-level security when a query references a security-relevant column. You can apply a column-level Oracle Virtual Private Database policy to tables and views, but not to synonyms.

To apply the policy to a column, specify the security-relevant column by using the sec_relevant_columns parameter of the DBMS_RLS.ADD_POLICY procedure. This parameter applies the security policy whenever the column is referenced, explicitly or implicitly, in a query.

For example, users who are not in a Human Resources department typically are allowed to view only their own social security numbers. A sales clerk initiates the following query:

SELECT fname, lname, ssn FROM emp;

The function implementing the security policy returns the predicate ssn='my_ssn'. Oracle Database rewrites the query and executes the following:

SELECT fname, lname, ssn FROM emp 
 WHERE ssn = 'my_ssn';

Example 8-3 shows a Oracle Virtual Private Database policy in which sales department users cannot see the salaries of people outside the department (department number 30) of the sales department users. The relevant columns for this policy are sal and comm. First, the Oracle Virtual Private Database policy function is created, and then it is added by using the DBMS_RLS PL/SQL package.

CREATE OR REPLACE FUNCTION hide_sal_comm (
 v_schema IN VARCHAR2, 
 v_objname IN VARCHAR2)

RETURN VARCHAR2 AS
con VARCHAR2 (200);

BEGIN
 con := 'deptno=30';
 RETURN (con);
END hide_sal_comm;

BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema     => 'scott', 
  object_name       => 'emp',
  policy_name       => 'hide_sal_policy', 
  policy_function   => 'hide_sal_comm',
  sec_relevant_cols => 'sal,comm');
END;

8.3.4.2 Displaying Only the Column Rows Relevant to the Query

The default behavior for column-level Oracle Virtual Private Database is to restrict the number of rows returned for a query that references columns containing sensitive information. You specify these security-relevant columns by using the sec_relevant_columns parameter of the DBMS_RLS.ADD_POLICY procedure, as shown in Example 8-3.

For example, consider sales department users with the SELECT privilege on the emp table, which is protected with the column-level Oracle Virtual Private Database policy created in Example 8-3. The user (for example, user SCOTT) runs the following query:

SELECT ENAME, d.dname, JOB, SAL, COMM 
 FROM emp e, dept d
 WHERE d.deptno = e.deptno;

The database returns the following rows:

ENAME      DNAME          JOB              SAL       COMM
---------- -------------- --------- ---------- ----------
ALLEN      SALES          SALESMAN        1600        300
WARD       SALES          SALESMAN        1250        500
MARTIN     SALES          SALESMAN        1250       1400
BLAKE      SALES          MANAGER         2850           
TURNER     SALES          SALESMAN        1500          0
JAMES      SALES          CLERK            950           
 
6 rows selected.

The only rows that are displayed are those that the user has privileges to access all columns in the row.

8.3.4.3 Using Column Masking to Display Sensitive Columns as NULL Values

If a query references a sensitive column, then the default action of column-level Oracle Virtual Private Database restricts the number of rows returned. With column-masking behavior, all rows display, even those that reference sensitive columns. However, the sensitive columns display as NULL values. To enable column-masking, set the sec_relevant_cols_opt parameter of the DBMS_RLS.ADD_POLICY procedure.

For example, consider the results of the sales clerk query, described in the previous example. If column-masking is used, then instead of seeing only the row containing the details and social security number of the sales clerk, the clerk would see all rows from the emp table, but the ssn column values would be returned as NULL. Note that this behavior is fundamentally different from all other types of Oracle Virtual Private Database policies, which return only a subset of rows.

In contrast to the default action of column-level Oracle Virtual Private Database, column-masking displays all rows, but returns sensitive column values as NULL. To include column-masking in your policy, set the sec_relevant_cols_opt parameter of the DBMS_RLS.ADD_POLICY procedure to dbms_rls.ALL_ROWS.

Example 8-4 shows column-level Oracle Virtual Private Database column-masking. It uses the same VPD policy as Example 8-3, but with sec_relevant_cols_opt specified as dbms_rls.ALL_ROWS.

BEGIN
 DBMS_RLS.ADD_POLICY(
   object_schema         => 'scott', 
   object_name           => 'emp',
   policy_name           => 'hide_sal_policy', 
   policy_function       => 'hide_sal_comm',
   sec_relevant_cols     =>' sal,comm',
   sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
END;

SELECT ENAME, d.dname, job, sal, comm 
 FROM emp e, dept d
 WHERE d.deptno = e.deptno;

ENAME      DNAME          JOB              SAL       COMM
---------- -------------- --------- ---------- ----------
CLARK      ACCOUNTING     MANAGER
KING       ACCOUNTING     PRESIDENT
MILLER     ACCOUNTING     CLERK
JONES      RESEARCH       MANAGER
FORD       RESEARCH       ANALYST
ADAMS      RESEARCH       CLERK
SMITH      RESEARCH       CLERK
SCOTT      RESEARCH       ANALYST
WARD       SALES          SALESMAN        1250        500
TURNER     SALES          SALESMAN        1500          0
ALLEN      SALES          SALESMAN        1600        300
JAMES      SALES          CLERK            950
BLAKE      SALES          MANAGER         2850
MARTIN     SALES          SALESMAN        1250       1400
 
14 rows selected.

• Column-masking applies only to SELECT statements.

• Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates.

• For applications that perform calculations, or do not expect NULL values, use standard column-level Oracle Virtual Private Database, specifying sec_relevant_cols rather than the sec_relevant_cols_opt column-masking option.

• Column-masking used with UPDATE AS SELECT updates only the columns that users are allowed to see.

• For some queries, column-masking may prevent some rows from displaying. For example:

  SELECT * FROM emp
   WHERE sal = 10;
  

  Because the column-masking option was set, this query may not return rows if the salary column returns a NULL value.

8.3.5.1 About Policy Groups

A policy group is a set of security policies that belong to an application. You can designate an application context (known as a driving context or policy context) to indicate the policy group in effect. Then, when a user accesses the table, view, or synonym column, Oracle Database looks up the driving context to determine the policy group in effect. It enforces all the associated policies that belong to the policy group.

Policy groups are useful for situations where multiple applications with multiple security policies share the same table, view, or synonym. This enables you to identify those policies that should be in effect when the table, view, or synonym is accessed.

For example, in a hosting environment, Company A can host the BENEFIT table for Company B and Company C. The table is accessed by two different applications, Human Resources and Finance, with two different security policies. The Human Resources application authorizes users based on ranking in the company, and the Finance application authorizes users based on department. Integrating these two policies into the BENEFIT table requires joint development of policies between the two companies, which is not a feasible option. By defining an application context to drive the enforcement of a particular set of policies to the base objects, each application can implement a private set of security policies.

To do this, you organize security policies into groups. By referring to the application context, Oracle Database determines which group of policies should be in effect at run time. The server enforces all the policies that belong to that policy group.

8.3.5.2 Creating a New Policy Group

To add a policy to a table, view, or synonym, use the DBMS_RLS.ADD_GROUPED_POLICY procedure to specify the group to which the policy belongs. To specify which policies will be effective, you can add a driving context using the DBMS_RLS.ADD_POLICY_CONTEXT procedure. If the driving context returns an unknown policy group, then an error is returned.

If the driving context is not defined, then Oracle Database runs all policies. Likewise, if the driving context is NULL, then policies from all policy groups are enforced. An application accessing the data cannot bypass the security setup module (which sets up application context) to avoid any applicable policies.

You can apply multiple driving contexts to the same table, view, or synonym, and each of them will be processed individually. This enables you to configure multiple active sets of policies to be enforced.

Consider, for example, a hosting company that hosts Benefits and Financial applications, which share some database objects. Both applications are striped for hosting using a SUBSCRIBER policy in the SYS_DEFAULT policy group. Data access is partitioned first by subscriber ID, then by whether the user is accessing the Benefits or Financial applications (determined by a driving context). Suppose that Company A, which uses the hosting services, wants to apply a custom policy that relates only to its own data access. You could add an additional driving context (such as COMPANY A SPECIAL) to ensure that the additional, special policy group is applied for data access for Company A only. You would not apply this under the SUBSCRIBER policy, because the policy relates only to Company A, and it is more efficient to segregate the basic hosting policy from other policies.

8.3.5.3 Example of Implementing a Policy Group

To create a policy group, you must first create a driving context to identify the effective policy group. Then, you can add policies to the policy groups as required.

The following steps show how to implement a policy group:

• Step 1: Create the Components for This Example

• Step 2: Create the Driving Application Context

• Step 3: Add a Policy to the Default Policy Group

• Step 4: Add a Policy to the HR Policy Group

• Step 5: Add a Policy to the FINANCE Policy Group

The following example shows how to perform these tasks.

Step 1: Create the Components for This Example

In SQL*Plus, run the following statements:

DROP USER finance CASCADE;
CREATE USER finance IDENTIFIED BY beancounter4u;
GRANT RESOURCE TO apps;
DROP TABLE apps.benefit;
CREATE TABLE apps.benefit (c NUMBER);

Step 2: Create the Driving Application Context

To create the driving application context, you create a namespace by using the CREATE CONTEXT SQL statement. Remember that you need CREATE ANY CONTEXT privileges to use this statement.

1. Create the namespace for the driving application context:

   CREATE OR REPLACE CONTEXT appsctx USING apps.apps_security_init;
   

2. Create the package that administers the driving context:

   CREATE OR REPLACE PACKAGE apps.apps_security_init IS
    PROCEDURE setctx (policy_group VARCHAR2);
   END;
   
   CREATE OR REPLACE PACKAGE BODY apps.apps_security_init AS
    PROCEDURE setctx ( policy_group varchar2 ) IS
   BEGIN
   
   REM  Do some checking to determine the current application. 
   REM  You can check the proxy if using the proxy authentication feature.
   REM  Then set the context to indicate the current application.
   
     DBMS_SESSION.SET_CONTEXT('APPSCTX','ACTIVE_APPS', policy_group);
    END;
   /
   

3. Define the driving context for the table APPS.BENEFIT:

   BEGIN
   DBMS_RLS.ADD_POLICY_CONTEXT('apps','benefit','APPSCTX','ACTIVE_APPS');
   END;
   /
   

Step 3: Add a Policy to the Default Policy Group

1. Create a security function to return a predicate to divide the data by company:

   CREATE OR REPLACE FUNCTION by_company (
    sch varchar2, 
    tab varchar2)
   RETURN VARCHAR2 AS
   BEGIN
     RETURN 'COMPANY = SYS_CONTEXT(''ID'',''MY_COMPANY'')';
   END;
   /
   

   Because policies in the SYS_DEFAULT policy group are executed (except for SYS, or users with the EXEMPT ACCESS POLICY system privilege), the SECURITY_BY_COMPANY security policy is enforced, regardless of the application running. This achieves the universal security requirement on the table: that each company should see its own data regardless of the application that is running. The function APPS.APPS_SECURITY_INIT.BY_COMPANY returns the predicate to ensure that users can only see data related to their own company.

2. Run the DBMS_RLS.ADD_GROUPED_POLICY procedure:

   BEGIN
   DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','SYS_DEFAULT',
   'security_by_company',
   'apps','by_company');
   END;
   /
   

Step 4: Add a Policy to the HR Policy Group

1. Create a function for the HR policy group:

   CREATE OR REPLACE FUNCTION hr.security_policy 
   RETURN VARCHAR2
   AS
   BEGIN
    RETURN 'SYS_CONTEXT(''ID'',''TITLE'') = ''MANAGER'' ';
   END;
   /
   

2. Create the HR policy group and then add the HR_SECURITY policy to the HR policy group:

   BEGIN
    DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','HR');
    DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','HR',
    'hr_security','hr','security_policy');
   END;
   /
   

   The function HR.SECURITY_POLICY returns the predicate to enforce security on the APPS.BENEFIT table.

Step 5: Add a Policy to the FINANCE Policy Group

1. Create a function for the FINANCE policy group:

   CREATE OR REPLACE FUNCTION finance.security_policy 
   RETURN VARCHAR2
   AS
   BEGIN
    RETURN ('SYS_CONTEXT(''ID'',''DEPT'') = ''FINANCE'' ');
   END;
   

2. Create a policy group named FINANCE, and add the FINANCE policy to the FINANCE group:

   BEGIN
   DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','FINANCE');
   DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','FINANCE',
   'finance_security','finance', 'security_policy');
   END;
   

When a user accesses the database, the application initializes the driving context after authentication. For example, HR application initializes it as follows:

EXECUTE apps.security_init.setctx('HR');

8.3.5.4 Designating a Default Policy Group with the SYS_DEFAULT Policy Group

Within a group of security policies, you can designate one security policy to be the default security policy. This is useful in situations where you partition security policies by application, so that they will be always be in effect. Default security policies allow developers to base security enforcement under all conditions, while partitioning security policies by application (using security groups) enables layering of additional, application-specific security on top of default security policies. To implement default security policies, you add the policy to the SYS_DEFAULT policy group.

Policies defined in this group for a particular table, view, or synonym are run with with the policy group specified by the driving context. As described earlier, a driving context is an application context that indicates the policy group in effect. The SYS_DEFAULT policy group may or may not contain policies. You cannot to drop the SYS_DEFAULT policy group. If you do, then Oracle Database displays an error.

If, to the SYS_DEFAULT policy group, you add policies associated with two or more objects, then each object will have a separate SYS_DEFAULT policy group associated with it. For example, the emp table in the scott schema has one SYS_DEFAULT policy group, and the dept table in the scott schema has a different SYS_DEFAULT policy group associated with it. Think of them as being organized in the tree structure as follows:

SYS_DEFAULT
  - policy1 (scott/emp)
  - policy3 (scott/emp)
SYS_DEFAULT
  - policy2 (scott/dept)

You can create policy groups with identical names. When you select a particular policy group, its associated schema and object name are displayed in the property sheet on the right side of the screen.

8.3.5.5 Establishing Multiple Policies for Each Table, View, or Synonym

You can establish several policies for the same table, view, or synonym. Suppose, for example, you have a base application for Order Entry, and each division of your company has its own rules for data access. You can add a division-specific policy function to a table without having to rewrite the policy function of the base application.

All policies applied to a table are enforced with AND syntax. If you have three policies applied to the CUSTOMERS table, then each policy is applied to the table. You can use policy groups and an application context to partition fine-grained access control enforcement so that different policies apply, depending upon which application is accessing data. This eliminates the requirement for development groups to collaborate on policies, and simplifies application development. You can also have a default policy group that is always applicable (for example, to enforce data separated by subscriber in a hosting environment).

8.3.5.6 Validating the Application Used to Connect to the Database

The package implementing the driving context must correctly validate the application that is being used to connect to the database. Although Oracle Database checks the call stack to ensure that the package implementing the driving context sets context attributes, inadequate validation can still occur within the package.

For example, in applications where database users or enterprise users are known to the database, the user needs the EXECUTE privilege on the package that sets the driving context. Consider a user who knows that:

• The BENEFITS application enables more liberal access than the HR application.

• The setctx procedure (which sets the correct policy group within the driving context) does not perform any validation to determine which application is actually connecting. That is, the procedure does not check either the IP address of the incoming connection (for a three-tier system) or the proxy_user attribute of the user session.

This user could pass to the driving context package an argument setting the context to the more liberal BENEFITS policy group, and then access the HR application instead. Because the setctx does no further validation of the application, this user bypasses the more restrictive HR security policy.

By contrast, if you implement proxy authentication with Oracle Virtual Private Database, then you can determine the identity of the middle tier (and the application) that is connecting to the database on behalf of a user. The correct policy will be applied for each application to mediate data access.

For example, a developer using the proxy authentication feature could determine that the application (the middle tier) connecting to the database is HRAPPSERVER. The package that implements the driving context can thus verify whether the proxy_user in the user session is HRAPPSERVER. If so, then it can set the driving context to use the HR policy group. If proxy_user is not HRAPPSERVER, then it can deny access.

In this case, the following query is executed:

SELECT * FROM apps.benefit;

Oracle Database picks up policies from the default policy group (SYS_DEFAULT) and active namespace HR. The query is internally rewritten as follows:

SELECT * FROM apps.benefit 
 WHERE company = SYS_CONTEXT('ID','MY_COMPANY') 
 and SYS_CONTEXT('ID','TITLE') = 'MANAGER';

8.3.6.1 Using the Dynamic Policy Type to Automatically Rerun Policy Functions

The DYNAMIC policy type runs the policy function each time a user accesses the Virtual Private Database-protected database objects. If you do not specify a policy type in the DBMS_RLS.ADD_POLICY procedure, then, by default, your policy will be dynamic. You can specifically configure a policy to be dynamic by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to DYNAMIC.

This policy type does not optimize database performance as the static and context sensitive policy types do. However, Oracle recommends that before you set policies as either static or context-sensitive, you should first test them as DYNAMIC policy types, which run every time. Testing policy functions as DYNAMIC policies first enables you to observe how the policy function affects each query, because nothing is cached. This ensures that the functions work properly before you enable them as static or context-sensitive policy types to optimize performance.

You can use the DBMS_UTILITY.GET_TIME procedure to measure the start and end times for a statement to execute. For example:

SQL> SELECT DBMS_UTILITY.GET_TIME FROM dual;

  GET_TIME
----------
   2312721

SQL> SELECT COUNT(*) FROM hr.employees;

  COUNT(*)
----------
       107

SQL> SELECT DBMS_UTILITY.GET_TIME FROM dual;

  GET_TIME
----------
   2314319

Example 8-5 shows how to create the DYNAMIC policy type.

BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.DYNAMIC);
END;

8.3.6.2 Using a Static Policy to Prevent Policy Functions from Rerunning for Each Query

The static policy type enforces the same predicate for all users in the instance. Oracle Database stores static policy predicates in SGA, so policy functions do not rerun for each query. This results in faster performance.

You can enable static policies by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to either STATIC or SHARED_STATIC, depending on whether or not you want the policy to be shared across multiple objects.

Example 8-6 shows how to create the STATIC policy type.

BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.STATIC);
END;

Each execution of the same cursor could produce a different row set for the same predicate, because the predicate may filter the data differently based on attributes such as SYS_CONTEXT or SYSDATE.

For example, suppose you enable a policy as either a STATIC or SHARED_STATIC policy type, which appends the following predicate to all queries made against policy protected database objects:

WHERE dept = SYS_CONTEXT ('hr_app','deptno')

Although the predicate does not change for each query, it applies to the query based on session attributes of the SYS_CONTEXT. In the case of the preceding example, the predicate returns only those rows where the department number matches the deptno attribute of the SYS_CONTEXT, which is the department number of the user who is querying the policy-protected database object.

8.3.6.3 Using a Shared Static Policy to Share a Policy with Multiple Objects

If, for example, you wanted to apply the policy in Example 8-6 to a second table in the HR schema that may contain financial data that you want to side, you would use the SHARED_STATIC setting for both tables.

Example 8-7 shows how to set the SHARED_STATIC policy type for two tables that share the same policy.

-- Create a policy for the first table, employees:
BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.SHARED_STATIC);
END;

-- Now create a policy for the second table, fin_data:
BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'fin_data',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.SHARED_STATIC);
END;

8.3.6.4 When to Use Static and Shared Static Policies

Static policies are ideal for environments where every query requires the same predicate and fast performance is essential, such as hosting environments. For these situations when the policy function appends the same predicate to every query, rerunning the policy function each time adds unnecessary overhead to the system. For example, consider a data warehouse that contains market research data for customer organizations that are competitors. The warehouse must enforce the policy that each organization can see only their own market research, which is expressed by the following predicate:

WHERE subscriber_id = SYS_CONTEXT('customer', 'cust_num')

Using SYS_CONTEXT for the application context enables the database to dynamically change the rows that are returned. You do not need to rerun the function, so the predicate can be cached in the SGA, thus conserving system resources and improving performance.

8.3.6.5 Using a Context-Sensitive Policy for Predicates That Do Not Change After Parsing

In contrast to static policies, context-sensitive policies do not always cache the predicate. With context-sensitive policies, the database assumes that the predicate will change after statement parse time. But if there is no change in local application context, Oracle Database does not rerun the policy function within the user session. If there was a change in context, then the database reruns the policy function to ensure that it captures any changes to the predicate since the initial parsing.

You can enable context-sensitive policies by setting the policy_type parameter of the DBMS_RLS.ADD_POLICY procedure to either CONTEXT_SENSITIVE or SHARED_CONTEXT_SENSITIVE.

Example 8-8 shows how to create the CONTEXT_SENSITIVE policy type.

BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.CONTEXT_SENSITIVE);
END;

Context-sensitive policies are useful when different predicates should apply depending on which user is executing the query. For example, consider the case where managers should have the predicate WHERE group set to managers, and employees should have the predicate WHERE empno set to emp_id.

Shared context-sensitive policies operate in the same way as regular context-sensitive policies, except they can be shared across multiple database objects. For this policy type, all objects can share the policy function from the UGA, where the predicate is cached until the local session context changes.

8.3.6.6 Using a Shared Context Sensitive Policy to Share a Policy with Multiple Objects

Example 8-9 Ishows how to create two shared context sensitive policies that share a policy with multiple tables.

-- Create a policy for the first table, employees:
BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'employees',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.SHARED_CONTEXT_SENSITIVE);
END;

-- Now create a policy for the second table, fin_data:
BEGIN
 DBMS_RLS.ADD_POLICY(
  object_schema   => 'hr',
  object_name     => 'fin_data',
  policy_name     => 'secure_update',
  policy_function => 'hide_fin',
  policy_type     => dbms_rls.SHARED_CONTEXT_SENSITIVE);
END;

8.3.6.7 When to Use Context-Sensitive and Shared Context-Sensitive Policies

Context-sensitive policies are useful when a predicate does not need to change for a user session, but the policy must enforce two or more different predicates for different users or groups. For example, consider a sales_history table with a single policy. This policy states that analysts can see only their own products and regional employees can see only their own region. In this case, the database must rerun the policy function each time the type of user changes. The performance gain is realized when a user can log in and issue several DML statements against the protected object without causing the server to rerun the policy function.

8.3.6.8 Summary of the Five Oracle Virtual Private Database Policy Types

Table 8-2 summarizes the types of policy types available.

^Footnote 1 Each execution of the same cursor could produce a different row set for the same predicate because the predicate may filter the data differently based on attributes such as SYS_CONTEXT or SYSDATE.

8.4.1.1 Step 1: Ensure That the OE User Account Is Active

1. Log on to SQL*Plus as SYS and connect using the AS SYSDBA privilege.

   sqlplus "SYS/AS SYSDBA"
   Enter password: password
   

2. Run the following SELECT statement on the DBA_USERS data dictionary view:

   SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'OE';
   

   If the DBA_USERS view lists user OE as locked and expired, then enter the following statement to unlock the OE account and create a new password for him:

   ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY ready2go;
   

   The password ready2go is offered as an example of a valid password, but you can create any password that is secure, according to the requirements described in "How Oracle Database Checks the Complexity of Passwords".

8.4.1.2 Step 2: Create a Policy Function

Create the following function, which will append the WHERE SALES_REP_ID = 159 clause to any SELECT statement on the OE.ORDERS table.

SQL> CREATE OR REPLACE FUNCTION auth_orders(
  2  schema_var IN VARCHAR2,
  3  table_var  IN VARCHAR2
  4 )
  5 RETURN VARCHAR2
  6 IS
  7  return_val VARCHAR2 (400);
  8 BEGIN
  9  return_val := 'SALES_REP_ID = 159';
 10  RETURN return_val;
 11 END auth_orders;

In this example:

• Lines 2–3: Create input parameters to specify to store the schema name, OE, and table name, ORDERS. First, define the parameter for the schema, and then define the parameter for the object, in this case, a table. Always create them in this order. The Virtual Private Database policy you create will need these parameters to specify the OE.ORDERS table.

• Line 5: Returns the string that will be used for the WHERE predicate clause. Remember that return value is always a VARCHAR2 data type.

• Lines 6–10: Encompass the creation of the WHERE SALES_REP_ID = 159 predicate.

8.4.1.3 Step 3: Create the Oracle Virtual Private Database Policy

Next, create the following policy by using the ADD_POLICY procedure in the DBMS_RLS package.

SQL> BEGIN
  2   DBMS_RLS.ADD_POLICY (
  3     object_schema    => 'oe',
  4     object_name      => 'orders',
  5     policy_name      => 'orders_policy',
  6     function_schema  => 'sys',
  7     policy_function  => 'auth_orders',
  8     statement_types  => 'select, insert, update, delete'
  9   );
 10  END;

In this example:

• Line 3: Specifies the schema that you want to protect, that is, OE.

• Line 4: Specifies the object within the schema to protect, that is, the ORDERS table.

• Line 5: Names this policy orders_policy.

• Line 6: Specifies the schema in which the auth_orders function was created. In this example, auth_orders was created in the SYS schema. But typically, it should be created in the schema of a security administrator.

• Line 7: Specifies a function to enforce the policy. Here, you specify the auth_orders function that you created in Step 2: Create a Policy Function.

• Line 8: Specifies the operations to which the policy applies. In this example, the policy applies to all SELECT, INSERT, UPDATE, and DELETE statements the user may perform.

8.4.1.4 Step 4: Test the Policy

After you create the Oracle Virtual Private Database policy, it goes into effect immediately. The next time a user, including the owner of the schema, performs a SELECT on OE.ORDERS, only the orders by Sales Representative 159 will be accessed.

1. Log on as user OE.

   CONNECT oe
   Enter password: password
   Connected. 
   

2. Enter the following SELECT statement:

   SELECT COUNT(*) FROM ORDERS;
   
    COUNT(*)
   ---------
           7
   

   The policy is in effect for user OE: As you can see, only 7 of the 105 rows in the orders table are returned.

   But users with administrative privileges still have access to all the rows in the table.

3. Log back on as user SYS.

   CONNECT SYS/AS SYSDBA
   Enter password: password
   Connected.
   

4. Enter the follow SELECT statement:

   SELECT COUNT(*) FROM ORDERS;
   
    COUNT(*)
   ---------
         105 
   

8.4.1.5 Step 5: Remove the Components for This Example

1. As user SYS, remove the function and policy as follows:

   DROP FUNCTION auth_orders;
   EXEC DBMS_RLS.DROP_POLICY('OE','ORDERS','ORDERS_POLICY');
   

2. If you need to lock and expire the OE account, enter the following statement:

   ALTER USER OE ACCOUNT LOCK PASSWORD EXPIRE;
   

8.4.2.1 Step 1: Create User Accounts and Sample Tables

1. Start SQL*Plus and log on as a user who has administrative privileges.

   sqlplus "SYS/AS SYSDBA"
   Enter password: password
   Connected.
   

2. Create the following administrative user, who will administer the Oracle Virtual Private Database policy.

   GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_vpd IDENTIFIED BY omni2all;
   GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;
   GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;
   

3. Create the following user accounts:

   GRANT CREATE SESSION TO tbrooke IDENTIFIED BY shop2drop;
   GRANT CREATE SESSION TO owoods IDENTIFIED BY loads4me;
   

4. Check the status of the sample user scott, who will be used for this example:

   SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'SCOTT';
   

   If the DBA_USERS view lists user scott as locked and expired, then enter the following statement to unlock the scott account and create a new password for him:

   ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY tgris86d;
   

   The password tgris86d is offered as an example of a valid password, but you can create any password that is secure, according to the requirements described in "How Oracle Database Checks the Complexity of Passwords".

5. Connect as user SCOTT, and then create and populate the customers table.

   CONNECT scott
   Enter password: tgris86d
   Connected.
   
   CREATE TABLE customers (
    cust_no    NUMBER(4), 
    cust_email VARCHAR2(20),
    cust_name  VARCHAR2(20));
   
   INSERT INTO customers VALUES (1234, 'TBROOKE', 'Thadeus Brooke');
   INSERT INTO customers VALUES (5678, 'OWOODS', 'Oberon Woods');
   

6. User sysadmin_vpd will need select privileges for the customers table, so as user scott, grant him this privilege.

   GRANT SELECT ON customers TO sysadmin_vpd;
   

7. Create and populate the orders_tab table.

   CREATE TABLE orders_tab (
     cust_no  NUMBER(4),
     order_no NUMBER(4));
   
   INSERT INTO orders_tab VALUES (1234, 9876);
   INSERT INTO orders_tab VALUES (5678, 5432);
   INSERT INTO orders_tab VALUES (5678, 4592);
   

8. Users tbrooke and owoods need to query the orders_tab table, so grant them the SELECT privilege.

   GRANT SELECT ON orders_tab TO tbrooke;
   GRANT SELECT ON orders_tab TO owoods;
   

At this stage, the two sample customers, tbrooke and owoods, have a record of purchases in the orders_tab order entry table, and if they tried right now, they can see all the orders in this table.

8.4.2.2 Step 2: Create a Database Session-Based Application Context

1. Connect as user sysadmin_vpd.

   CONNECT sysadmin_vpd
   Enter password: omni2all
   Connected. 
   

2. Enter the following statement:

   CREATE OR REPLACE CONTEXT orders_ctx USING orders_ctx_pkg;
   

   This statement creates the orders_ctx application context. Remember that even though user sysadmin_vpd has created this context and it is associated with the sysadmin_vpd schema, the SYS schema owns the application context.

8.4.2.3 Step 3: Create a PL/SQL Package to Set the Application Context

As user sysadmin_vpd, create the following PL/SQL package, which will set the database session-based application context when the customers tbrooke and owoods log onto their accounts.

SQL> CREATE OR REPLACE PACKAGE orders_ctx_pkg IS
  2   PROCEDURE set_custnum;
  3  END;
  4/
  5 CREATE OR REPLACE PACKAGE BODY orders_ctx_pkg IS
  6   PROCEDURE set_custnum
  7   AS
  8     custnum NUMBER;
  9   BEGIN
 10      SELECT cust_no INTO custnum FROM scott.customers 
 11         WHERE cust_email = SYS_CONTEXT('USERENV', 'SESSION_USER');
 12      DBMS_SESSION.SET_CONTEXT('orders_ctx', 'cust_no', custnum);
 13   END set_custnum;
 14 END;
 17 /

In this example:

• Line 8: Creates the custnum variable, which will hold the customer ID.

• Line 10: Performs a SELECT statement to copy the customer ID that is stored in the cust_no column data from the scott.customers table into the custnum variable.

• Line 11: Uses a WHERE clause to find all the customer IDs that match the user name of the user who is logging on.

• Line 12: Sets the order_entry application context values by creating the cust_no attribute and then setting it to the value stored in the custnum variable.

To summarize, the sysadmin_vpd.set_cust_num procedure says, "Get the session user ID of the user, and then find the customer user name matches this session user ID. If they are the same, then let the user log on. If not, then deny the user access."

8.4.2.4 Step 4: Create a Logon Trigger for the Application Context PL/SQL Package

The logon trigger runs the procedure in the PL/SQL package that you created in Step 3: Create a PL/SQL Package to Set the Application Context the next time a user logs on.

As user sysadmin_vpd, create the following trigger:

CREATE TRIGGER set_custno_ctx_trig AFTER LOGON ON DATABASE
 BEGIN
  sysadmin_vpd.orders_ctx_pkg.set_custnum;
 END;
 /

8.4.2.5 Step 5: Create a PL/SQL Policy Function to Limit User Access to Their Orders

At this stage, only customers who are listed in the sysadmin_vpd.customers table can log in to the database. The next step is to create a PL/SQL function that, when the user who has logged in performs a SELECT * FROM scott.orders_tab query, displays only the orders of that user.

As user sysadmin_vpd, create the following function:

CREATE OR REPLACE FUNCTION get_user_orders(
  schema_p   IN VARCHAR2,
  table_p    IN VARCHAR2)
 RETURN VARCHAR2
 AS
  orders_pred VARCHAR2 (400);
 BEGIN
  orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')'; 
 RETURN orders_pred;
END;
/

This function creates and returns a WHERE predicate that translates to "WHERE the orders displayed belong to the user who has logged in." It then appends this WHERE predicate to any queries this user may run against the scott.orders_tab table. Next, you need to create an Oracle Virtual Private Database policy that applies this function to the orders_tab table.

8.4.2.6 Step 6: Create the New Security Policy

As user sysadmin_vpd, create the policy as follows:

BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema    => 'scott', 
  object_name      => 'orders_tab', 
  policy_name      => 'orders_policy', 
  function_schema  => 'sysadmin_vpd',
  policy_function  => 'get_user_orders', 
  statement_types  => 'select');
END;
/

This statement creates a policy named orders_policy and applies it to the orders_tab table, which customers will query for their orders, in the SCOTT schema. The get_user_orders function implements the policy, which is stored in the sysadmin_vpd schema. The policy further restricts users to issuing SELECT statements only.

8.4.2.7 Step 7: Test the New Policy

1. Log on as user tbrooke.

   CONNECT tbrooke
   Enter password: shop2drop
   Connected.
   

   User tbrooke can log on because he has passed the requirements you defined in the application context.

2. As user tbrooke, access your purchases.

   SELECT * FROM scott.orders_tab;
   
   VALUES
   ------
   9876
   

   User tbrooke has passed the second test. He can access his own orders in the scott.orders_tab table.

3. Log on as user owoods, and then access your purchases.

   CONNECT owoods
   Enter password: loads4me
   
   SELECT * FROM scott.orders_tab
   
   VALUES
   ------
   5432
   4592
   

   As with user tbrooke, user owoods can log on and see a listing of his own orders.

Note the following about this example:

• You can create several predicates based on the position of a user. For example, a sales representative would be able to see records only for his customers, and an order entry clerk would be able to see any customer order. You could expand the custnum_sec function to return different predicates based on the user position context value.

• The use of an application context in a fine-grained access control package effectively gives you a bind variable in a parsed statement. For example:

  SELECT * FROM orders_tab 
     WHERE custno = SYS_CONTEXT('order_entry', 'cust_num')
  

  This is fully parsed and optimized, but the evaluation of the cust_num attribute value of the user for the order_entry context takes place at run-time. This means that you get the benefit of an optimized statement that executes differently for each user who issues the statement.

  Note:

  You can improve the performance of the function in this example by indexing cust_no.

• You can set context attributes based on data from a database table or tables, or from a directory server using Lightweight Directory Access Protocol (LDAP).

Compare and contrast this example, which uses an application context within the dynamically generated predicate, with "About Oracle Virtual Private Database Policies", which uses a subquery in the predicate.

8.4.2.8 Step 8: Remove the Components for This Example

1. Connect as user SYS, connecting with AS SYSDBA.

   CONNECT SYS/AS SYSDBA
   Enter password: password
   Connected.
   

2. Run the following statements to drop the components for this example:

   DROP CONTEXT orders_ctx;
   DROP USER sysadmin_vpd CASCADE;
   DROP USER tbrooke CASCADE;
   DROP USER owoods CASCADE;
   DROP TABLE scott.orders_tab;
   DROP TABLE scott.customers;