Implementing VPD grouped policies

There may be cases where you want to use different VPD policies on the same object. In such cases VPD offers a feature named grouped policies that can be used to assign policies to different groups and to trigger them depending on certain conditions. Enabling one policy or another will be decided by a driver context according to certain parameters declared at the application level. The following recipe will demonstrate how to use this VPD feature.

In this recipe we will create a table that will contain three different department groups.

We will create a new user STOBIAS in addition to the DOCONNEL and JWHALEN users created earlier, in order to have one user for each group of departments. For each group of departments a group policy will be defined. These grouped policies will isolate the role of each group based on user membership. Each user will see his department determined by a driver context.

Getting ready

All steps will be performed on the database HACKDB.

How to do it...

  1. Connect as the user HR and create the DEPARTMENT_CATEGORY table as follows:
    SQL>CREATE TABLE HR.DEPARTMENT_CATEGORY
      (
        DEPID_CAT1 NUMBER,
        DEP_CAT1   VARCHAR2(100 BYTE),
        DEPID_CAT2 NUMBER,
        DEP_CAT2   VARCHAR2(100 BYTE),
        DEPID_CAT3 NUMBER,
        DEP_CAT3   VARCHAR2(100 BYTE)
      )
      SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
      (
        INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
      )
      TABLESPACE USERS ;
Table created.

SQL>
  2. Next, insert control data into department_category. The data will be used by the driving context:
    SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (10,'Administration',20,'Marketing',30,'Purchasing'),

1 row created.

SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (40,'Human Resources',50,'Shipping',60,'IT'),

1 row created.

SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (70,'Public Relations',80,'Sales',90,'Executive'),

1 row created.

SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (100,'Finance',110,'Accounting',120,'Treasury'),

1 row created.

SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (130,'Corporate Tax',140,'Control And Credit',150,'Sha
reholder Services'),

1 row created.

SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (160,'Benefits',170,'Manufacturing',180,'Construction'
);

1 row created.

SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (190,'Contracting',200,'Operations',210,'IT Support'),


1 row created.

SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (220,'NOC',230,'IT Helpdesk',240,'Government Sales'),

1 row created.

SQL> Insert into DEPARTMENT_CATEGORY (DEPID_CAT1,DEP_CAT1,DEPID_CAT2,DEP_CAT2,DEPID_CAT3,DEP_CAT3) values (250,'Retail Sales',260,'Recruiting',270,'Payroll'),

1 row created.

SQL> commit;

Commit complete.

SQL>
  3. Connect as user system and create a user STOBIAS; grant the create session privileges to it:
    SQL> conn system
Enter password:
Connected.

SQL> create user STOBIAS identified by STOBIAS;

User created.

SQL> grant create session to STOBIAS;

Grant succeeded.

SQL>
  4. Next, issue grant select on DEPARTMENT_CATEGORY to DOCONNEL, JWHALEN, and STOBIAS as follows:
    SQL>  grant select on hr.department_category to stobias,doconnel,jwhalen;

Grant succeeded.
  5. Connect as system and create the driving context dep_cat_context as follows:
    SQL> conn system
Enter password:
Connected.
SQL> CREATE OR REPLACE CONTEXT dep_cat_context USING department_cat_pkg;

Context created.
  6. From now on we will create one policy for each category. Create policy group category_dept_one as follows:
    SQL>
SQL> BEGIN
  2    DBMS_RLS.CREATE_POLICY_GROUP( object_schema => 'HR', object_name => 'department_category', policy_group => 'category_dept_one'),
  3  END;
  4  /
PL/SQL procedure successfully completed.
  7. Create the policy group category_dept_two as follows:
    SQL> 
SQL> BEGIN
  2    DBMS_RLS.CREATE_POLICY_GROUP( object_schema => 'HR', object_name => 'department_category', policy_group => 'category_dept_two'),
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
  8. Create the policy group category_dept_three as follows:
    SQL> BEGIN
  2    DBMS_RLS.CREATE_POLICY_GROUP( object_schema => 'HR', object_name => 'department_category', policy_group => 'category_dept_three'),
  3  END;
  4  /

PL/SQL procedure successfully completed.
  9. Next, we will create three policy functions that will be assigned to each grouped policy. Create the policy function for category one, named vpd_function_category_one, as follows:
    SQL> CREATE OR REPLACE
  2    FUNCTION VPD_FUNCTION_CATEGORY_ONE
  3      (
  4        V_SCHEMA IN VARCHAR2,
  5        V_TABLE  IN VARCHAR2)
  6      RETURN VARCHAR2
  7    AS
  8      PREDICATE VARCHAR2(8) DEFAULT NULL;
  9    BEGIN
 10      IF (SYS_CONTEXT('USERENV','SESSION_USER')) = 'JWHALEN' THEN
 11        predicate                               := '1=2';
 12      ELSE
 13        NULL;
 14      END IF;
 15      RETURN predicate;
 16    END;
 17  /

Function created.
  10. Create the policy function vpd_function_category_two as follows:
    SQL> CREATE OR REPLACE FUNCTION vpd_function_category_two
  2    (v_schema in varchar2, v_table in varchar2) return varchar2 as
  3     predicate varchar2(8) default NULL;
  4      BEGIN
  5      IF (SYS_CONTEXT('USERENV','SESSION_USER')) = 'DOCONNEL'
  6      THEN predicate := '1=2';
  7      ELSE NULL;
  8      END IF;
  9      RETURN predicate;
 10    END;
 11  /

Function created.
  11. Create the policy function vpd_function_category_three as follows:
    SQL> CREATE OR REPLACE
  2    FUNCTION vpd_function_category_three
  3      (
  4        v_schema IN VARCHAR2,
  5        v_table  IN VARCHAR2)
  6      RETURN VARCHAR2
  7    AS
  8      predicate VARCHAR2(8) DEFAULT NULL;
  9    BEGIN
 10      IF (SYS_CONTEXT('USERENV','SESSION_USER')) = 'STOBIAS' THEN
 11        predicate                               := '1=2';
 12      ELSE
 13        NULL;
 14      END IF;
 15      RETURN predicate;
 16    END;
 17  /

Function created.

SQL>
  12. Next, we will create the grouped policies for each department category. Create the grouped policy named vpd_function_category_one_plc for category one as follows:
      1  
SQL> BEGIN
  2    DBMS_RLS.ADD_GROUPED_POLICY( object_schema => 'HR', object_name => 'department_category', policy_group => 'category_dept_one', policy_name => 'vpd_function_category_one_plc', policy_function => 'vpd_function_category_one', statement_types => 'select', policy_type => DBMS_RLS.CONTEXT_SENSITIVE, sec_relevant_cols
=> 'depid_cat2,dep_cat2,depid_cat3,dep_cat3', sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
  3  END;

PL/SQL procedure successfully completed.

SQL>
  13. Next create a grouped policy named vpd_function_category_two_plc for category two as follows:
      1  
SQL> BEGIN
  2    DBMS_RLS.ADD_GROUPED_POLICY( object_schema => 'HR', object_name => 'department_category', policy_group => 'category_dept_two', policy_name => 'vpd_functi
on_category_two_plc', policy_function => 'vpd_function_category_two', statement_types => 'select', policy_type => DBMS_RLS.CONTEXT_SENSITIVE, sec_relevant_cols
=> 'depid_cat1,dep_cat1,depid_cat3,dep_cat3', sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
  3  END;
  4  /

PL/SQL procedure successfully completed.
  14. And finally create the policy named vpd_function_cat_three_plc for the last department category as follows:
    SQL> BEGIN
  2    DBMS_RLS.ADD_GROUPED_POLICY( object_schema => 'HR', object_name => 'department_category', policy_group => 'category_dept_three', policy_name => 'vpd_function_cat_three_plc', policy_function => 'vpd_function_category_three', statement_types => 'select', policy_type => DBMS_RLS.CONTEXT_SENSITIVE, sec_relevant_cols
 => 'depid_cat1,dep_cat1,depid_cat2,dep_cat2', sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
  3  END;
  4  /
PL/SQL procedure successfully completed.

SQL>
  15. Next, create the package and package body department_cat_pkg, associated with the context dep_cat_context:
    SQL> 
SQL> CREATE OR REPLACE
  2  PACKAGE department_cat_pkg
  3  IS
  4  PROCEDURE set_dep_cat_context
  5    (
  6      plc_grp VARCHAR2 DEFAULT NULL);
  7  END;
  8  /

Package created.

SQL>/

Package created.

SQL> 
SQL> CREATE OR REPLACE
  2  PACKAGE BODY department_cat_pkg
  3  AS
  4  PROCEDURE set_dep_cat_context
  5    (
  6      plc_grp VARCHAR2 DEFAULT NULL)
  7  IS
  8  BEGIN
  9    CASE (SYS_CONTEXT('USERENV', 'SESSION_USER'))
 10    WHEN 'JWHALEN' THEN
 11      DBMS_SESSION.SET_CONTEXT('dep_cat_context','plc_grp','CATEGORY_DEPT_ONE'),
 12    WHEN 'DOCONNEL' THEN
 13      DBMS_SESSION.SET_CONTEXT('dep_cat_context','plc_grp','CATEGORY_DEPT_TWO'),
 14    WHEN 'STOBIAS' THEN
 15      DBMS_SESSION.SET_CONTEXT('dep_cat_context','plc_grp','CATEGORY_DEPT_THREE'),
 16    ELSE
 17      NULL;
 18    END CASE;
 19  EXCEPTION
 20  WHEN NO_DATA_FOUND THEN
 21    NULL;
 22  END set_dep_cat_context;
 23  END;
 24  /

Package body created.

SQL>/
  16. Next, assign the dep_cat_context context to department_category as the driving context:
    SQL> BEGIN
  2    DBMS_RLS.ADD_POLICY_CONTEXT( object_schema =>'HR', object_name =>'department_category', namespace =>'dep_cat_context',
  3    attribute =>'plc_grp'),
  4  END;
  5  /
PL/SQL procedure successfully completed.
SQL>
  17. Next, create a new logon trigger to set the driving context after connecting, as follows:
    SQL> CREATE OR REPLACE TRIGGER set_dep_cat_context_trg AFTER LOGON ON DATABASE
  2    BEGIN
  3      security_adm.department_cat_pkg.set_dep_cat_context;
  4    END;
  5  /

Trigger created.
  18. Next, connect as DOCONNEL, check the plc_grp value from the driving context, and issue a SELECT statement on department_category to check if the grouped policy is enforced:
    SQL> conn DOCONNEL
Enter password:
Connected.
SQL> select sys_context('dep_cat_context','plc_grp') as DRIVING_CONTEXT from dual;

DRIVING_CONTEXT
--------------------------------------------------------------------------------
category_dept_two
SQL> 
SQL> select depid_cat1,dep_cat1,depid_cat2,dep_cat2,depid_cat3,dep_cat3 from hr.department_category;

---------------------------------------------------- ---------- -
                     20 Marketing
                     50 Shipping
                     80 Sales
                    110 Accounting
                    140 Control And Credit
                    170 Manufacturing
                    200 Operations
                    230 IT Helpdesk
                    260 Recruiting

9 rows selected.
Just departments from category 2 are visible for DOCONNEL
  19. Connect as the STOBIAS user, check the plc_grp value from the driving context, and issue a SELECT statement on department_category to check if the grouped policy is enforced:
    SQL> conn STOBIAS/STOBIAS
Connected.
SQL> select sys_context('dep_cat_context','plc_grp') from dual;

SYS_CONTEXT('DEP_CAT_CONTEXT','PLC_GRP')
CATEGORY_DEPT_THREE

SQL> select depid_cat1,dep_cat1,depid_cat2,dep_cat2,depid_cat3,dep_cat3 from hr.department_category;
----------------------------------------------------------------
                                  30 Purchasing
                                  60 IT
                                  90 Executive
                                 120 Treasury
                                 150 Shareholder Services
                                 180 Construction
                                 210 IT Support
                                 240 Government Sales
                                 270 Payroll

9 rows selected.

SQL>
  20. And finally connect as the user JWHALEN, check the plc_grp value from the driving context, and issue a SELECT statement on department_category to check if the grouped policy is enforced:
    SQL> conn JWHALEN/JWHALEN
Connected.
SQL> select sys_context('dep_cat_context','plc_grp') from dual;

SYS_CONTEXT('DEP_CAT_CONTEXT','PLC_GRP')
---------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
CATEGORY_DEPT_ONE

SQL> select depid_cat1,dep_cat1,depid_cat2,dep_cat2,depid_cat3,dep_cat3 from hr.department_category;

--------------------------------------- ---------- - ---------- -
        10 Administration
        40 Human Resources
        70 Public Relations
       100 Finance
       130 Corporate Tax
       160 Benefits
       190 Contracting
       220 NOC
       250 Retail Sales

9 rows selected.

SQL>

How it works...

In grouped policies, the active policy is decided by using the driving context. In our example, the driving context is "dep_cat_context" defined with the ADD_POLICY_CONTEXT procedure from the DBMS_RLS package. Its attribute is modified depending on which user connects.

There's more...

More information about grouped policies can be found in the ALL_POLICIES_GROUP, DBA_POLICIES_GROUPS, and DBA_POLICY_CONTEXTS dictionary views.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.223.196.146