Comparing Oracle 10g column masking to relational views

by Bob Watkins | Apr 02, 2007 10:03:00 PM

Tags: Listing B, Oracle Application Server 10g, Bob Watkins, Virtual Private Database, relational view, predicate, salary, Oracle Corp., Oracle Tips Newsletter

2 comment(s)

Takeaway: Thanks to the suggestion of TechRepublic member BrooklynPennyPincher, this Oracle tip compares two approaches to hiding column-level data on a report: the Virtual Private Database and relational views.

In a previous tip, I described how a column's data could be masked (hidden) using Oracle 10g's Virtual Private Database (VPD) feature. TechRepublic member BrooklynPennyPincher suggested I write a comparison of VPD with relational views.

The mission of the older tip was to hide salary and commission_pct columns on a report if the department_id was equal to 60. The predicate is a static value, so a relational view could certainly have been used to do the same thing. Listing A shows a view that uses the selector CASE statement (which has been available in Oracle SQL since version 8i) to compare the department_id value to 60, return NULL if it is equal, or return the salary if it is any other value. A similar CASE statement could mask the commission_pct data. The data would be protected by granting access only to the view and not to the base table (Employees).

But what if I change the rules slightly and require that all salaries be masked except those in the user's own department?

Your first thought might be to simply create more views -- one for each department -- and grant each user access to the correct view. There are many reasons that this is not a good solution, which include the following:

Multiple views would add maintenance overhead (i.e.,� if one is changed, all the others would have to be changed as well). New departments would require new views. Users must be granted permissions, and those permissions must be changed when users change departments.
The queries implemented in the views would be using static values, not bind variables, so multiple copies of what is essentially the same query would be stored in the Shared Pool.
The application would have to be programmed to call different view names for different users, which is another source of complexity.

Listing B shows a small modification to the VPD policy function from the previous article. Instead of testing for department 60 directly, it uses the SYS_CONTEXT function to return the user's department. (Assume this value was set at logon for the user.) The function then returns a different predicate (the WHERE clause) for each user. The salary will only be shown in rows for which the predicate is true. If a department number is not set, the always false predicate of "1=2" will be returned, causing the salary to be masked for the whole report.

Even better is the fact that the call to SYS_CONTEXT acts as a bind variable in the query; only one copy of this query needs to be stored in the Shared Pool to handle all departments. A relational view could be built that uses the SYS_CONTEXT function in the same way, but a different view in the application would be able to see all the data. The VPD approach will filter all accesses to the Employees table regardless of query.

This is the key difference in the two techniques: Views are designed to provide filtering of data within an application, whereas VPD is designed to allow multiple groups of users to share the same tables transparently, each able to see only their own data regardless of application.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

2 comment(s)