Application of Oracle Virtual Private Data Control Method

Welcome to the Oracle community forum and interact with 2 million technical staff. As database technology becomes more and more widely used, the number of users using databases increases and the sensitivity of data content increases, database security has become more important. To prevent unauthorized viewing and modification of data in the database

Welcome to the Oracle community forum and interact with 2 million technical staff> as database technology becomes more and more widely used, the number of users using databases increases and the sensitivity of data content increases, database security has become more important. To prevent unauthorized viewing and modification of data in the database

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

As database technology becomes more and more widely used, the increasing number of database users and the increasing sensitivity of data content make database security more important. To prevent unauthorized viewing and modification of data in the database, you must control user access to the data. Fine-grained access control, that is, virtual private database, provides powerful row-level security functions.

Fine-grained access control is used to provide 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 can only view the account admin account, the fine-grained access control settings automatically query:

Select * from accounts;

Where am_name = 'admin ';

DBA sets a security policy on the table ACCOUNTS. This policy has a related function called policy function. It returns a where am_name = 'admin' string used as the predicate '.

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 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.

To maintain backward compatibility, the Default policy type in 10 Gb 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 = 'admin'

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 the user's 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:

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, 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, significantly improving the performance.

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 variable (application context) 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 ', and for Michelle, where CUST_NAME = 'goldtone '. Here, predicates depend on their userid, so any session they create always has the same value in the context of the application.

10 Gb 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.