Creating and using label components

In this recipe, we will create four users and a table called EMPLOYEES_OLS_TBL. The users in this recipe will receive rights to select data from the table REG_DATA_TBS according to their hierarchy level. All steps will be performed using the Oracle labels PL/SQL interface.

Getting ready

As a prerequisite, you must have OLS installed. Details about the installation can be found in the OLS documentation link http://docs.oracle.com/cd/B28359_01/network.111/b28529/getstrtd.htm#CIHBBJFA. Another detailed description can be found on the Oracle Support doc:

How to Install/Deinstall Oracle Label Security [ID 171155.1]

All steps will be performed on the HACKDB database.

How to do it...

In the following steps, we will create a new table EMPLOYESS_OLS_TBL and label it according to each user's hierarchical position:

  1. Connect as the HR user and create the table EMPLOYEES_OLS_TBL as follows:
    SQL> conn HR
Enter password:
Connected.
SQL> 
CREATE TABLE EMPLOYEES_OLS_TBL AS
SELECT E.FIRST_NAME,
  E.LAST_NAME,
  E.EMAIL,
  E.PHONE_NUMBER,
  E.HIRE_DATE,
  J.JOB_TITLE ,
  E.SALARY,
  E.COMMISSION_PCT,
  D.DEPARTMENT_NAME,
  L.STATE_PROVINCE,
  L.CITY,
  L.POSTAL_CODE,
  C.COUNTRY_NAME,
  C.REGION_ID
FROM HR.EMPLOYEES E
JOIN HR.DEPARTMENTS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
JOIN HR.JOBS J
ON E.JOB_ID=J.JOB_ID
JOIN HR.LOCATIONS L
ON D.LOCATION_ID=L.LOCATION_ID
JOIN HR.COUNTRIES C
ON L.COUNTRY_ID=C.COUNTRY_ID

SQL> / 
SQL> Table created.
  2. Next, we will create the users who will access the table on a hierarchical base. The usernames will be identical with the corresponding e-mail address usernames stored in the EMAIL column. Connect as the user system, select the e-mail usernames from the table REG_DATA_TBS corresponding to the job_title of President, Administration Vice President, and Human Resources Representative, and create the users as follows:
    SQL> conn system
Enter password:
Connected.
SQL> SELECT email,
  2    job_title
  3  FROM HR.EMPLOYEES_OLS_TBL
  4  WHERE job_title IN ('President','Administration Vice President','Human Reso
urces Representative' );

EMAIL                     JOB_TITLE
------------------------- -----------------------------------
SMAVRIS                   Human Resources Representative
SKING                     President
NKOCHHAR                  Administration Vice President
LDEHAAN                   Administration Vice President

SQL> 
SQL> create user SMAVRIS identified by SMAVRIS;

User created.

SQL> create user SKING identified by SKING;

User created.

SQL> create user NKOCHHAR identified by NKOCHHAR;

User created.

SQL> create user LDEHAAN identified by LDEHAAN;

User created.

SQL>
  3. Grant CREATE SESSION and SELECT ON privilege on the table EMPLOYEES_OLS_TBL to the users SMAVRIS, SKING, NKOCHHAR, and LDEHAAN, as follows: 
    SQL> grant create session to SMAVRIS,SKING,NKOCHHAR,LDEHAAN;

Grant succeeded.

SQL> grant select on HR.
EMPLOYEES_OLS_TBL
to SMAVRIS,SKING,NKOCHHAR,LDEHAAN;

Grant succeeded.

SQL>
  4. For all the recipes in this chapter, we will use the LBACSYS user for all OLS administrative tasks. This user is initially locked and has his password expired by default. Set a strong password and unlock the user LBACSYS as follows:
    SQL> alter user LBACSYS identified by "yI9@T|*t619.}" account unlock;

User altered.
SQL>
  5. Now that we have set up the prerequisites for creating and using OLS components, connect as the user LBACSYS and create a policy REG_ACCESS using a label column named LD_COLUMN, as follows:
    SQL> conn LBACSYS
Enter password:
Connected.
SQL> begin SA_SYSDBA.CREATE_POLICY(policy_name => 'REG_ACCESS', column_name => 'LB_COLUMN', default_options => 'NO_CONTROL,'), end;
   /

PL/SQL procedure successfully completed.
SQL>
  6. The label component level will define the place of the label in the hierarchy. At this step, we will create three label component levels. These will be assigned to each user depending on their place in the hierarchy. Create the component level ALL_ACCESS with the highest level of access, REG_ACCESS with the second highest level, and OTHER_ACCESS with the lowest level in the hierarchy, as follows:
    SQL> begin SA_COMPONENTS.CREATE_LEVEL(policy_name => 'REG_ACCESS', level_num => 300, short_name => 'AAC', long_name => 'ALL_ACCESS'), end;
   /

PL/SQL procedure successfully completed.

SQL> begin SA_COMPONENTS.CREATE_LEVEL(policy_name => 'REG_ACCESS', level_num =>200, short_name => 'RAC', long_name => 'REGION_ACCESS'), end;
  2  /

PL/SQL procedure successfully completed.

SQL> begin SA_COMPONENTS.CREATE_LEVEL(policy_name => 'REG_ACCESS', level_num =>100, short_name => 'OAC', long_name => 'OTHER_ACCESS'), end;
  2  /

PL/SQL procedure successfully completed.

SQL>
  7. Next we will create three data labels. These will be applied on the table EMPLOYEES_OLS_TBL, and will contain the rank and access level for every user. Create a data label for ALL_ACCESS, REG_ACCESS, and OTHER_ACCESS component levels, as follows:
    SQL> begin SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'REG_ACCESS', label_tag => 55, label_value => 'AAC', data_label => TRUE); end;
   /

PL/SQL procedure successfully completed.

SQL> begin SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'REG_ACCESS', label_tag => 44, label_value => 'RAC', data_label => TRUE); end;
    /

PL/SQL procedure successfully completed.

SQL> begin SA_LABEL_ADMIN.CREATE_LABEL(policy_name => 'REG_ACCESS', label_tag => 33, label_value => 'OAC', data_label => TRUE); end;
  2  /

PL/SQL procedure successfully completed.

SQL>
  8. Next apply the REG_ACCESS policy with the default NO_CONTROL option on the EMPLOYEES_OLS_TBL table, as follows:
    SQL> begin    SA_POLICY_ADMIN.APPLY_TABLE_POLICY(policy_name=>'REG_ACCESS',schema_name=>'HR',table_name=> 'EMPLOYEES_OLS_TBL',table_options=>null,label_function=>null,predicate=>null);  end;
   
PL/SQL procedure successfully completed.
  9. At this step we will add user authorizations. The highest authorization or the most powerful access level will be granted to the user SKING, as follows:
    SQL> begin SA_USER_ADMIN.SET_LEVELS(policy_name => 'REG_ACCESS', user_name => 'SKING', max_level => 'AAC', min_level => 'OAC', def_level => 'AAC', row_level =>'AAC'), end;
  2  /

PL/SQL procedure successfully completed.
SQL>
  10. The next highest access level will be granted to the users LDEHAAN and NKOCHHAR as follows:
    SQL> begin SA_USER_ADMIN.SET_LEVELS(policy_name => 'REG_ACCESS', user_name => 'LDEHAAN', max_level => 'RAC', min_level => 'OAC', def_level => 'RAC', row_level => 'RAC'), end;
  2  /

PL/SQL procedure successfully completed.

SQL> begin SA_USER_ADMIN.SET_LEVELS(policy_name => 'REG_ACCESS', user_name => 'NKOCHHAR', max_level => 'RAC', min_level => 'OAC', def_level => 'RAC', row_level=> 'RAC'), end;
  2  /

PL/SQL procedure successfully completed.
  11. The last and lowest access level will be granted to the user SMAVRIS as follows:
    SQL> begin SA_USER_ADMIN.SET_LEVELS(policy_name => 'REG_ACCESS', user_name => 'SMAVRIS', max_level => 'OAC', min_level => 'OAC', def_level => 'OAC', row_level => 'OAC'), end;
 /

PL/SQL procedure successfully completed.
  12. In this step, we will first label the rows belonging to SKING, then the rows belonging to LDEHAAN and NKOCHHAR, and finally all the remaining rows that belong to the user SMAVRIS. Connect as the user system and apply the data labels as follows:
    SQL> conn system
Enter password:
Connected.
SQL> update HR.EMPLOYEES_OLS_TBL set lb_column = char_to_label ('REG_ACCESS','AAC') where job_title in ('President'),

1 row updated.

SQL> update HR.EMPLOYEES_OLS_TBL set lb_column = char_to_label ('REG_ACCESS','RAC') where job_title in ('Administration Vice President'),

2 rows updated.
SQL> update HR.EMPLOYEES_OLS_TBL set lb_column = char_to_label ('REG_ACCESS','OAC') where job_title not in ('President','Administration Vice President'),

103 rows updated.

SQL> commit;

Commit complete.

SQL>
  13. The REG_ACCESS policy was created with the NO_CONTROL option, hence the labels are not enforced. In this step, we will alter the policy to enforce it for all queries by using the READ_CONTROL option, and set an optional policy enforcement called LABEL_DEFAULT. Connect as the user LBACSYS and execute the following procedures:
    SQL> conn LBACSYS
Enter password:
Connected.
SQL>begin SA_SYSDBA.ALTER_POLICY(policy_name => 'REG_ACCESS', default_options => 'READ_CONTROL,LABEL_DEFAULT,'), end;
PL/SQL procedure successfully completed.

SQL>
  14. The new settings of the REG_ACCESS policy are not inherited and applied automatically. Therefore once we alter the policy, we need to remove and reapply it in order to inherit the new policy enforcement settings from REG_ACCESS. First we must remove the old policy as follows:
    SQL> begin  SA_POLICY_ADMIN.REMOVE_TABLE_POLICY('REG_ACCESS','HR','EMPLOYEES_OLS_TBL',false);
  end;
SQL> /
PL/SQL procedure successfully completed.
  15. Reapply the REG_ACCESS policy by executing the following code:
    SQL>begin
SA_POLICY_ADMIN.APPLY_TABLE_POLICY(policy_name=>'REG_ACCESS',schema_name=>'HR',table_name=>'EMPLOYEES_OLS_TBL',table_options=>null,label_function=>null,predicate=>null);
end;
/

PL/SQL procedure successfully completed.

SQL>
  16. Connect as the user SMAVRIS and verify if the policy is correctly applied on the table REG_DATA_TBS. At the moment, we should not have access to the rows labeled by the SKING, LDEHAAN, and NKOCHHAR users, but we should have access to all others:
    SQL> conn SMAVRIS
Enter password:
Connected.
SQL> select salary,email from hr.EMPLOYEES_OLS_TBS where job_title in ('President','Administration Vice President'),

no rows selected

SQL>
  17. SMAVRIS has no access to the rows protected by higher, ranked labels. All other departments are accessible: 
    SQL> select distinct department_name from hr.employees_ols_tbl where job_title n
ot in ('President','Administration Vice President')
SQL> /

DEPARTMENT_NAME
------------------------------
Administration
Accounting
Human Resources
Public Relations
Purchasing
IT
Shipping
Sales
Finance
Marketing

10 rows selected.
  18. Since the user SKING has the highest place in the hierarchy, he/she is able to select from all categories. Connect as the user SKING and verify if he/she has access to all the rows from the table REG_DATA_TBS, as follows:
    SQL> conn SKING/SKING
Connected.
SQL> select distinct job_title from hr.EMPLOYEES_OLS_TBL;

JOB_TITLE
-----------------------------------
Public Relations Representative
Accounting Manager
Programmer
Purchasing Clerk
Sales Representative
Marketing Representative
Administration Vice President
Stock Manager
Administration Assistant
President
Finance Manager
Purchasing Manager
Human Resources Representative
Shipping Clerk
Accountant
Stock Clerk
Marketing Manager
Public Accountant
Sales Manager

19 rows selected.

SQL>

How it works...

A data label is composed of rank levels, compartments, and groups. The rank level for a data label is always mandatory, whereas for compartments and groups it is optional. The level is defined by using a numerical scale or tag, a short name, and a long name. A higher number indicates a higher place in the hierarchy and a lower number indicates a lower place in the hierarchy. In our example, the highest level is 300 and represents all access, the lowest is 100 and represents other access. The numeric value can be defined within the 0 to 9999 interval.

No matter how high the privileges a user has, he cannot access the data without authorization, unless he has special policy privileges assigned to him explicitly by administration packages or implicitly by using trusted stored units.

Access to the data protected by the data labels is ensured through authorizations made by comparing the row's label with a user's label and privilege. The data labels indicate row sensitivity and the user label indicates the user sensitivity present due to authorizations and any additional characteristics, such as compartments and groups. Data labels are discrete while user labels are inclusive. In our example, we defined the SKING user's user label with the maximum access level AAC or ALL_ACCESS and the minimum access level OAC or OTHER_ACCESS; in this way, the user SKING will have access to all the data and can read all the data labels defined in the table.

Label security is enforced using a label policy that is used and associated with labels, rules, and authorizations. In our example, we initially defined the REG_ACCESS policy with the NO_CONTROL option. This option will not add any initial enforcement on the table, and can be labeled. This makes sense for a table that has data, because if we apply enforcements from the start, the data is not accessible and may not be labeled. After we applied the corresponding labels, we redefined the REG_ACCESS policy with the READ_CONTROL and LABEL_DEFAULT options. The READ_CONTROL option will enforce the policy, and only authorized rows are accessible for the SELECT, INSERT, and UPDATE operations issued against the table. LABEL_DEFAULT uses the session default row label unless a user specifies a different label during an INSERT operation.

Practically, the access to data is decided by the user's label, which indicates the permitted access received through authorizations; the data or row's label, which indicates the sensibility of the information contained; a special policy privilege, which if granted can bypass label access control; and table policy settings.

There's more...

When a policy is created, a special label column will be added named the LB_COLUMN label column:

SQL> hr.employees_ols_tbl
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 STATE_PROVINCE                                     VARCHAR2(25)
 CITY                                      NOT NULL VARCHAR2(30)
 POSTAL_CODE                                        VARCHAR2(12)
 COUNTRY_NAME                                       VARCHAR2(40)
 REGION_ID                                          NUMBER
 LB_COLUMN                                          NUMBER(10)

SQL>

This column will contain a numeric equivalent of the character string value of a label, and all label operations must be performed on this column. 

SQL> select lb_column from hr.employees_ols_tbl;

 LB_COLUMN
----------
        31
        31
        33
………………………………………

There are two functions used for label column manipulation, namely LABEL_TO_CHAR and CHAR_TO_LABEL.

LABEL_TO_CHAR will retrieve the corresponding string value for a numerical tag:

SQL> select label_to_char(lb_column) label,job_title,lb_column from hr.employees_ols_tbl where job_title like '%Pre%';

LABEL   JOB_TITLE                            LB_COLUMN
AAC        President                                      55
RAC        Administration Vice President               44
RAC        Administration Vice President               44

CHAR_TO_LABEL will return the specified tag for a character value of the label. We used this function in our examples to apply the labels.

A label policy may have different enforcement options depending on the kind of operation that needs to be enforced.

Through a label policy, we may enforce a write operation for the UPDATE, INSERT, and DELETE statements by using WRITE_CONTROL, or separately enforce INSERT_CONTROL for INSERT statements, UPDATE_CONTROL for UPDATE statements, and DELETE_CONTROL for DELETE statements. All these controls, if active, are applicable for the rows where the user has write access. There is a special control called CHECK_CONTROL that ensures that the user will be able to read the data after he or she updates or inserts the data, and the data is in range of user-authorized label levels. ALL_CONTROL will enforce all the controls except READ_CONTROL and CHECK_CONTROL. If LABEL_UPDATE is enforced, then the user must have WRITEUP, WRITEDOWN, or WRITEACROSS privileges, a subject covered in the Using label policy privileges recipe in this chapter.

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

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