Oracle labels have a set of privileges, which can be used to bypass the current privileges in certain conditions, such as performing exports on label-protected tables or other operations that need to read or update the entire table. The same is true for other DML statements such as INSERT and DELETE.
In the previous recipe, we gave FULL policy privilege to the user HR. In this recipe, we will create a new user OLSAUTH, who will be granted a special privilege called PROFILE_ACCESS.
Usually special privileges should be granted to dedicated users. This can be done as follows:
- Connect as the user
system, create userOLSAUTHand grantcreate sessionandSELECTon the tableEMPLOYESS_OLS_TBL, as follows:SQL> conn system Enter password: Connected. SQL> create user OLSAUTH identified by OLSAUTH; User created. SQL> grant create session to employees_ols_tbl to OLSAUTH; SQL> grant select on hr.employees_ols_tbl to olsauth; Grant succeeded. SQL>
- Connect as the user
LBACSYSand grantPROFILE_ACCESStoOLSAUTHby executing the following code:begin LBACSYS.SA_USER_ADMIN.SET_USER_PRIVS(policy_name => 'REG_ACCESS', user_name => 'OLSAUTH', privileges => 'PROFILE_ACCESS,'), end;
- The use of
PROFILE_ACCESSis enforced through a procedure calledset_access_profile. Therefore, we will use thegrant execute on sa_admin.set_access_profileprocedure toOLSAUTHas follows:SQL> grant execute on sa_admin.set_access_profile to OLSAUTH; - Connect as the user
OLSAUTHand set the access profile of the userSKING:SQL> exec lbacsys.sa_session.set_access_profile('REG_ACCESS','SKING'), PL/SQL procedure successfully completed.
- For the moment, you will have the same visibility over the rows belonging to the user
SKING:SQL> select job_title,salary from hr.reg_data_tbs where job_title = 'President'; JOB_TITLE SALARY ----------------------------------- ---------- President 24000
Policy privileges are designed to bypass the conventional label security enforcements. The administration of special privileges is performed by using the SA_USER_ADMIN.SET_USER_PRIVS procedure. PROFILE_ACCESS can be used to escalate the label access for a session, to a higher one.
Usually you must find a secure mechanism to grant these special privileges, such as application context and logon triggers. The idea is to control these privileges within the application and not by Oracle. A recommended method is to use trusted stored units that will provide fine-grained access over the use of privileges, a subject covered in the next recipe.
Other security privileges are:
READ: With this privilege, a user will be allowed to read all the data protected by the policy. It can be granted to users who are performing administrative tasks, such as a data pump export of an entire schema, or specific tables protected by labels.FULL: With this security privilege, you will get full read and write access on a table protected by the policy.WRITEUP: With this security privilege granted, a user may raise the row label level up to the maximum authorized level. It can be used only if theLABEL_UPDATEpolicy enforcement is active.WRITEDOWN: This security privilege allows a user to set or lower the level within a row label to any level equal to or greater than the minimum authorized level. It can be used only if theLABEL_UPDATEpolicy enforcement is active.WRITEACROSS: This can be used in case we have compartments and groups defined. It allows you to change the compartments or groups of a row label. It can be used only if theLABEL_UPDATEpolicy enforcement is active.COMPACCESS: It allows access to rows by using a row's compartments independent of the row's groups.