Oracle POLICY learning

Oracle POLICY learning

01 -- 1. create test table 02 create table TEST_POLICY03 (04 USERNAME VARCHAR2 (10), 05 password number (10) 06); 07 insert into t_policy values ('A', 30 ); 08 insert into t_policy values ('B', 20); 09 insert into t_policy values ('C', 40); 10 commit; 11--2. Create a policy constraint function, only users with a password of 40 can be entered in the Table. Other users will be deleted 12 create or replace Function Fn_GetPolicy (P_Schema in varchar2, 13 P_Object in varchar2) 14 return varchar2 is15 L_PREDICATE VARCHAR2 (1000): = ''; 16Beg In17 L_PREDICATE: = 'password = 40'; 18 Return L_PREDICATE; 19end Fn_GetPolicy; 20--3. Create a policy 21declare22Begin23 Dbms_Rls.Add_Policy (Object_Schema => 'Scott ', -- data table (or view) schema name 24 Object_Name => 'test _ policy', -- Name of the data table (or view) 25 Policy_Name => 't_testpolicy', -- Name of the Policy, it is mainly used for Policy management in the future 26 Function_Schema => 'Scott ', -- returns the Schema name of the function in the Where Clause 27 Policy_Function => 'fn _ getpolicy ', -- returns the Where clause's function name 28 Stat Ement_Types => 'select, Insert, Update, delete', -- the DML type of the Policy to be used, such as 'select, Insert, Update, delete' 29 Update_Check => True, -- only applicable to Statement_Type: 'insert, Update'; Value: 'true' or 'false' 30 Enable => True -- whether to Enable, value: 'true' or 'false' 31); 32end; 33 -- Note: If Update_Check is set to 'true', when the value inserted by the user does not meet the condition returned by Policy_Function, the DML execution returns an error message. 34 -- now you can work: select * from t_policy; check whether the password <> 40 is missing. 35--4. Check the current user's Policy 36 SELECT * FROM USER_POLICIES; 37--5. Delete the Policy 38declare39inin40 Dbms_Rls.drop_policy ('Scott ', -- the Schema41 'test _ policy' where the Policy to be deleted is located ', -- Name of the data table (or view) for which the Policy is to be deleted: 42 't_ testpolicy' -- name 43 of the Policy to be deleted); 44end;