Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Virtual Private Database policy (VPD) tips

Oracle Tips by Burleson Consulting
August 25, 2003

In previous installments of this series on Oracle security, we examined Oracle grant security and grant execute security, noting the advantages and shortcomings of each approach. Now let's look at another Oracle security alternative, the virtual private database (VPD).


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:

connect jsmith/manpass;


select
namespace, attribute, value
from
session_context;


NAMESPACE ATTRIBUTE VALUE
---------------- --------- ---------
PUBLISHING_APPLICATION ROLE_NAME MANAGER
PUBLISHING_APPLICATION USER_NAME JSMITH
PUBLISHING_APPLICATION COMPANY RAMPANT_TECHPRESS


connect mault/authpass;

select
namespace, attribute, value
from
session_context;


PUBLISHING_APPLICATION ROLE_NAME AUTHOR
PUBLISHING_APPLICATION USER_NAME MAULT
PUBLISHING_APPLICATION COMPANY   RAMPANT_TECHPRESS

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.

create or replace function
   book_access_policy
   (obj_schema varchar2, obj_name varchar2) return varchar2

 is                

 d_predicate varchar2(2000);

 begin              

 if sys_context('publishing_application','role_name')='manager' then
   d_predicate:=
      'upper(company)=sys_context(''publishing_application'',''company'')';

 else                  
   -- If the user_type session variable is set to anything else,
   -- display only this person's record                    --                  
   d_predicate:=
      'upper(author_name)=sys_context(''userenv'',''session_user'')';

 end if;       

 return d_predicate;       

 end;

 end; /


 DBMS_RLS.ADD_POLICY (
   'pubs',
   'book',
   'access_policy',
   'pubs',
   'book_access_policy',
   'select'
 );

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.
 

connect jsmith/manpass;


 select * from book;



 Book                   Author                                  
 Title                  name          Publisher                 
 --------------------   ------------- --------------------      
 Oracle9i RAC           mault         Rampant Techpress         
 Oracle job Interview   dburleson     Rampant Techpress         
 Oracle Utilities       dmmoore       Rampant Techpress         
 Oracle Troubleshooting rschumacher   Rampant Techpress         
 Oracle10i DBA Features mault         Rampant Techpress         

 connect mault/authpass;

 select * from book;

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.


Next in the series:
We've reviewed grant security, grant execute security, and VPD security. We're now ready to explore the auditing of Oracle security. Oracle offers several methods for auditing, including Oracle DDL and server error triggers, the Oracle audit SQL command, and Oracle's fine-grained auditing facility.
 
If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.