Oracle fine-grained access control has very powerful functions

Oracle fine-grained access control (VPD) is a virtual dedicated cantalua database. Its main function is to provide very powerful row-level security. Oracle fine-grained access control is launched in Oracle8i and has been widely used in various applications, including educational software and financial services.

VPD provides users with a local view of a 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 policy function, 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 ".

Repeated analysis required to generate a predicate is an overhead that can be trimmed in some cases. For example, in most cases, the 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 predicate structure, providing better Oracle fine-grained access control for improved 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 an Oracle fine-grained 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 10g, you can use the "context_sensitive" type policy as a parameter in dbms_rls.add_policy call to implement this method:

 
 

  
  
policy_type => dbms_rls.context_sensitive  
 
 

In another example, we have a table named ACCOUNTS, which has several columns, one of which is BALANCE, indicating the account BALANCE. Assume that a user is allowed to view accounts lower than a specific balance, and the balance is determined by the application context. We do not fix the balance value in the policy function, but 3 is determined based on the application context, such:

 
 

  
  
create or replace vpd_pol_func  
  
  
(  
  
  
p_schema in varchar2,  
  
  
p_table in varchar2  
  
  
)   
  
  
return varchar2  
  
  
is  
  
  
begin  
  
  
return 'balance < sys_context(''vpdctx'', ''maxbal'')';   
  
  
end;  
 
 

The attribute of the application context vpdctx maxbal can be set at the beginning of the session, and the function can easily obtain this value at runtime.

Pay attention to this example. The predicate has two parts: the partial part before the sign and the subsequent part. The previous section is the word "balance", which is a text character. The latter part is static to some extent, because the application context variable remains a constant before it changes.

If the context attribute of the application remains unchanged, the entire predicate is a constant, so you do not need to re-execute the function. If the policy type is defined as context-sensitive, the Oracle Database 10 Gb can identify this situation for optimization. If the session context does not change during the session, the function is not re-executed, which significantly improves the performance.

Static policy. Sometimes business operations can ensure that the predicates are more static. For example, in the context-sensitive policy type example, we define the maximum balance you see as a variable. This method is useful when the Oracle userid in a web application is shared by many web users and the application sets the context of the Variable Application Based on the permissions of these users.

Therefore, both the web user TAO and KARTHIK are connected to the database by the user APPUSER. The two can have two different application context values in their sessions. In this case, the MAXBAL value does not depend on the Oracle userid, but on the sessions of TAO and KARTHIK.

In the case of static policies, predicates are more predictable, as described below.

LORA and MICHELLE are account administrators of Acme Bearings and Goldtone Bearings respectively. When they connect to the database, they use their own id and should only see the rows that belong to them. In Lora, the predicate becomes where CUST_NAME = 'acme ';

For Michelle, where CUST_NAME = 'goldtone' is used '. Here, predicates depend on their userid, so any session they create always has the same value in the context of the application.

10g can be used to cache the predicate in SGA and reuse the predicate in the session without re-executing the policy function. Policy functions are similar to the following:

 
 

  
  
create or replace vpd_pol_func  
  
  
(  
  
  
p_schema in varchar2,  
  
  
p_table in varchar2  
  
  
)   
  
  
return varchar2  
  
  
is  
  
  
begin  
  
  
return 'cust_name = sys_context(''vpdctx'', ''cust_name'')';   
  
  
end;  
 
 

The policy is defined:

 
 

  
  
policy_type => dbms_rls.static 
 
 

This method ensures that the policy function is executed only once. Even if the application context changes in the session, the function is never re-executed, making the process very fast.

We recommend that you use static policies to host applications among several users. In this case, a single database has several user data. When a user logs on, the trigger can set the context value of the application used for policy functions after logon to quickly generate predicates.

However, defining a policy as static is also a double-edged sword. In the preceding example, we assume that the value of VPDCTX. CUST_NAME in the context attribute of the application does not change in the session. What if this assumption is incorrect? If this value changes, the policy function will not execute, so the new value will not be used in the predicate, and an error result will be returned! Therefore, be very careful when defining a policy as static; you must be absolutely sure that this value will not change. If you cannot make this assumption, it is best to define the policy as context-sensitive.

Share policy type. To reuse the code and use the code that has been analyzed to the maximum extent, you can decide to use common policy functions for several tables. For example, in the above example, we may have different tables-SAVINGS and CHECKING-for different types of accounts, but the rules are still the same: restrict users to view accounts whose balance exceeds their authorization range. In this case, a unified function is required for the policy on the CHECKING and SAVINGS tables. This policy is created as context_sensitive.

Assume that events occur in the following order:

1. Connection session
 

2. Set application context

3. select * from savings;

4. select * from checking;

Even if the application context does not change between Step 1 and step 2, the policy function is re-executed because the selected table is different. This is not what we want, because the policy functions are the same and you do not need to re-execute the Oracle fine-grained function.

The new feature of 10g is the ability to share policies between objects. In the preceding example, you can define the policy types of these policies:

 
 

  
  
policy_type => dbms_rls.shared_context_sensitive 
 
 

Declaring a policy as "shared" can not execute this function in the above cases, thus improving performance.

Article by: http://database.csdn.net/c_oracle/tag/2