SQL> select sys_context('HR_SECURITY','SEC_LEVEL') from dual;
SYS_CONTEXT('HR_SECURITY','SEC_LEVEL')
--------------------------------------------------------------
NORMAL
To enforce the requirement that only HR employees can see salary information, we would
need to make a slight change to the policy function and enable the policy with column-level
restrictions:
dbms_rls.add_policy (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_SELECT_RESTRICT',
function_schema => 'VPD',
policy_function => 'get_predicates.emp_select_restrict',
statement_types => 'SELECT',
update_check => TRUE,
enable => TRUE,
sec_relevant_cols => 'SALARY',
sec_relevant_cols_opt => dbms_rls.all_rows
);
The last parameter, SEC_RELEVANT_COLS_OPT, specifies the package constant DBMS_RLS.
ALL_ROWS to indicate that we still want to see all rows in our query results, but with the relevant
columns (in this case SALARY) returning NULL values. Otherwise, we would not see any rows
from queries that contain the SALARY column.
Debugging a VPD Policy
Even if you??™re not getting an ???ORA-28113: policy predicate has error??? or an ???ORA-00936: missing
expression,??? it can be very useful to see the actual predicate being generated at statement parse
time. There are a couple of ways to debug your predicates, both have their advantages and
disadvantages.
The first method uses the dynamic performance views V$SQLAREA and V$VPD_POLICY.
Pages:
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541