Defining and using compartments and groups

By using compartments and groups, the granularity of access to data might be increased considerably. In this recipe, we will cover how to implement additional groups and compartments in the table EMPLOYEES_OLS_TBL. The compartments will be created based on departments, and the groups will be based on countries and regions.

Getting ready

All steps will be performed on the HACKDB database.

How to do it...

Before we start, it is mandatory to find and design a method to compartmentalize and group the data. This is shown in the following steps:

  1. Each department manager must be able to see his own data. There will also be users with permissions to see the compartmentalized and grouped data corresponding to their departments. As the user SKING, issue the following SELECT to gather the needed information:
    SQL> conn SKING
Enter password:
Connected.
SQL> select distinct job_title,country_name,email from hr.EMPLOYEES_OLS_TBL where job_title like '%Manager';

JOB_TITLE            COUNTRY_NAME              EMAIL        
-----------------	----------------------- 	----------- 
Purchasing Manager   United States of America  DRAPHEAL
Stock Manager        United States of America  AFRIPP                    
Stock Manager        United States of America  MWEISS                    
Stock Manager        United States of America  SVOLLMAN                  
Stock Manager        United States of America  KMOURGOS                  
Sales Manager        United Kingdom            KPARTNER                  
Sales Manager        United Kingdom            GCAMBRAU                  
Marketing Manager    Canada                    MHARTSTE                  
Stock Manager        United States of America  PKAUFLIN                  
Accounting Manager   United States of America  SHIGGINS                  
Sales Manager        United Kingdom            AERRAZUR                 
Finance Manager      United States of America  NGREENBE                  
Sales Manager        United Kingdom            JRUSSEL                   
Sales Manager        United Kingdom            EZLOTKEY                  

14 rows selected.
  2. To simplify the scenario and to make it reasonably short, we will choose to group based on country, namely United States of America and United Kingdom, and regions such as AMERICAS and EUROPE, and compartmentalize based on just three departments, such as Purchasing, Stock, and Sales. The compartmentalizing of departments and the grouping based on regions and countries can be summarized as follows:

    User

    Compartment

    Group

    Parent Group

    Label format

    DRAPHEAL

    Purchasing (PUR)

    United States of America (US)

    Americas (AM)

    OAC:PUR:AM,US

    AFRIPP

    Stock (STO)

    United States of America (US)

    Americas

    (AM)

    OAC:STO:AM,US

    KPARTNER

    Sales (SAL)

    United Kingdom (UK)

    Europe (EU)

    OAC:SAL:EU,UK

  3. Connect as the user system and create the user DRAPHEAL, who is the manager of the Purchasing department. This user will be allocated to the Purchasing (PUR) compartment and the United States of America (US) group, defined within the parent Americas (AM) group:
    SQL> conn system
Enter password:
Connected.
SQL> create user DRAPHEAL identified by DRAPHEAL;

User created.	
SQL>
  4. Next create the user AFRIPP, who is the manager of the Stock department. This user will be allocated to the Stock (STO) compartment and the United States of America (US) group, defined within the parent group Americas (AM):
    SQL> create user AFRIPP identified by AFRIPP;

User created.
  5. Then create the user KPARTNER as the manager of the Sales department. This user will be allocated to the Sales (SAL) compartment and the United Kingdom (UK) group within the parent group Europe (EU).
    SQL> create user KPARTNER identified by KPARTNER;

User created.
  6. Grant create session and select the table HR.EMPLOYEES_OLS_TBL for users DRAPHEAL, KPARTNER, and AFRIPP, as follows:
    SQL> grant create session to DRAPHEAL,KPARTNER, AFRIPP;

Grant succeeded.
SQL> grant select on hr.employees_ols_tbl to DRAPHEAL,KPARTNER, AFRIPP;

Grant succeeded.
  7. In this step we will proceed to create the compartments. Connect as the user LBACSYS and create the PURCHASING compartment with the numeric level 1390 assigned to the REG_ACCESS policy, as follows:
    SQL> conn LBACSYS
Enter password:
Connected.
SQL>
SQL>
begin 
LBACSYS.SA_COMPONENTS.CREATE_COMPARTMENT(policy_name => 'REG_ACCESS', comp_num => 1390, short_name => 'PUR', long_name => 'PURCHASING'), 
end;
SQL> /
PL/SQL procedure successfully completed.
  8. Create the Stock compartment with the numeric level 1395 assigned to the policy REG_ACCESS, as follows: 
    SQL>
begin 
LBACSYS.SA_COMPONENTS.CREATE_COMPARTMENT(policy_name => 'REG_ACCESS', comp_num => 1395, short_name => 'STO', long_name => 'STOCK'), 
end;
SQL> /
PL/SQL procedure successfully completed.
  9. Create the Sales compartment with the numeric level 1400 assigned to the policy REG_ACCESS, as follows:
    SQL>
begin 
LBACSYS.SA_COMPONENTS.CREATE_COMPARTMENT(policy_name => 'REG_ACCESS', comp_num => 1400, short_name => 'SAL', long_name => 'SALES'), 
end;

SQL> /
PL/SQL procedure successfully completed.
  10. In this step we start by creating the parent groups AMERICAS and EUROPE. Create the parent group AMERICAS with the numeric level 3400 assigned to the REG_ACCESS policy, as follows:
    SQL>begin 
LBACSYS.SA_COMPONENTS.CREATE_GROUP(policy_name => 'REG_ACCESS', group_num => 3400, short_name => 'AM', long_name => 'AMERICAS', parent_name => null); 
end;
SQL> /
PL/SQL procedure successfully completed.
  11. Create the parent group EUROPE with the numerical level 3500 assigned to the REG_ACCESS policy, as follows:
    begin 
LBACSYS.SA_COMPONENTS.CREATE_GROUP(policy_name => 'REG_ACCESS', group_num => 3500, short_name => 'EU', long_name => 'EUROPE', parent_name => null); 
end;
/ SQL> /
PL/SQL procedure successfully completed.
  12. Create the group UNITED STATES OF AMERICA with the numerical level 3410 and the parent group AMERICAS, as follows:
    begin
LBACSYS.SA_COMPONENTS.CREATE_GROUP(policy_name => 'REG_ACCESS', group_num => 3410, short_name => 'US', long_name => 'UNITED STATES OF AMERICA', parent_name => 'AM'), 
end;
/ SQL> /
PL/SQL procedure successfully completed.
  13. Create the United Kingdom group with the numerical level 3510 and the parent group EUROPE, as follows:
    begin 
LBACSYS.SA_COMPONENTS.CREATE_GROUP(policy_name => 'REG_ACCESS', group_num => 3510, short_name => 'UK', long_name => 'UNITED KINGDOM', parent_name => 'EU'), 
end;
/ SQL> /
PL/SQL procedure successfully completed.
  14. Define the label OAC:PUR:AM,US corresponding to the Purchase department, as follows:
    begin 
SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'REG_ACCESS', label_tag => 30, label_value => 'OAC:PUR:AM,US', data_label => TRUE); 
end;
/SQL> /

PL/SQL procedure successfully completed.
SQL>
  15. Define the label OAC:STO:AM,US corresponding to the Stock department, as follows:
    begin 
SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'REG_ACCESS', label_tag => 31, label_value => 'OAC:STO:AM,US', data_label => TRUE); 
end;
SQL> /

PL/SQL procedure successfully completed.
  16. And define the last label OAC:SAL:EU,UK, corresponding to the Sales department, as follows: 
    SQL>
begin 
SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'REG_ACCESS', label_tag => 32, label_value => 'OAC:SAL:EU,UK', data_label => TRUE); 
end;
/
SQL> /

PL/SQL procedure successfully completed.
SQL>
  17. Grant authorization on the label OAC:PUR:AM,US to the user DRAPHEAL, as follows:
    begin sa_user_admin.set_user_labels
                      (policy_name    => 'REG_ACCESS',
                       user_name      => 'DRAPHEAL',
                      max_read_label => 'OAC:PUR:AM,US'),
end;
/
SQL> /

PL/SQL procedure successfully completed.
  18. Grant authorization on the label OAC:STO:AM,US to the user AFRIPP, as follows:
    begin sa_user_admin.set_user_labels
                      (policy_name    => 'REG_ACCESS',
                       user_name      => 'AFRIPP',
                      max_read_label => 'OAC:STO:AM,US'),
end;
/ SQL> /

PL/SQL procedure successfully completed.
  19. Grant authorization on the label OAC:SAL:EU,UK to the user KPARTNER, as follows: 
    begin sa_user_admin.set_user_labels
                      (policy_name    => 'REG_ACCESS',
                       user_name      => 'KPARTNER',
                      max_read_label => 'OAC:SAL:EU,UK'),
end;
SQL> /

PL/SQL procedure successfully completed.
  20. The labels must be updated by a user who is able to bypass the label security already applied on the table. There are a collection of special privileges that can be granted to bypass label security. One is FULL, which once granted will allow the user to bypass all label security. We will cover this subject in the Using label policy privileges recipe, later in this chapter. Grant FULL access privilege to the schema user HR as follows:
    SQL> begin SA_USER_ADMIN.SET_USER_PRIVS(policy_name => 'REG_ACCESS', user_name => 'HR', privileges => 'FULL'), end;
  2  /
  21. Then label the rows using the label OAC:PUR:AM,US for the Purchasing manager DRAPHEAL, as follows:
    SQL> update 
employees_ols_tbl set lb_column = char_to_label ('REG_ACCESS','OAC:PUR:AM,US') where department_name ='Purchasing';

6 rows updated.

SQL> commit;

Commit complete.
  22. Next label the rows of the department Shipping, which is a part of the Stock department, for the user AFRIPP as follows: 
    SQL> update hr.employees_ols_tbl set lb_column = char_to_label ('REG_ACCESS','OAC:STO:AM,US') where department_name ='Shipping';

45 rows updated.

SQL> commit;

Commit complete.
  23. Finally, label the rows for the user KPARTNER as follows:
    SQL> update employees_ols_Tbl set lb_column = char_to_label ('REG_ACCESS','OAC:SAL:EU,UK') where department_name ='Sales';

34 rows updated.

SQL> commit;

Commit complete.
  24. Now check the visibility for the user DRAPHEAL. Execute the following code as the user DRAPHEAL:
    SQL> conn DRAPHEAL
Enter password:
Connected.
SQL> select distinct job_title from hr.employees_ols_tbl;

JOB_TITLE
-----------------------------------
Purchasing Clerk
Purchasing Manager
Human Resources Representative
  25. As we can see, the user DRAPHEAL has read rights over his/her labeled rows and over the rows labeled with OAC (other access). Connect as the user SMAVRIS and issue the same statement as follows:
    SQL> conn SMAVRIS
Enter password:
Connected.
SQL> select distinct job_title from hr.reg_data_tbs;

JOB_TITLE
-----------------------------------
Human Resources Representative
  26. Also the user SKING has no authorization to see the Purchasing, Stock, and Sales rows at the moment: 
    SQL> select distinct job_title from hr.reg_data_tbs;

JOB_TITLE
-----------------------------------
Administration Vice President
President
Human Resources Representative

SQL>
  27. For the user SKING to be able to select all the rows labeled within the compartments and groups, he/she needs to have authorization over compartments and groups. Connect as LBACSYS and grant access to user SKING over Purchase, Stock, and Sales compartments and over United States, United Kingdom, Europe and Americas groups and parent groups by executing the following:
    SQL> conn LBACSYS
Enter password:
Connected.
SQL> begin sa_user_admin.add_compartments (policy_name=>'REG_ACCESS',user_name=>'SKING',comps=>'PUR,STO,SAL'), end;
  2  /

PL/SQL procedure successfully completed.

SQL> begin LBACSYS.SA_USER_ADMIN.ADD_GROUPS(policy_name => 'REG_ACCESS', user_name => 'SKING', groups => 'US,UK,AM,EU'), end;
  2  /

PL/SQL procedure successfully completed.

SQL>
  28. Now the user SKING has received access rights on all the rows. Connect again as the user SKING and reissue the statement from step 26, as follows: 
    SQL> select distinct job_title from hr.employees_ols_tbl;

JOB_TITLE
-----------------------------------
Sales Representative
Purchasing Clerk
Administration Vice President
Stock Manager
President
Purchasing Manager
Human Resources Representative
Shipping Clerk
Stock Clerk
Sales Manager

10 rows selected.

SQL>

How it works...

Compartments, together with groups, are generally used to better segregate data. Compartments do not have ranks and are not hierarchical. The numerical tags are used just for reference, and control only the display order in the label character string. The numeric value can range from 0 to 9999. Similarly, groups do not have ranks; the numerical value controls only the display order in the label character string. The only difference from compartments is that they may have hierarchy a (parent/child relationship).

A recommended way of using compartments and groups would be to implement them in a manner that reproduces the organization of your company.

There's more...

By using compartments and groups, the label authorizations will change. You may have separate compartment and group authorizations.

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

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