Initial Experience of Oracle Virtual Private Database (VPD)

 

I learned about Oracle's VPD technology a few weeks ago and did some tests on the EBS system. Summary: some content is taken from the network.

There are many methods to solve the permission problem in the solution of database data security access. Common methods include creating view method control, such as adding where statements to query statements for control. The view method is not easy to operate when the table structure or permission is changed. The coding workload is large, and the system has less elastic space to adapt to the user management system. Once the Permission Logic changes, the permission system may need to be modified, so that all views must be modified. The where statement can solve the problem but is not secure. It can only be controlled at the application level and cannot be controlled without the application.

The database-level security control of Oracle VPD effectively solves the permission issues of data access operations. It can control access to database objects and flexibly isolate data. Oracle has implemented VPD, it can be used directly, which is easier to implement than permission control at the application layer, and easier to maintain permissions.

VPD uses the where statement to control access control, but the where statement is automatically spliced when the database operates data, not at the application layer, in addition, the policy defined in advance determines whether to concatenate conditional statements. The main steps are as follows:

(1) Compile the function. The function returns the statement that is spliced after the where condition of the DML statement. However, the where statement is not visible when DML is executed. The system automatically adds a root policy, the condition is returned from the policy function. For example:

Table: Apps. t_policy (T1 varchar2 (10 byte), T2 number (10 ))

Policy functions:

Create or replace function fn_getpolicy (

P_schema in varchar2,

P_object in varchar2)

Return varchar2 is

Result varchar2 (1000 );

Begin

Result: = 't2 not in (10 )';

Return (result );

End fn_getpolicy;

/

 

(2) Add a policy to the Oracle database

Policies are defined in the system and maintained by DBAs. Policies can be defined as needed during application development. For example, according to the preceding table and policy function, the policy is defined as follows:

Declare

Begin

Dbms_rls.add_policy (

Object_schema => 'apps ', -- schema name of the data table (or view)

Object_name => 't_ policy', -- name of a data table (or view)

Policy_name => 't_ testpolicy ', -- Policy Name, mainly used for future policy management

Function_schema => 'apps ', -- returns the schema name of the function in the WHERE clause.

Policy_function => 'fn _ getpolicy', -- returns the function name of the WHERE clause.

Statement_types => 'select', -- DML type, such as 'select, insert, update, delete'

Enable => true -- enable or not; Value: 'true' or 'false'

);

End;

/

 

In this way, when querying the t_policy table, you can control the access according to the policy function conditions.

 

 

Dbms_rls built-in function package

Dbms_rls contains many functions that maintain tables, views, and synonym security policies. To use dbms_rls, database users must have the Execute Permission of SYS. dbms_rls. Common dbms_rls functions include:

Add_policy adds an access control policy to an object

Drop_policy Delete the policy in the object

Refresh_policy re-resolves all statements associated with the policy and cached

Enable_policy enable or disable the policy

Create_policy_group create a policy group

Add_grouped_policy Add a policy to the Policy Group

Add_policy_context: add the context of the current application

Delete_policy_group delete a Policy Group

Drop_grouped_policy: deletes a policy from the Policy Group.

Drop_policy_context: Delete the context of the active application

Enable_grouped_policy enable or disable group policies

Disable_grouped_policy: Disable a group policy.

Refresh_grouped_policy re-resolves all statements associated with and cached by the Policy Group

 

The most common options are add_policy, drop_policy, create_policy_group, and add_grouped_policy.

Parameters of add_policy:

Object_schema in varchar2 null,

Object_name in varchar2,

Policy_name in varchar2,

Function_schema in varchar2 null,

Policy_function in varchar2,

Statement_types in varchar2 null,

Update_check in Boolean false,

Enable in Boolean true,

Static_policy in Boolean false,

Policy_type in binary_integer null,

Long_predicate in Boolean false,

Sec_relevant_cols in varchar2,

Sec_relevant_cols_opt in binary_integer null

 

If you want to use a policy to control some columns in a DML access table or view, you can set the value of sec_relevant_cols (new content of Oracle 10 Gb ), by default, the entire table or view is controlled.

 

 

VPD Error Control/debugging

The most common error during VPD policy execution is "ORA-28113: Policy predicate has error", "ORA-00936: Missing expression ". Most of the time, it is because the returned statements of the policy function are spliced to the original DML, so the complete statements can be quickly found.

Method: Use the dynamic performance view v $ sqlarea and V $ vpd_policy. V $ sqlarea contains the SQL statements currently located in the Shared Pool and execution statistics. View v $ vpd_policy lists all policies and predicates currently implemented in the database. For example:

Select S. SQL _text, V. object_name, V. Policy, V. Predicate

From v $ sqlarea S, V $ vpd_policy v

Where S. hash_value = V. SQL _hash;

If you add a connection to the V $ session in this query, you can identify which user is running the SQL statement. The disadvantage of this method is that, if the database is very busy, the current SQL command may be refreshed in the sharing pool due to other SQL commands before the query can be run.