Oracle Policy-related

Oracle policies can limit query, modify, delete, new, and so on, just contact, the query to do a test:

Reference http://blog.csdn.net/diyyong/article/details/19552637

Use the following:

Begin
--Call the procedure
Sys.dbms_rls.add_policy (Object_schema =: The schema name/user in which the datasheet (or view) resides,
object_name =: The name of the datasheet (or view),
Policy_name = Name of the:p olicy
Function_schema =: The function that returns the WHERE clause is the schema name/user,
Policy_function =: Returns the function name of the WHERE clause,
Statement_types =: To use the policy's DML type, such as ' Select,insert,update,delete ',
Update_check = only applies to Statement_type as ' insert,update ', value ' True ' or ' False ',
Enable + = is enabled with a value of ' True ' or ' False ',
Static_policy = default value is False. If it is set to true then all users are enabled for this policy, SYS or privileged user exception.
Policy_type = = The default value is null, meaning that the value of Static_policy determines that any policy specified here overrides the Static_policy value.
Long_predicate = Long_predicate,
Sec_relevant_cols =: Sensitive field names,
Sec_relevant_cols_opt =: set to Dbms_rls. All_rows to display all rows, the value of the sensitive column is null);
End

To create a function:

Create or Replace function F_policy (  p_owner  in varchar2,--two parameters must have, name can be different p_object in  varchar2) return VARCHAR2 as  v_sql varchar2; begin  V_sql: = Lower (Sys_context (' USERENV ', ' current_sql ', 4000));  If InStr (V_sql, ' where ') = 0 Then    return ' deptno=10 ';    --raise_application_error (-20001, ' does not contain a where condition ');  End If;  return "; end;

　　

Add a policy (to be added 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 strategy and function collocation limits the condition that if the dept=10 is not included in the DML statement about the EMP table, the example is as follows:

SELECT * from EMP;

Delete policy:

Begin    Sys.dbms_rls.drop_policy (Object_schema = ' Scott ',                             object_name = ' emp ',                             policy_name = ' Sal ');  End

　　

Oracle Policy-related