Analysis of Oracle-vpd
Oracle permission control uses the dbms_rls package of Oracle to implement data access control in most systems. Permission control is mainly defined as the module access permission Control and Data column access permission control (for example: A certain person can enter a certain control, and the warehouse does not have to check related department fields ). However, in some systems, permission control must be defined to control the access permissions of data rows. This requirement usually occurs in the same system and is used by different independent organizations. (For example, if multiple subsidiaries of the group use the same data table, but the data of different subsidiaries is relatively isolated ), most people will choose to add the WHERE clause to view for data isolation. This method requires a large amount of coding workload and a small amount of elastic space for the system 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.
This article describes how to use the policy management method provided by Oracle to isolate data rows. Note: The policy here is tested on 9i, And the 8i policy is different from 9i, but the principle is the same. (1) create a test data table (t_policy): Create Table t_policy (www.2cto.com T1 varchar2 (10 byte), T2 number (10); insert into t_policy values ('A ', 10); insert into t_policy values ('B', 20); insert into t_policy values ('C', 30); Commit; (2) create a test policy function: create or replace function fn_getpolicy (p_schema in varchar2, p_object in varchar2) return varc Har2 is result varchar2 (1000); begin result: = 't2 not in (10) '; Return (result); End fn_getpolicy;/www.2cto.com (3) Add Policy: declarebegindbms_rls.add_policy (object_schema => 'niegc', -- schema name of the data table (or view) object_name => 't_policy', -- data table (or view) the name of policy_name => 't_testpolicy ', -- Policy Name, mainly used for policy management in the future function_schema => 'niegc ', -- returns the schema name of the function in the WHERE clause, which is policy_function => 'fn _ getpolicy'. -- returns the name of the function in the WHERE clause Sta. Tement_types => 'select, insert, update, delete', -- the DML type of the policy to be used, such as 'select, insert, update, delete' update _ Check => true, -- only applicable to statement_type: 'insert, Update'; Value: 'true' or 'false' enable => true -- enable or not; Value: 'true' or 'false '); end; Note: If update_check is set to 'true', the DML execution returns an error message when the value inserted by the user does not meet the condition returned by policy_function. Now we can work: Select * From t_policy; to see how the result is, Is it missing t2 = 10. www.2cto.com (4) delete policydeclarebegin dbms_rls.drop_policy ('niegc', 't_ policy', 't_ testpolicy'); end; (5) set the state of the policy to declarebegin dbms_rls.enable_policy ('niegc', 't_ policy', 't_ testpolicy', false); end; (6) view the policy. You can see it in the user_policies table.
