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.