Oracle Virtual Private Database (VPD):

                  Oracle virtual private database(VPD) enables to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

When a user directly or indirectly accesses a table, view, or synonym that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a WHERE condition (called a predicate) returned by a function implementing the security policy. Oracle Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function.

Simple Example:

Oracle Virtual Private Database policies can be applied to SELECT, INSERT, UPDATE, INDEX and DELETE statements.

 Benefits of  Using Virtual Private Database(VPD):

Ø  Oracle VPD has several powerful security features – fine grained access control (FGAC), application context and global application context. VPD policies are written using PL/SQL, and can be assigned to an individual table or view. Information requests which reference tables and views protected by VPD are modified according to the policy assigned to this table or view. VPD policies can be as simple as enforcing access during business hours.

Ø  VPD policies can restrict access by comparing the value of an attribute in an individual row with an application context value. Global application context allows an application context to be accessed across multiple database sessions, reducing or eliminating the need to create a separate application context for each user session.

Ø  VPD is used when the standard object privileges and associated database roles are insufficient to meet application security requirements. VPD policies can be simple or complex depending on your security requirements. VPD can be used in combination with the "application context" feature to enforce sophisticated row and/or column level security requirements for privacy and regulatory compliance.

Ø  Oracle VPD is provided at no cost with the Oracle Enterprise Edition.

Advantages of VPD:

Ø  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.

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:

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

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

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

Which Privileges Are Used to Run Oracle Virtual Private Database Policy Functions?

                    For greater security, the Oracle Virtual Private Database policy function runs as if it had been declared with definer's rights. Do not declare it as invoker's rights because this can confuse yourself and other users who maintain the code.

Definer rights:

A PL/SQL program must be compiled and stored in the database before it can be executed. Any
reference in the code is resolved at compile time. This means the program must have directly
granted access to these objects. They are not resolved using roles granted to the definer or
owner of the program. Although the references are checked against the rights the owner has,
you can still grant execute rights to different schemas or users. Keep in mind though that when
another user executes the code, it runs under the authority of the definer of the code. This
means this user has access to all the objects needed by the code, while the user might not have
access to these objects either through directly granted privileges or through a role. This may be
a good thing when you want to make sure the user accesses tabledata only through a defined
interface (Table API) but if you want the user to see certain parts of the data, when different
parts should be hidden from him/her it means either building more code to hide parts of the data
or resort to Virtual Private Databases.

Invoker rights:

The other model available to you is the Invoker Rights model. In this model the code is executed under the authority of the schema (or user) that started the code. This means that all objects needed by the code need to be available to the invoker of the code. References are checked to the references available to the rights the invoker has, instead of the rights the definer of the  code has. This means that every user that executes the code can have different objects
available than the ones available to the definer. If the code, for instance, references a table then
this table can be a completely different object than the one used by the compiling schema. This
way you can make sure the user only has access to his own data. If multiple users use the
same code to reference, for instance, a clients table, the different users will see different results.
This way you can easily implement a model where users have access to only their own data,
without the need for Virtual Private Databases or more code. Be aware though that when you
call a Definer Rights defined program from within an Invoker Rights program; from that time
on you will be using the rights for that Definer. If you are to call another Invoker rights program it will be executed under the authority of that Definer, rather then the Invokers authority. If this
program calls an Invoker Rights program it will be executed under the authority of the Definer of
the program that was just called rather than the authority of the original invoker.

Using Oracle Virtual Private Database with an Application Context:                 

                            You can use application contexts with Oracle Virtual Private Database policies. When you create an application context, it securely caches user information. Only the designated application package can set the cached environment. It cannot be changed by the user or outside the package. In addition, because the data is cached, performance is increased.

                       For example, suppose  if  you want to base access to the ORDERS_TAB table on the customer ID number. Rather than querying the customer ID number for a logged-in user each time you need it, you could store the number in the application context. Then, the customer number is available in the session when you need it.

Ø  Definition of application context: An application context is a set of name-value pairs that Oracle Database stores in memory. The application context has a label called a namespace.

For Example:

v  If empno_ctx for an application context that retrieves employee IDs. Inside the context are the name-value pairs (an associative array):

v  Name points to a location in memory that holds the value.

v  An application can use the application context to access session information about a user, such as the user ID or other user-specific information, or a client ID, and then securely pass this data to the database. You can then use this information to either permit or prevent the user from accessing data through the application.

v  You can use application contexts to authenticate both database and non database users.

Where Are the Application Context Values Stored?

                 Oracle Database stores the application context values in a secure data cache available in the User Global Area (UGA) or the System (sometimes called "Shared") Global Area (SGA).

By this way the application context values are retrieved during the session. Because the application context stores the values in this data cache, it increases performance for your applications.

You can use an application context by itself, with Oracle Virtual Private Databases policies, or with other fine-grained access control policies.

Application contexts are useful for the following purposes:

v  Enforcing fine-grained access control, for example, in Oracle Virtual Private Database polices

v  Preserving user identity across multitier environments

v  Enforcing stronger security for your applications, because the application context is controlled by a trusted procedure, not the user

v  Increasing performance by serving as a secure data cache for attributes needed by an application for fine-grained auditing or for use in PL/SQL conditional statements or loops.

v  Serving as a holding area for name-value pairs that an application can define, modify, and access.

2. Components of an Oracle Virtual Private Database Policy:

               To implement Oracle Virtual Private Database, you must create a function to generate the dynamic WHERE clause and a policy to attach this function to the objects that you want to protect.

Ø  Creating a Function to Generate the Dynamic WHERE Clause.

Ø  Creating a Policy to Attach the Function to the Objects You Want to Protect.

Creating a Function to Generate the Dynamic WHERE Clause:

                To generate the dynamic WHERE clause (predicate), you must create a function (not a procedure) that defines the restrictions that you want to enforce. Usually, the security administrator creates this function in his or her own schema.

The function must have the following behavior:

Ø  It must take as arguments a schema name and an object (table, view, or synonym) name as inputs: Define input parameters to hold this information, but do not specify the schema and object name themselves within the function. The policy that you create with the DBMS_RLS package provides the names of the schema, and object to which the policy will apply. You must create the parameter for the schema first, followed by the parameter for the object.

Ø  It must provide a return value for the WHERE clause predicate that will be generated: The return value for the WHERE clause is always a VARCHAR2 data type.

Ø  It must generate a valid WHERE clause: For  designing  the WHERE clause to be different for each user, each group of users, or each application that accesses the objects you want to protect.

Ø  It must not select from a table within the associated policy function:

  Although you can define a policy against a table, you cannot select that table from within the policy that was defined against the table.

Creating a Policy to Attach the Function to the Objects You Want to Protect: 

           After you create the function, you need to create an Oracle Virtual Private Database policy that associates the function with a table, view, or synonym. You create the policy by using the DBMS_RLS package. If you are not SYS, then you must be granted EXECUTE privileges to use the DBMS_RLS package. This package contains procedures that enable you to manage the policy and set fine-grained access control.

The combination of creating the function and then applying it to a table or view is referred to as creating the Oracle Virtual Private Database policy.

Creating a Simple Oracle Virtual Private Database Policy:

        For   Example:

                           If u want to create a simple Oracle Virtual Private Database policy that limits access to all orders in the OE.ORDERS table that were created by Sales Representative 159.

   So, the  policy translates the following statement:

          SELECT * FROM OE.ORDERS;

To the following statement:

    SELECT * FROM OE.ORDERS

    WHERE SALES_REP_ID = 159;

Step 1: Ensure That the OE User Account Is Active

1.       Log on to SQL*Plus as user SYS with the SYSDBA privilege.

2.  sqlplus sys as sysdba

3.  Enter password: password

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

5.  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:

ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;

             Replace password with a password that is secure. For greater security, do not reuse the same password that was used in previous releases of Oracle Database.

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.

1 2 3 4 5 6 7 8 9 10 11 12

CREATE OR REPLACE FUNCTION auth_orders(   schema_var IN VARCHAR2,   table_var  IN VARCHAR2 ) RETURN VARCHAR2 IS   return_val VARCHAR2 (400); BEGIN   return_val := 'SALES_REP_ID = 159';   RETURN return_val; END auth_orders; /

In this example:

v  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.

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

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

Step 3: Create the Oracle Virtual Private Database Policy

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

1 2 3 4 5 6 7 8 9 10 11

BEGIN   DBMS_RLS.ADD_POLICY (     object_schema    => 'oe',     object_name      => 'orders',     policy_name      => 'orders_policy',     function_schema  => 'sys',     policy_function  => 'auth_orders',     statement_types  => 'select, insert, update, delete'    ); END; /

In this Example:

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

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

v  Line 5: Names this policy orders_policy.

v  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.

v  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.

v  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.

Step 4: Test the Policy

After creating 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.

2.      Connect oe.

3.      Enter password: password.

4.      Enter the following  select statement.

SELECT COUNT(*) FROM ORDERS;

The following output should appear is:

COUNT(*)--------7

The policy is in effect for user OE.ORDERS  only the orders by sales representative 159: 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. so it will return all 105 rows.

Step 5: Remove the Components(policy and function)

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

2.  DROP FUNCTION auth_orders;

3.  EXEC DBMS_RLS.DROP_POLICY('OE','ORDERS','ORDERS_POLICY');

4.     If you need to lock and expire the OE account, then enter the following statement.

5.  ALTER USER OE ACCOUNT LOCK PASSWORD EXPIRE;

 

Five Oracle Virtual Private Database Policy Types

DBMS_RLS.ADD_POLICY Policy Types

Policy Types	When the Policy Function Executes	Usage Example	Shared Across Multiple Objects?
DYNAMIC	Policy function re-executes every time a policy-protected database object is accessed.	Applications where policy predicates must be generated for each query, such as time-dependent policies where users are denied access to database objects at certain times during the day	No
STATIC	Once, then the predicate is cached in the SGA	View replacement	No
SHARED_STATIC	Same as STATIC	Hosting environments, such as data warehouses where the same predicate must be applied to multiple database objects	Yes
CONTEXT_SENSITIVE	At statement parse time . At statement execution time (When  the local application context changed since the last use of the cursor)	Three-tier, session pooling applications where policies enforce two or more predicates for different users or groups	No
SHARED_CONTEXT_SENSITIVE	First time the object is reference in a database session. Predicates are cached in the private session memory UGA so policy functions can be shared among objects.	Same as CONTEXT_SENSITIVE, but multiple objects can share the policy function from the session UGA	Yes

3. Configuring an Oracle Virtual Private Database Policy.

This section contains :

A.      About Oracle Virtual Private Database Policies.

B.      Attaching a Policy a Database Table, View, or Synonym.

C.      Enforcing Policies on Specific SQL Statement Types.

D.      Controlling the Display of Column Data with Policies.

E.       Working with Oracle Virtual Private Database Policy Groups.

F.       Optimizing Performance by Using Oracle Virtual Private Database Policy Types.

3.A.  About Oracle Virtual Private Database Policies:

              After creating the  function that defines the actions of the Oracle Virtual Private Database WHERE clause, you must associate this function with the database table to which the VPD action applies. You can do this by configuring an Oracle Virtual Private Database policy. The policy itself is a mechanism for managing the Virtual Private Database function. The policy also enables you to add fine-grained access control, such as specifying the types of SQL statements or particular table columns the policy affects. When a user tries to access the data in this database object, the policy goes into effect automatically.

      To manage an Oracle Virtual Private Database policy, you can use the “DBMS_RLS” package.

 DBMS_RLS:

        The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement Virtual Private Database (VPD). DBMS_RLS is available with the Enterprise Edition only.

Overview of   DBMS_RLS:

       The functionality to support fine-grained access control is based on dynamic predicates, where security rules are not embedded in views, but are acquired at the statement parse time, when the base table or view is referenced in a DML statement.

A dynamic predicate for a table, view, or synonym is generated by a PL/SQL function, which is associated with a security policy through a PL/SQL interface. For example:

DBMS_RLS.ADD_POLICY (

   'hr', 'employees', 'emp_policy', 'hr', 'emp_sec', 'select');

Whenever the EMPLOYEES table, under the HR schema, is referenced in a query or subquery (SELECT), the server calls the EMP_SEC function (under the HR schema). This function returns a predicate specific to the current user for the EMP_POLICY policy. The policy function may generate the predicates based on the session environment variables available during the function call. These variables usually appear in the form of application contexts. The policy can specify any combination of security-relevant columns and of these statement types: INDEX, SELECT, INSERT, UPDATE, or DELETE.

The server then produces a transient view with the text:

SELECT * FROM hr.employees WHERE P1

Here, P1 (for example, where SAL > 10000, or even a subquery) is the predicate returned from the EMP_SEC function. The server treats the EMPLOYEES table as a view and does the view expansion just like the ordinary view, except that the view text is taken from the transient view instead of the data dictionary.

If the predicate contains subqueries, then the owner (definer) of the policy function is used to resolve objects within the subqueries and checks security for those objects. In other words, users who have access privilege to the policy-protected objects do not need to know anything about the policy. They do not need to be granted object privileges for any underlying security policy. Furthermore, the users do not require EXECUTE privilege on the policy function, because the server makes the call with the function definer's right.

DBMS_RLS also provides the interface to drop or enable security policies. For example, you can drop or enable the EMP_POLICY with the following PL/SQL statements:

DBMS_RLS.DROP_POLICY('hr', 'employees', 'emp_policy');

DBMS_RLS.ENABLE_ POLICY('hr', 'employees', 'emp_policy', FALSE);

Security Model

         A security check is performed when the transient view is created with a subquery. The schema   owning the policy function, which generates the dynamic predicate, is the transient view's definer for security check and object lookup.

Operational Notes

       The DBMS_RLS procedures cause current DML transactions, if any, to commit before the operation. However, the procedures do not cause a commit first if they are inside a DDL event trigger. With DDL transactions, the DBMS_RLS procedures are part of the DDL transaction.
 For example, you may create a trigger for CREATE TABLE. Inside the trigger, you may add a column through ALTER TABLE, and you can add a policy through DBMS_RLS. All these operations are in the same transaction as CREATE TABLE, even though each one is a DDL statement. The CREATE TABLE succeeds only if the trigger is completed successfully.
Views of current cursors and corresponding predicates are available from “v$vpd_policies”.

DBMS_RLS Procedures:

Procedure	Description
For Handling Individual Policies
DBMS_RLS.ADD_POLICY	Adds a policy to a table, view, or synonym
DBMS_RLS.ENABLE_POLICY	Enables (or disables) a policy you previously added to a table, view, or synonym
DBMS_RLS.REFRESH_POLICY	Invalidates cursors associated with nonstatic policies
DBMS_RLS.DROP_POLICY	To drop a policy from a table, view, or synonym
For Handling Grouped Policies
DBMS_RLS.CREATE_POLICY_GROUP	Creates a policy group
DBMS_RLS.DELETE_POLICY_GROUP	Drops a policy group
DBMS_RLS.ADD_GROUPED_POLICY	Adds a policy to the specified policy group
DBMS_RLS.ENABLE_GROUPED_POLICY	Enables a policy within a group
DBMS_RLS.REFRESH_GROUPED_POLICY	Parses again the SQL statements associated with a refreshed policy
DBMS_RLS.DISABLE_GROUPED_POLICY	Disables a policy within a group
DBMS_RLS.DROP_GROUPED_POLICY	Drops a policy that is a member of the specified group
For Handling Application Contexts
DBMS_RLS.ADD_POLICY_CONTEXT	Adds the context for the active application
DBMS_RLS.DROP_POLICY_CONTEXT	Drops the context for the application

3.B. Attaching a Policy a Database Table, View, or Synonym.

To attach a policy to a table, view, or synonym, you use the DBMS_RLS.ADD_POLICY procedure. You must specify the table, view, or synonym to which you are adding a policy, and a name for the policy. You can also specify other information, such as the types of statements the policy controls (SELECT, INSERT, UPDATE, DELETE, CREATE INDEX, or ALTER INDEX).
 This examples shows how to use DBMS_RLS.ADD_POLICY to attach an Oracle Virtual Private Database policy called secure_update to the HR.EMPLOYEES table. The function attached to the policy is check_updates.

Example for Attaching a Simple Oracle Virtual Private Database Policy to a Table

BEGIN

 DBMS_RLS.ADD_POLICY(

  object_schema   => 'hr',

  object_name     => 'employees',

  policy_name     => 'secure_update',

  policy_function => 'check_updates',

...

If the function was created inside a package, include the package name. For example:

policy_function => 'pkg.check_updates',

3. C. Enforcing Policies on Specific SQL Statement Types

        You can enforce Oracle Virtual Private Database policies for SELECT, INSERT, UPDATE, INDEX and  DELETE statements. If you do not specify a statement type, by default, Oracle Database specifies SELECT, INSERT, UPDATE and DELETE,but not INDEX. Enter any combination of these statement types by using the statement_types parameter in the DBMS_RLS.ADD_POLICY procedure. Enclose the list in a pair of single quotation marks.

Example to show  an  how to specify the SELECT and INDEX statements for a policy.

Example for  Specifying  SQL  Statement Types with DBMS_RLS.ADD_POLICY

BEGIN

 DBMS_RLS.ADD_POLICY(

  object_schema   => 'hr',

  object_name     => 'employees',

  policy_name     => 'secure_update',

  policy_function => 'check_updates',

  statement_types => 'SELECT,INDEX');

END;

/

3. D. Controlling the Display of Column Data with Policies

You can create policies that enforce row-level security when a security-relevant column is referenced in a query.

1.       Adding Policies for Column-Level Oracle Virtual Private Database.

2.       Displaying Only the Column Rows Relevant to the Query.

3.       Using Column Masking to Display Sensitive Columns as NULL Values.

3. D.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_cols 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.
Example: An 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.

Example : Creating a Column-Level Oracle Virtual Private Database Policy

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;

/

Then  configure the policy with the DBMS_RLS.ADD_POLICY procedure as follows:

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;

/

3.D.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 above.

 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 above example. 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.

3. D. 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.
      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 for :Adding a Column Masking to an Oracle Virtual Private Database Policy

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;

/

Assume that a sales department user with SELECT privilege on the emp table (such as 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 all rows specified in the query, but with certain values masked because of the Oracle Virtual Private Database policy:

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.

The column-masking returned all rows requested by the sales user query, but made the sal and comm columns NULL for employees outside the sales department.


The following considerations apply to column-masking:

v  Column-masking applies only to SELECT statements.

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

v  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.

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

v  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.

4.How Oracle Virtual Private Database Works with Other Oracle Features

This section contains:
1.       Using SELECT FOR UPDATE in User Queries on VPD-Protected Tables
2.       How Oracle Virtual Private Database Policies Affect Outer or ANSI Join Operations
3.       How Oracle Virtual Private Database Security Policies Work with Applications
4.       Using Automatic Reparsing for Fine-Grained Access Control Policy Functions
5.       Using Oracle Virtual Private Database Policies and Flashback Query
6.       Using Oracle Virtual Private Database and Oracle Label Security
7.       User Models and Oracle Virtual Private DatabasE

4.1.Using SELECT FOR UPDATE in User Queries on VPD-Protected Tables

            As a general rule, users should not include the FOR UPDATE clause when querying Virtual Private Database-protected tables. The Virtual Private Database technology depends on rewriting the user's query against an inline view that includes the VPD predicate generated by the VPD policy function. Because of this, the same limitations on views also apply to VPD-protected tables. If a user's query against a VPD-protected table includes the FOR UPDATE clause in a SELECT statement, in most cases, the query may not work. However, the user's query may work in some situations if the inline view generated by VPD is sufficiently simple.


The FOR UPDATE clause has the following restrictions:

·         You cannot specify this clause with the following other constructs: the DISTINCT operator, CURSOR expression, set operators, group_by_clause, or aggregate functions.

·         The tables locked by this clause must all be located on the same database and on the same database as any LONG columns and sequences referenced in the same statement.

4.2How Oracle Virtual Private Database Policies Affect Outer or ANSI Join Operations

           Oracle Virtual Private Database rewrites SQL by using dynamic views. For SQL that contains outer join or ANSI operations, some views may not merge and some indexes may not be used. This problem is a known optimization limitation. To remedy this problem, rewrite the SQL to not use outer joins or ANSI operations.

4.3.How Oracle Virtual Private Database Security Policies Work with Applications

        An Oracle Virtual Private Database security policy is applied within the database itself, rather than within an application. Hence, a user trying to access data by using a different application cannot bypass the Oracle Virtual Private Database security policy. Another advantage of creating the security policy in the database is that you maintain it in one central place, rather than maintaining individual security policies in multiple applications. Oracle Virtual Private Database provides stronger security than application-based security, at a lower cost of ownership.
You may want to enforce different security policies depending on the application that is accessing data. Consider a situation in which two applications, Order Entry and Inventory, both access the orders table. You may want to have the Inventory application use a policy that limits access based on type of product. At the same time, you may want to have the Order Entry application use a policy that limits access based on customer number.
In this case, you must partition the use of fine-grained access by application. Otherwise, both policies would be automatically concatenated together, which may not be the result that you want. You can specify two or more policy groups, and a driving application context that determines which policy group is in effect for a given transaction. You can also designate default policies that always apply to data access. In a hosted application, for example, data access should be limited by subscriber ID.

4.4.Using Automatic Reparsing for Fine-Grained Access Control Policy Functions

            The  queries against objects enabled with fine-grained access control run the policy function to ensure that the most current predicate is used for each policy. For example, in the case of a time-based policy function, in which queries are only allowed between 8:00 a.m. and 5:00 p.m., a cursor execution parsed at noon runs the policy function at that time, ensuring that the policy is consulted again for the query. Even if the curser was parsed at 9 a.m., when it runs later on (for example, at noon), then the Virtual Private Database policy function runs again to ensure that the execution of the cursor is still permitted at the current time (noon). This ensures that the security check it must perform is the most recent.
Automatic re-execution of the Virtual Private Database policy function does not occur when you set the DBMS_RLS.ADD_POLICY setting STATIC_POLICY to TRUE while adding the policy. This setting causes the policy function to return the same predicate.

4.5.Using Oracle Virtual Private Database Policies and Flashback Query

          By default  operations on the database use the most recently committed data available. The flashback query feature enables you to query the database at some point in the past. To write an application that uses flashback query, you can use the AS OF clause in SQL queries to specify either a time or a system change number (SCN), and then query against the committed data from the specified time. You can also use the DBMS_FLASHBACK PL/SQL package, which requires more code, but enables you to perform multiple operations, all of which refer to the same point in time.
However, if you use flashback query against a database object that is protected with Oracle Virtual Private Database policies, then the current policies are applied to the old data. Applying the current Oracle Virtual Private Database policies to flashback query data is more secure because it reflects the most current business policy.

4.6.Using Oracle Virtual Private Database and Oracle Label Security

Oracle label security:

          

              Oracle Label Security is a security option for the Oracle Enterprise Edition database and was introduced with Oracle 8.1.7. Oracle Label Security mediates access to data rows by comparing labels attached to data rows in application tables (sensitivity labels) and a set of user labels (clearance labels).

               Oracle Label Security is an out-of-the-box solution for row level security, built on VPD technology. No coding or software development is required, allowing the administrator to focus completely on the policy. Oracle Label Security provides an interface for creating policies, specifying enforcement options, defining data sensitivity labels, establishing user label authorizations, and protecting individual tables or schemas. Data sensitivity labels provide a powerful and flexible method of restricting access to data. For example, data belonging to different organizations or companies can be separated using data sensitivity labels and selectively shared between companies by changing the data sensitivity label.
Depending on the complexity of the security policy, Oracle Virtual Private Database (VPD) may be the preferred method for implementing your security policy. In addition, Oracle Label Security is best suited for situations where access control decisions need to be based on the sensitivity of the information.

User Models and Oracle Virtual Private Database

You can use Oracle Virtual Private Database in the following types of user models:

·         Application users who are also database users. Oracle Database enables applications to enforce fine-grained access control for each user, regardless of whether that user is a database user or an application user unknown to the database. When application users are also database users, Oracle Virtual Private Database enforcement works as follows: users connect to the database, and then the application sets up application contexts for each session. (You can use the default USERENV application context namespace, which provides many parameters for retrieve different types of user session data.) As each session is initiated under a different user name, it can enforce different fine-grained access control conditions for each user.

·         Proxy authentication using OCI or JDBC/OCI. Proxy authentication permits different fine-grained access control for each user, because each session (OCI or JDBC/OCI) is a distinct database session with its own application context.

·         Proxy authentication integrated with Enterprise User Security. If you have integrated proxy authentication by using Enterprise User Security, you can retrieve user roles and other attributes from Oracle Internet Directory to enforce Oracle Virtual Private Database policies. (In addition, globally initialized application context can also be retrieved from the directory.)

·         Users connecting as One Big Application User. Applications connecting to the database as a single user on behalf of all users can have fine-grained access control for each user. The user for that single session is often called One Big Application User. Within the context of that session, however, an application developer can create a global application context attribute to represent the individual application user (for example, REALUSER). Although all database sessions and audit records are created for One Big Application User, the attributes for each session can vary, depending on who the end user is. This model works best for applications with a limited number of users and no reuse of sessions. The scope of roles and database auditing is diminished because each session is created as the same database user.

·         Web-based applications. Web-based applications typically have hundreds of users. Even when there are persistent connections to the database, supporting data retrieval for many user requests, these connections are not specific to particular Web-based users. Instead, Web-based applications typically set up and reuse connections, to provide scalability, rather than having different sessions for each user. For example, when Web users Jane and Ajit connect to a middle tier application, it may establish a single database session that it uses on behalf of both users. Typically, neither Jane nor Ajit is known to the database. The application is responsible for switching the user name on the connection, so that, at any given time, it is either Jane or Ajit using the session.

Oracle Virtual Private Database helps with connection pooling by allowing multiple connections to access more than one global application context. This ability makes it unnecessary to establish a separate application context for each distinct user session.

         
Table :summarizes how Oracle Virtual Private Database applies to user models.

Oracle Virtual Private Database in Different User Models:

User Model Scenario	Individual Database Connection	Separate Application Context per User	Single Database Connection	Application Must Switch User Name
Application users are also database users	Yes	Yes	No	No
Proxy authentication using OCI or JDBC/OCI	Yes	Yes	No	No
Proxy authentication integrated with Enterprise User Security	No	No	Yes	Yes
One Big Application User	No	No	No	Yes
Web-based applications	No	No	Yes	Yes

5.Finding Information About Oracle Virtual Private Database Policies

Data Dictionary Views That Display Information about Virtual Private Database Policies

View	Description
ALL_POLICIES	Describes all Oracle Virtual Private Database security policies for objects accessible to the current user.
ALL_POLICY_CONTEXTS	Describes the driving contexts defined for the synonyms, tables, and views accessible to the current user. A driving context is an application context used in an Oracle Virtual Private Database policy.
ALL_POLICY_GROUPS	Describes the Oracle Virtual Private Database policy groups defined for the synonyms, tables, and views accessible to the current user
ALL_SEC_RELEVANT_COLS	Describes the security relevant columns of the security policies for the tables and views accessible to the current user
DBA_POLICIES	Describes all Oracle Virtual Private Database security policies in the database.
DBA_POLICY_GROUPS	Describes all policy groups in the database.
DBA_POLICY_CONTEXTS	Describes all driving contexts in the database. Its columns are the same as those in ALL_POLICY_CONTEXTS.
DBA_SEC_RELEVANT_COLS	Describes the security relevant columns of all security policies in the database
USER_POLICIES	Describes all Oracle Virtual Private Database security policies associated with objects owned by the current user. This view does not display the OBJECT_OWNER column.
USER_POLICY_CONTEXTS	Describes the driving contexts defined for the synonyms, tables, and views owned by the current user. Its columns (except for OBJECT_OWNER) are the same as those in ALL_POLICY_CONTEXTS.
USER_SEC_RELEVANT_COLS	Describes the security relevant columns of the security policies for the tables and views owned by the current user. Its columns (except for OBJECT_OWNER) are the same as those in ALL_SEC_RELEVANT_COLS.
USER_POLICY_GROUPS	Describes the policy groups defined for the synonyms, tables, and views owned by the current user. This view does not display the OBJECT_OWNER column.
V$VPD_POLICY	Displays all the fine-grained security policies and predicates associated with the cursors currently in the library cache. This view is useful for finding the policies that were applied to a SQL statement.