Oracle Tips by Burleson

New Types of VPD Policies

In Oracle9i, there is only one dynamic type of VPD policy.

Oracle 10g introduces the following new types of VPD policies:

Table 15.1 VPD Policy Types

Static Policy Type

When you use static policies, VPD always enforces the same predicate for access control, regardless of the runtime environment.  This means that no matter which user access the objects, everyone gets the same predicate.  Static policy functions are executed once and then cached in SGA memory.  Statements accessing the same object do not re-execute the policy function.  This makes the static policies very fast for each query execution.

There are two options in the Static type policy:

• static - If you set the policy_type parameter in the dbms_rls.add_policy procedure to static, the policy is applied to a single object.
   

• shared_static - However, if you set the policy_type parameter to shared_static, the policy is applied to multiple objects.

Non-Static Policy Type

When you use non-static (context sensitive) policies, the VPD security policy function is re-executed whenever the session context changes.

There are three options in the Non-Static policy types:

• context_sensitive - If the policy_type parameter is set to context_sensitive, the VPD re-evaluates the policy function at statement execution time, if it detects context changes since the last use of the cursor.  However, when connect pooling is used where multiple clients share a database session, the middle tier must reset context during client switches.
   

• shared_context_sensitive - The shared_context_sensitive option is the same as context_sensitive, except the security policy is applied to multiple objects.
   

• dynamic - The dynamic option executes the policy function every time a statement accesses the security-relevant columns of the object.  The VPD assumes any system or session environment change at any time may affect the predicate.  Therefore, it always re-executes the policy function on each statement parsing or execution.  This is the default option in Oracle 10g and the only policy type in the Oracle9i database.

Shared Policies Benefits

In Oracle 10g, you can apply both the static and non-static VPD policies to multiple objects.  By applying a single policy to multiple objects, you can ease the administration overhead by reducing redundant policies.

Get the complete Oracle10g story:

The above text is an excerpt from "Oracle Database 10g New Features: Oracle10g Reference for Advanced Tuning and Administration", by Rampant TechPress.  Written by top Oracle experts, this book has a complete online code deport with ready to use scripts. 

To get the code instantly, click here:

http://rampant-books.com/book_2003_2_oracle10g.htm

Need an Oracle Mentor?

BEI is now offering personal mentors for Oracle DBAs where you can have an Oracle expert right at your fingertips, anytime day or night. We work with hundreds of Oracle databases every year, so we know exactly how to quickly assist you with any Oracle question.

Why risk an unplanned outage? You can now get telephone access to Don Burleson or any of his Oracle Certified DBAs with more than 20 years of full-time IT experience. Click here for details:

http://www.dba-oracle.com/service_oracle_backup.htm