-- sys --
CREATE OR REPLACE
PACKAGE PROTECT_HR_SALARY_PKG AS
procedure set_app_context(emp_id_value IN number);
function the_predicate (schema_in IN varchar2,name_in IN VARCHAR2) return varchar2;
END PROTECT_HR_SALARY_PKG;
CREATE OR REPLACE
PACKAGE BODY PROTECT_HR_SALARY_PKG
AS
procedure set_app_context(emp_id_value IN number) AS
BEGIN
dbms_session.set_context ('protect_hr_salaries','emp_id_value',''||emp_id_value);
NULL;
END set_app_context;
function the_predicate (schema_in IN varchar2,name_in IN VARCHAR2) return varchar2
AS
v_r varchar2(2000);
employee_id_ number;
BEGIN
employee_id_:=nvl(sys_context('protect_hr_salaries','emp_id_value'),0);
v_r := 'department_id=( select department_id from hr.departments where manager_id='||employee_id_||')';
return v_r;
END the_predicate;
END PROTECT_HR_SALARY_PKG;
create or replace context protect_hr_salaries using PROTECT_HR_SALARY_PKG;
-- Apply the policy function to the table.
BEGIN
DBMS_RLS.ADD_POLICY (object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'PROTECT_EMPLOYEES',
function_schema => 'sys',
policy_function => 'PROTECT_HR_SALARY_PKG.the_predicate',
statement_types => 'SELECT,INSERT,UPDATE,DELETE',
enable => TRUE);
END;
/
grant execute on PROTECT_HR_SALARY_PKG to hr
hr
begin
sys.PROTECT_HR_SALARY_PKG. set_app_context(103);
end;
/
SQL> select first_name,salary from employees;
FIRST_NAME SALARY
-------------------- ----------
Alexander 9000
Bruce 6000
David 4800
Valli 4800
Diana 4200
begin
sys.PROTECT_HR_SALARY_PKG. set_app_context(102);
end;
/
SQL> select first_name,salary from employees;
no rows selected
