11.5.2.3 Adding and Enabling the Security Policy

To add and enable the security policy, choose the target table or view and call the ADD_POLICY procedure in the DBMS_RLS package. Name the policy clearly, and indicate table or view to protect and the associated security policy function.

Since the Employee Directory needs to show all employees, create the following EBA_DEMO_EMP_V view to use for the Salary Review page:

create view eba_demo_emp_v as
select empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno
from eba_demo_emp; 

To enforce data security on the EBA_DEMO_EMP_V view using the ONLY_OWN_REPORTS policy function, use the following PL/SQL block. It first drops, then adds and enables a policy named ONLY_OWN_REPORTS_POLICY:

-- Create the row-level data security policy on the view
begin
    -- First, try to drop the policy if it exists (ignore errors)
    begin
        dbms_rls.drop_policy(
            object_schema => sys_context('USERENV','CURRENT_USER'),
            object_name   => 'EBA_DEMO_EMP_V',
            policy_name   => 'ONLY_OWN_REPORTS_POLICY');
    exception
        when others then
            null; -- Ignore errors if policy doesn't exist
    end;
    
    -- Add the row-level data security policy to the view
    dbms_rls.add_policy(
        object_schema   => SYS_CONTEXT('USERENV','CURRENT_USER'),
        object_name     => 'EBA_DEMO_EMP_V',
        policy_name     => 'ONLY_OWN_REPORTS_POLICY',
        function_schema => SYS_CONTEXT('USERENV','CURRENT_USER'),
        policy_function => 'ONLY_OWN_REPORTS',
        statement_types => 'SELECT,INSERT,UPDATE,DELETE',
        update_check    => true,
        enable          => true);
end;

Tip:

To see all policies you've applied, run this query:

SELECT 
    object_name,
    policy_name,
    function,
    policy_type,
    enable,
    sel, ins, upd, del,
    chk_option
FROM USER_POLICIES
ORDER BY object_name, policy_name