Oracle Virtual Private Database policy (VPD) tips

• "Oracle design security from the ground up"
• "Control access with Oracle grant security"
• "Achieve better control with Oracle's grant execute security model"

Using VPD policy security

Virtual private databases have several other names within the Oracle documentation, including row-level security (RLS) and fine-grained access control (FGAC). Regardless of the name, VPD security provides a whole new way to control access to Oracle data. Most interesting is the dynamic nature of a VPD. At runtime, Oracle performs these near magical feats by dynamically modifying the SQL statement of the end user:
 

1. Oracle gathers application context information at user logon time and then calls the policy function, which returns a predicate. A predicate is a where clause that qualifies a particular set of rows within the table.
    
2. Oracle dynamically rewrites the query by appending the predicate to users' SQL statements.

Whenever a query is run against the target tables, Oracle invokes the policy and produces a transient view with a where clause predicate pasted onto the end of the query, like so:

SELECT * FROM book WHERE P1

A VPD security model uses the Oracle dbms_rls package (RLS stands for row-level security) to implement the security policies and application contexts. This requires a policy that is defined to control access to tables and rows:.

VPDs are involved in the creation of a security policy, and when users access a table (or view) that has a security policy. The security policy modifies the user's SQL, adding a where clause to restrict access to specific rows within the target tables. Let's take a close look at how this works.

VPD security Application context

For the VPD to properly use the security policy to add the where clause to the end user's SQL, Oracle must know details about the authority of the user. This is done at sign-on time using Oracle's dbms_session package. At sign-on, a database logon trigger executes, setting the application context for the user by calling dbms_session.set_context. The set_context procedure can be used to set any number of variables about the end user, including the application name, the user's name, and specific row restriction information. Once this data is collected, the security policy will use this information to build the run-time where clause to append to the end user's SQL statement. The set_context procedure sets several parameters that are used by the VPD, and accepts three arguments:

dbms_session.set_context(namespace, attribute, value)

For example, let's assume that we have a publication table and we want to restrict access based on the type of end user. Managers will be able to view all books for their publishing company, while authors may only view their own books. Let's assume that user JSMITH is a manager and user MAULT is an author. At login time, the Oracle database logon trigger would generate the appropriate values and execute the statements shown in Listing A:

dbms_session.set_context('publishing_application', 'role_name', 'manager');

dbms_session.set_context('publishing_application', 'user_name', 'jsmith');

dbms_session.set_context('publishing_application', 'company', 'rampant_techpress');

dbms_session.set_context('publishing_application', 'role_name', 'author');

dbms_session.set_context('publishing_application', 'user_name', 'mault');

dbms_session.set_context('publishing_application', 'company', 'rampant_techpress');

Once executed, we can view these values with the Oracle session_context view. This data will be used by the VPD at runtime to generate the where clause. Note that each user has his or her own specific session_context values, shown in Listing B:

Now let's see how this application context information is used by the VPD security policy. In Listing C, we create a security policy function called book_access_policy that builds two types of where clauses, depending on the information in the session_context for each end user. Note that Oracle uses the sys_context function to gather the values.

Look at the code in Listing C carefully. If the user was defined as a manager, their where clause (d_predicate) would be:

where upper(company) = 'RAMPANT_TECHPRESS';

For the author, they get a different where clause:

where upper(author_name) = 'MAULT';

VPDs in action

We are now ready to show our VPD in action. In Listing D, we see very different results from an identical SQL query, depending on the application context of the specific end user.
 

It should be obvious that VPD is a totally different way of managing Oracle access than grant-based security mechanisms. There are many benefits to VPDs:

• Dynamic security?No need to maintain complex roles and grants.
   
• Multiple security?You can place more than one policy on each object, as well as stack them on other base policies. This makes VPD perfect for Web applications that are deployed for many companies.
   
• No back doors?Users no longer bypass security policies embedded in applications, because the security policy is attached to the data.
   
• Complex access rules may be defined?With VPD, you can use data values to specify complex access rules that would be difficult to create with grant security. You can easily restrict access to rows.

Of course, there are also some drawbacks to VPD security:

• Difficult column level security?Because access is controlled by adding a where clause, column-level access can only be maintained by defining multiple views for each class of end user.
   
• Requires Oracle IDs for every user?Unlike security that is managed externally, VPD requires that an Oracle user ID be defined for every person who connects to the database. This adds maintenance and overhead.
   
• Hard to audit?It is hard to write an audit script that defines the exact access for each specified user. This problem becomes even more acute for shops that mix security methods.

Problems with mixing VPD and grant security

Now that we have established the areas of security and auditing, it should be clear that we must come up with a method to ensure that security methods are not mixed in an inappropriate way. By themselves, each of these security mechanisms provides adequate access protection, but when these methods are mixed, it can often be difficult (if not impossible) to identify the access for individual users. You'll have to decide whether the security benefits of VPD are worth the extra administrative method.

------------------------------------------------------------------------------------------------------------------------------

The Virtual Private Database (VPD) concept was first introduced in the Oracle version 8i and is one of the most common security features in the Oracle database evolution, serving as an interface between the PL/SQL functions and the database tables. The PL/SQL functions compute a WHERE condition predicate that is appended to the SQL statements which are executed on a database transparently, restricting the access to the rows and the columns within the queried table.

This feature is also called as Fine-Grained Access Control (FGAC) and has a remarkable difference between the data redaction process, which is discussed in the earlier section. While the data redaction concentrates only on the column level data security, the VPD can secure both row level and column level data.

The VPD creates a virtual private look of the database table to the users who are restricted to access only the portion of the data from the table that are authorized for them. Since a dynamic WHERE condition is appended to the SQL statements which are secured under the VPD implementation, the structure of the original SQL is modified by a change in its access path, which seems to appear in the explain plan. Thus, utmost care has to be taken while designing the predicate clause in a such a way that it does not take a big toll on the performance of the SQL statement being executed.

Advantages of VPD over FGA

Implementing VPD security policy to our database objects has the below advantages rather creating access controls all over our application.

1.      Simplicity: We can implement the VPD policy to a table, view, or synonym only once, rather repeating the implementation to all the database objects in our application.

2.      Security: By implementing the VPD policy, the security is placed on the objects at the database level and not at the object level. Thus, accidental modification of the data using ad-hoc tools is prohibited when the VPD is in place.

3.      Flexibility: The security policies can be created on a database column of a table or a view and can be applied to SELECT, INSERT, UPDATE, DELETE, and INDEX statements based on it. We can also have individual policies placed upon the above types of statements.

Virtual Private Database Policy Components

To implement the VPD security in the Oracle database, we must first create a function to create a dynamic predicate clause and then a policy to combine this function and the objects we need to secure.

Function Definition and Implementation Rules

The first step in enforcing the security over the database object is to create a function (Procedures are not supported) defining the WHERE condition predicate that we want to implement.

The function created must satisfy the below behavior,

·         It must have two input parameters. One for the schema name and the other one for the object name with VARCHAR2 datatypes in their respective order mentioned. Even though these parameters are not needed by the function, those parameter values are supplied by the DBMS_RLS package during the policy creation.

·         It must have a VARCHAR2 return type, that returns the restriction condition to be placed in the WHERE predicate of a query.

·         The WHERE clause predicate can either be simplistic (with a simple arithmetic operated predicate) or be complex (with a predicate containing an application context), but it should be a valid syntax.

·         The function body cannot select or transform the table on which the policy is about to be created.

VPD Policy Creation

After creating the function that defines the WHERE clause predicate, we must associate this function with the database object by creating a VPD policy.

We can create the policy using the procedures offered by the DBMS_RLS package (RLS stands for Row Level Security). During the policy creation, we can also configure the fine-grained access control, that is, the types of SQL statements and the columns the policy protects. The policy starts functioning once we try to access data from this database object.

Oracle VPD Policy Types

There are totally 5 policy types provided by the Oracle Virtual Private Database to satisfy our needs such as for using them in the hosting environments.

Fine-grained Access Control Implementation Rules

We can create Oracle VPD policies for SELECT, INSERT, UPDATE, DELETE, and INDEX statements. To specify an SQL statement type for the policy, we must use the statement_types parameter in the ADD_POLICY procedure. If we want to specify more than one type of SQL statement, we must separate them with a comma enclosed with a single quotation. When we omit this parameter, by default Oracle specifies all the SQL statement types except INDEX.

We must consider the below rules before setting up the statement_types parameter.

·         When our application uses the MERGE statement, we must make sure that we set the statement_types parameter to INSERT, UPDATE, and DELETE or omit this parameter to set these statement types by default.

·         When the users have their statement_types parameter set to INDEX, they have the privilege to see the data from all rows, even if the other statement types are not set.

A Simple Oracle VPD Policy Creation

Before creating an Oracle VPD policy, let us examine the plan for the EMPLOYEES table.

The EXPLAIN PLAN FOR statement parses and records the formatted plan in the plan table that has been generated by Oracle for executing the query.

The display function allows us to display the execution plan that is stored recently in the plan table.

In the above plan, we can see that all 107 rows of the EMPLOYEES table are returned from the query execution with a full table access.

The first step in creating an Oracle VPD is to create the policy function in a common user (In our case, say admin_user). Consider that all the employees have a database user created in their name (First_name||'_'||Last_name) for their purpose. For e.g., Alexander_Hunold is a username for a schema used by the employee- Alexander Hunold. Thus, the below function is created returning the department ID of the database user who calls it as its predicate statement. If the user accessing the EMPLOYEES table is not a valid employee, by default the predicate is returned as DEPARTMENT_ID is null, thus no employee details are displayed to them.

Note that even though the function does not need the schema name and the table name in its body functionality, we must create it with two input parameters of VARCHAR2 type as the VPD policy call expects the function like so.

The Oracle VPD policy can be created using the ADD_POLICY procedure from the DBMS_RLS package. It requires the below parameters to be set for its creation,

·         OBJECT_SCHEMA parameter is for setting the schema of the object on which the policy is to be applied. In this case, a common user who administers the VPD object, ADMIN_USER is set for this parameter.

·         OBJECT_NAME parameter is for setting the object name on which the policy is to be applied. In this case, the table EMPLOYEES is set for this parameter.

·         POLICY_NAME parameter is for setting the user defined unique policy name for its creation and unique identification. In this case, the user-defined name POLICY_EMP is set for this parameter.

·         FUNCTION_SCHEMA parameter is for setting the schema name of the policy function created in the above section. In this case, a common user who administers the policy function, ADMIN_USER is set for this parameter.

·         POLICY_FUNCTION parameter is for setting the policy function created in the above section. In this case, the policy function FUNC_EMP_VPD is set for this parameter.

·         STATEMENT_TYPES parameter is for setting the fine-grained access control statement types. In this case, SELECT, INSERT, UPDATE, and DELETE statement types are set for this parameter. These statement types will be taken into consideration by default if we omit this parameter during the policy creation.

After creating the policy, the VPD goes into effect immediately. When we try to query the EMPLOYEES table, we get the results pertaining to the department that the querying database user is associated with.

For example,

1.      When we try to select the table data from the database user steven_king, we can see that the employees table returning all the employees corresponding to the employee King, Steven’s department ID (i.e., 90).

2.      When we try to select the table data from the database user nancy_greenberg, we can see that the employees table returning all the employees corresponding to the employee Nancy_Greenberg’s department ID (i.e., 100).

The below statement for retrieving the explain plan for the EMPLOYEES table is executed in the database user david_austin, whose department ID is 60.

The display function is then executed for displaying the stored plan details as shown below,

In the above plan, the policy function’s predicate information is available as a result of a VPD process to filter the data returned. In addition, Oracle optimizer also uses an index range scan on the predicate clause containing the index IDX_DEPTNO for increasing the performance of the query’s execution.

Column Level Oracle Virtual Private Database Policy

We can create a column-level Oracle VPD policy by setting up the SEC_RELEVANT_COLS parameter of the ADD_POLICY procedure in the DBMS_RLS package. Here, the security is applied only when the security relevant columns are accessed.

After creating the policy function for the Oracle VPD process (We can use the above-created policy function in this case), we must create the below policy with the parameter SEC_RELEVANT_COLS set to the columns EMAIL, PHONE_NUMBER, and SALARY from the EMPLOYEES table in a comma separated manner enclosed within single quotes.

When we query the EMPLOYEE table from the user Shelley_Higgins who is an employee in the department ID 110 as below,

The only rows that are displayed are the ones which the user has the privileges to access all columns in a row. Thus, we will be able to find all the employees belonging to the department ID 110 as shown below.

But when we query the EMPLOYEE table omitting the security relevant columns from the selection list in any user as below,

We will be able to find all the rows in all the departments from the EMPLOYEE table without any security policy applied to them as below,

…                       …                    …                        …                                                           

…                       …                    …                        …

After creating the necessary application contexts, we must create the policy function with a dynamic predicate clause and the VPD policy for merging this function and the objects we intend to secure.

Creating the Policy Function

A policy function with two input VARCHAR2 parameters defining the secure object’s WHERE predicate is created below. Here, the database username is passed as the attribute parameter value to the SYS_CONTEXT function and its resulting department ID is returned as the WHERE clause predicate. When the SYS_CONTEXT function returns a null value, the Department_id is null predicate is returned by default. Thus, this policy function returns different department IDs for different users for which they have access to.

Creating the VPD Policy

After creating the policy function, VPD policy can be created using the ADD_POLICY procedure from the DBMS_RLS package as shown below.

Once the policy is created, when we try to access the EMPLOYEES table from the database user USER1 we tend to find all the employees belonging to the department ID- 10.

Result:

1

Thus, when we try to access the EMPLOYEES table from the database user USER2, USER3, USER4, we tend to find all the employees belonging to the department IDs- 20, 30, 40 respectively.

Oracle VPD Data Dictionary Views

VPD Gotchas!

·         We should not use the FOR-UPDATE clause when querying VPD protected tables as the query might sometimes fail if they are too complex in nature.

·         We must rewrite our outer joins and ANSI operations in our SQLs as some views may not merge and some indexes may not be used. This problem is a known optimization limitation.

·         When we use flashback queries on a VPD protected object, then the current policies are applied to the old data, which may not result what we have expected.

·         We cannot apply VPD policies to the objects owned by the SYS user.

·         When we are exporting data using a direct path export operation, Oracle VPD policies are not enforced as the Oracle database reads data directly from the disk and not from the database where the policies are in effect.