To continue the scenario from the previous article in this series, imagine that your CIO calls you into yet another clandestine meeting with the Director of Accounting at your company a few months after the first confidential gathering. “Good news!” says the Director. “The audit trails that you put in place with that FGA thing you talked about helped us catch our thief. We caught her red-handed when she created a bunch of fake credit memos for a real vendor. She admitted she’d updated the vendor’s credit card number to match her own card number and then issued several credit memos totaling just under $100,000.00. Your audit reports were absolutely essential evidence to our case. Well done!” Your CIO beams at you as well.

But now the Director’s brow once again furrows. “And that got me to thinking … how do we prevent this in the future? Even though we’re tightening our background check procedures, there’s nothing to stop anyone devious enough from doing this again, is there?” Your CIO chimes in, “Well, not without rewriting a lot of our custom application software for your team. Unless …” They both turn and look at you for another miracle. Yet again, Oracle 10g’s robust security features come to your rescue. You explain to the Director and CIO your plans to tighten database security without having to change any application code … and once again, there are smiles on their faces. “Outstanding! “ says your CIO. “I think we’ll keep you around.”

Virtual Private Database: An Overview

In the previous article, I demonstrated how to determine which users are accessing which data elements in an Oracle 10gR2 database with Fine-Grained Auditing (FGA), an extremely powerful set of features for tracking questionable or fraudulent transactions. While FGA can certainly assist me in tracking down security violations after they have happened, as the old proverb says, the horse has already left the barn, and I’m just closing the gate behind it. What I’d really like to do is prevent an application or user session from accessing and modifying sensitive data.

Some shops I’ve worked in have implemented complex security rules using specially constructed views to limit access to their database’s critical tables. While this is certainly a noble and elegant solution, it is also extremely time-consuming to plan, develop, test, and implement. Also, view-based security does have a significant disadvantage: If a user is sophisticated and curious (or malicious!) enough, somehow he will find a way to display the view and the restrictions it places upon the underlying base table(s). Ideally, I’d like to make sure that a user isn’t even aware that he’s been prohibited from accessing the data in the first place, as that may help quell any curiosity about where the data is stored in the database system

The introduction of the DBMS_RLS package in Oracle 9i offered an excellent alternative to the custom-written view implementation of security. As its name implies, DBMS_RLS allows a DBA to enforce row level security against specific tables in the database. Whenever a row is read, added, modified or deleted, Oracle applies fine grained access control (FGAC) rules that insure the row’s values met the strictures of that predefined security policy.

The security policy enforces these restrictions by adding a hidden predicate to each query or DML statement that attempts to access the data. For example, if a query attempts to access a row, and the security policy determined that the user had insufficient permission to access it, then Oracle filtered the row from the query’s result set. On the other hand, if a DML operation attempted to process the row, and the security policy showed that the user was limited from accessing the row, Oracle blocked the operation against the row. (I’ll explain more about how these predicates are constructed and applied to data when I demonstrate how to build application security context functions later in this article.)

However, the implementation of DBMS_RLS in releases prior to Oracle 10gR2 still had some major drawbacks:

Limited Scope Control. Fine grained access control rules could only be applied to one table at a time. If I needed to secure multiple tables, I had to create a separate security policy for each table. Also, the security policy lacked column-level specificity. For example, if I was only concerned with applying security rules against the CREDIT_LIMIT column of the AP.VENDORS table, I was forced to apply the security policy to the entire table regardless.

Finally, fine grained access control rules could not be applied directly against a view to secure it. For example, if I needed to restrict access to the AP.RV_INVOICE_DETAILS view, I would be forced to create a separate security policy for the AP.VENDORS and AP.INVOICES tables that underlie that view.

Repetitive Execution. In prior releases, Oracle was forced to evaluate the hidden predicate every time a user accessed the secured table. This meant that the predicate tended to be executed extremely frequently – even when the predicate didn’t change. For example, if I needed to restrict a user session from querying any entries in the AP.VENDORS table whose value for CREDIT_LIMIT exceeded $5000, and that credit limit restriction never changed for the duration of that user session, Oracle still parsed the predicate each time the query was executed. If that query was part of an OLTP application and tended to be evaluated hundreds of times per minute, a lot of parse time was essentially wasted.

Limited Predicate Size. Depending upon how complex the security restrictions needed to be applied to a query or DML operation, I often heard complaints from users because their session received an ORA-03113 error when Oracle attempted (and failed!) to parse a correspondingly complex predicate. Indeed, I’ve seen predicates that easily exceeded the maximum size of 4K.

Upgraded FGAC Security Features in Oracle 10gR2

The good news is that Oracle 10gR2 overcomes all of these limitations:

• A single security policy can now enforce restrictions on multiple tables. In addition, data that’s accessed via indexes and synonyms can be secured as well.
• Similar to this release’s enhancements for DBMS_FGA, DBMS_RLS can now define FGAC business rules that are evaluated only when one or more specific columns are accessed.
• Also, a security policy can be applied directly to a view without having to define policies to secure the underlying table(s) for that view.
• The maximum size of a security policy’s predicate has been expanded eightfold to 32K.

The most impressive set of enhancements, however, encompass the ability to tell Oracle how often a predicate should be evaluated. In the original release of DBMS_RLS, the predicate had to be evaluated every time the policy was enforced. This original mode is retained as the DYNAMIC policy type in Oracle 10gR2. Two additional security policy types are also available to control how often a predicate needs to be parsed: CONTEXT_SENSITIVE and STATIC.

Context-Sensitive Policies. The underlying security policy function for a CONTEXT_SENSITIVE security policy will only be executed under two conditions: when either the SQL statement is first parsed, or when the SQL statement is being executed and Oracle 10gR2 detects that the local application context has changed since the last time it was executed. This tends to eliminate an enormous amount of unnecessary statement parsing, especially when the predicate will not change dramatically between invocations of the statement.

Static Policies. Unlike a CONTEXT_SENSITIVE policy, the predicate of a STATIC policy is only evaluated when a user session is initiated, and it will never be re-evaluated as long as the session persists because the predicate is simply cached in the SGA. This type of policy is useful for predicates that will simply not change for the duration of the session. For example, if I need to restrict access to all deactivated entries in the AP.VENDORS table, the predicate that handles that limitation would be ACTIVE_IND <> ‘N’. This predicate would never need to change within the session; therefore, Oracle 10gR2 will parse this predicate only once, store it in the library cache, and never re-evaluate it. This obviously saves even more significant parsing time than does a CONTEXT_SENSITIVE security policy.

Shared Policies. In addition, Oracle 10gR2 offers a shared version of each of these policy types when it would be advantageous to apply the same security privileges across multiple database objects. These two policy types are SHARED_CONTEXT_SENSITIVE and SHARED_STATIC, and they work identically as their non-shared security policy counterparts.

Oracle recommends testing a VPD policy in DYNAMIC mode before attempting to activate the policy in either CONTEXT_SENSITIVE or STATIC mode. I heartily concur with this suggestion! In my experience, it helped me to alleviate a lot of frustration while still learning how to best use the different VPD policy types effectively.

Putting It All Together: A Demonstration

Now that I’ve hopefully explained the philosophy and theory behind VPD, it’s time to demonstrate its effectiveness. I’ll use the same Accounts Payable schema objects I created in the prior article, and I’ll apply the following business rules to the corresponding data using VPD security policies for three different types of users:

Accounts Payable Clerk (APCLERK)

• An Accounts Payable Clerk may not view a Vendor’s credit card number if the Vendor has a credit limit of $25,000 or above.
• An Accounts Payable Clerk is not permitted to create a new Vendor with a credit limit of $25,000 or above.
• An Accounts Payable Clerk is not permitted to upgrade the credit limit of an existing Vendor to above $25,000.
• An Accounts Payable Clerk is not permitted to access any Invoice marked as a Credit Memo.
• An Accounts Payable Clerk cannot change any Invoice from its normal status into a Credit Memo.

Accounts Payable Team Lead (APTLEAD)

• An Accounts Payable Team Lead may not view a Vendor’s credit card number if the Vendor has a credit limit of $150,000 or above.
• An Accounts Payable Team Lead is not permitted to create a new Vendor with a credit limit of $150,000 or above.
• An Accounts Payable Team Lead is not permitted to upgrade the credit limit of an existing Vendor to above $150,000.
• An Accounts Payable Team Lead, however, is not restricted from accessing any Invoice marked as a Credit Memo, and the Lead can also change a normal Invoice into a Credit Memo.

Accounts Payable Director (APDIR)

• The Accounts Payable Director has complete rights to change any data in the AP system.

Finally, what if a user falls into none of these groups? To play it extra-safe, I’ll make sure that my application security context package will construct and apply the appropriate predicates to keep an unapproved user session from seeing any credit card or credit limit information about a Vendor.

First, I’ll create three new users, APCLERK, APTLEAD, and APDIR, and assign them the appropriate system privileges, including the APP_SECURED role that was established in the prior article, as shown in Listing 2.1.

Next, I’ll construct a special package, AP.APP_SECURITY_CONTEXT, that implements the business rules described previously. (See Listing 2.2 for the code for this package.) The package comprises one procedure, SETUSERINFO, and two functions, CREDIT_LIMIT and CREDIT_MEMO. The procedure will be used to assign the appropriate values for each different type of user accessing the data in the Accounts Payable system, and the two functions will build the actual predicates to enforce the Accounts Payable security policies.

Listing 2.3 shows how to create a CONTEXT object in Oracle 10gR2. This context, VPD_CONTEXT, will provide a container for values assigned whenever a user session is established, and the AP.APP_SECURITY_CONTEXT package is defined as the only way to modify data inside the context. VPD_CONTEXT will be populated via the AP.ON_LOGON trigger whenever a new user session is established via a call to the SETUSERINFO procedure.

The code in Listing 2.4 establishes four security policies for the Accounts Payable system:

• Policy AP_CREDIT_CARD ensures that Vendor credit card numbers cannot be viewed during a SELECT operation if the Vendor’s credit limit is greater than the one specified for the user session and either the CREDIT_CARD or CREDIT_LIMIT columns are included in the query. The AP.APP_SECURITY_CONTEXT package’s CREDIT_LIMIT function is called to enforce this business rule.
• Policy AP_CREDIT_LIMIT ensures that a Vendor entry cannot be viewed, created, or updated if the Vendor’s credit limit is greater than the one specified for the user session. This policy is also enforced by the CREDIT_LIMIT function.
• Policy AP_CREDIT_MEMO enforces a restriction on creation of a new Credit Memo, and it’s enforced by the CREDIT_MEMO function. This policy also ensures that an existing Invoice can’t be transformed into a Credit Memo (i.e. by changing the Invoice’s value for INVOICE_TYPE from ‘C’ to ‘D’).
• Finally, policy AP_RPTG_READONLY ensures that no user accessing the reporting view named AP.RV_INVOICE_DETAILS can see any Vendor, Invoice, or Invoice Detail information for a Vendor whose credit limit is greater than the one specified for the user session. Again, the CREDIT_LIMIT function is called to enforce this business rule. Note that since all other users besides AP system users are sent a value of zero for their credit limit, this policy effectively limits those users from seeing any data if either the CREDIT_CARD or CREDIT_LIMIT columns are included in the query.

All the pieces are now in place, so it’s time to test the policies. In Listing 2.5 I’ve listed several queries that should prove if these security policies are working properly for SELECT statements, and in Listing 2.6 I’ve set up similar tests for INSERTs and UPDATEs.

To prove out my security policies, I first logged in as the AP Director user (APDIR) and executed all DML statements in these two sets of unit tests. As expected, the queries retrieved all data, and the DML statements completed successfully. Since this user should have full access to all data in the AP schema, this test turned out as expected. Listing 2.7 shows the results from the queries for the sake of later comparisons.

Next, I logged in as the AP Team Lead user (APTLEAD) and ran the same set of queries. Since there are no Vendors with a credit limit above $150,000, the results for all queries matched those in Listing 2.7, which was as expected. However, when I attempted to add a new Vendor entry with a credit limit above $150,000, or when I attempted to update an existing Vendor entry so that its credit limit exceeded $150,000, the VPD policy was triggered, and as expected, both of the operations were rejected. As shown in Listing 2.8, Oracle 10g raised an exception and returned an ORA-28115,Policy With Check Option Violation error message.

So far, so good! Now for the final set of unit tests. When I ran the same queries against the AP data for the AP Clerk user (APCLERK), I noticed significant differences. As I expected, data was returned only for those Vendors whose credit limit didn’t exceed $25,000. Moreover, when I attempted to add a new Vendor entry with a credit limit above $25,000, the VPD policy was triggered. However, note that for an attempted update of a Vendor whose credit limit already exceeds $25,000, the VPD policy still fires, but the predicate that’s generated simply prevents the DML from executing against any rows. These results are shown in Listing 2.9.

Conclusion

Even though Oracle 9i introduced the concept of Virtual Private Database (VPD), it took several enhancements in Oracle 10gR2 to improve significantly its breadth and flexibility. VPD now offers extremely granular row-level security enforcement via fine grained auditing control (FGAC), limited parsing of commonly-utilized predicates per each user session, and the creation of much larger, more complex predicates for security enforcement.

References and Additional Reading

Even though I’ve hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I’ve drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article:

B14214-01 Oracle Database New Features Guide

B14231-01 Oracle Database Administrator’s Guide

B14258-01 PL/SQL Packages and Types Reference

B14266-01 Oracle Database Security Guide

Previous   Next

Back to DBAsupport.com