get_predicates as
-- note -- security function ALWAYS has two parameters,
324 Oracle Database 11g DBA Handbook
-- table owner name and table name
function emp_select_restrict
(owner varchar2, object_name varchar2) return varchar2;
-- other functions can be written here for INSERT, DELETE, and so forth.
end get_predicates;
create or replace package body vpd.get_predicates is
function emp_select_restrict
(owner varchar2, object_name varchar2) return varchar2 is
ret_predicate varchar2(1000); -- part of WHERE clause
begin
-- only allow employee to see their row or immediate subordinates
ret_predicate := 'EMPLOYEE_ID = ' ||
sys_context('HR_SECURITY','EMP_ID') ||
' OR MANAGER_ID = ' ||
sys_context('HR_SECURITY','EMP_ID');
return ret_predicate;
end emp_select_restrict;
end; -- package body
Once we attach the function to a table with DBMS_RLS, it will generate a text string that can
be used in a WHERE clause every time the table is accessed. The string will always look
something like this:
EMPLOYEE_ID = 124 OR MANAGER_ID = 124
As with the packages that set up the context environment, we need to allow users access to
this package:
grant execute on vpd.get_predicates to PUBLIC;
create public synonym get_predicates for vpd.get_predicates;
Last, but certainly not least, we will attach the policy function to the table using the DBMS_
RLS.ADD_POLICY procedure:
dbms_rls.add_policy (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_SELECT_RESTRICT',
function_schema => 'VPD',
policy_function => 'get_predicates.
Pages:
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536