Virtual Private database based on Oracle 10g features

Introduction: Oracle 10gThe availability is much higher than the previous version.Virtual Private DatabaseThe working method is to provide users with a local view of the table based on a series of defined standards by transparently changing requests for data. During running, all queries are appended with a predicate to filter the rows that the user can see. For example, if you are only allowed to view the account administrator SCOTT's account, VPD automatically queries:

Select * from accounts;

Rewrite:

Select * from accounts

Where am_name = "SCOTT ";

DBA sets a security policy on the table ACCOUNTS. This policy has a related function called policyn, which returns a where am_name = "SCOTT" string used as a predicate ". If you are not familiar with all the features of this feature, I suggest you read the article "using VPD to keep information confidential" in Oracle magazine ".

Policy type

Repeated analysis required to generate a predicate is an overhead that can be trimmed in some cases. For example, in most cases, predicates are not static as am_name = "SCOTT; it is more dynamic based on the user's identity, the user's permission level, and the account administrator to which the user reports. Strings created and returned by policy functions may be highly dynamic. To ensure the results, Oracle must re-execute policy functions each time, which wastes resources and reduces performance. In this type of policy, each execution of a predicate may be very different. This policy is called a "dynamic" policy, which has been provided in the Oracle9i database and earlier versions.

In addition to retaining dynamic policies, Oracle Database 10 Gb also introduces several new types of policies based on the construction of predicates, providing better control for improving performance: context_sensitive, shared_context_sensitive, shared_static and static. Now let's take a look at the meaning of each policy type and how to use them in an appropriate context.

Dynamic policy. To maintain backward compatibility, the Default policy type in 10g is "dynamic"-just as in Oracle9i. In this case, for each row and each user, the policy function is re-evaluated every time the table is accessed. Let's analyze the rule predicates in detail:

Where am_name = "SCOTT"

If the where clause is ignored, the predicate has two different parts: the part before the equality operator (am_name) and the part after the equality operator ("SCOTT "). In most cases, the latter part is more like a variable because it is provided by user data (if the user is SCOTT, the value is "SCOTT "). The part before the equal sign is static. Therefore, even if the function does not have to obtain the value of the policy function for each row to generate an appropriate predicate, the performance can be improved by understanding the static nature of the previous section and the dynamic nature of the subsequent sections. In 10 Gb, you can use the "context_sensitive" type policy as a parameter in dbms_rls.add_policy call to implement this method.

I am very happy to share with you. I hope the content mentioned above will be helpful to you.