Oracle policy problems

Oracle policy problems
Oracle policies can restrict query, modification, deletion, and addition operations. A test is performed on the query as follows: begin -- Call the proceduresys. dbms_rls.add_policy (object_schema =>: name of the Schema where the data table (or view) is located/user, object_name =>: name of the data table (or view), policy_name =>: policy Name function_schema =>: return the Schema name/user of the Where clause function, policy_function =>: return the function name of the Where clause, statement_types =>: DML type of the Policy to be used, such as 'select, Insert, Update, delete', update_check => only applicable to Statement_Type: 'insert, Update'; Value: 'true' or' False', enable => enable or not. The value is 'true' or 'false', and static_policy => the default value is False. If it is set to TRUE, this policy is enabled by all users, except sys or privileged users. Policy_type =>: The default value is null, which means that the value of static_policy is determined. specifying any policy here will overwrite the value of static_policy. Long_predicate => long_predicate, sec_relevant_cols =>: sensitive field name, sec_relevant_cols_opt =>: Set it to dbms_rls.ALL_ROWS to display all rows. The value of sensitive columns is null); end; create a function:

Create or replace function f_policy (p_owner in varchar2, -- two parameters must have different names: p_object in varchar2) return varchar2 as v_ SQL varchar2 (2000); begin v_ SQL: = lower (sys_context ('userenv', 'current _ SQL ', 4000); if instr (v_ SQL, 'where') = 0 then return 'deptno = 10 '; -- raise_application_error (-20001, 'contains no where condition'); end if; return ''; end;

 

Add a policy under sys ):

begindbms_rls.add_policy(object_schema => 'scott', object_name => 'emp',policy_name => 'sal', function_schema => 'scott',policy_function => 'f_policy', sec_relevant_cols => 'sal');end;

 

This policy and function combination limit the following example: select * from emp; deletion policy:

begin   sys.dbms_rls.drop_policy(object_schema => 'scott',                            object_name => 'emp',                            policy_name => 'sal'); end;