Oracle Label Security (OLS) 12c installation and configuration

Preamble

Oracle Label Security (OLS) is not a new feature as it has been release in Oracle 9iR1. This feature is base on Virtual Private Database (VPD) technology that has even been released in Oracle 8i. OLS is a paid option of the database enterprise edition:

Oracle Virtual Private Database (VPD) is provided at no additional cost with the Enterprise Edition of Oracle Database. Oracle Label Security is an add-on security option for the Oracle Database Enterprise Edition.

When you want to protect rows of the same table you might do-it-yourself with a security column and you will check that the connected user has rights to see it with something like:

0 < (SELECT COUNT(*) FROM security_table sec WHERE sec.name = 'BO_username' AND fact_table.security__code LIKE sec.code)

The wildcard (all regions, all groups, …) can be simulated with underscore (_) that will perfectly work with LIKE SQL operator.

The drawback is an extended complexity in all SQL as the check will be added in WHERE clause. It is also quite easy to bypass the security if you are able to update the running SQL (forbid SQL editing in BO is a must in this case).

We have seen VPD in a previous post, that is generic term for Fine-Grained Access Control (FGAC), application context and global application context. VPD policies are made with PL/SQL while, as Oracle claims, Oracle Label Security is an out-of-the-box solution for row level security.

The high level picture of OLS model is this Oracle picture (copyright Oracle):

ols01
ols01

This can also be seen as a 3D security model (copyright Oracle):

ols02
ols02

Testing of this post has been done using Oracle Enterprise edition 12cR1 (12.1.0.2) running on Oracle Linux 7.2 64 bits running in a VirtualBox quest. The Cloud Control 13cR1 images have been done using the Oracle provided VirtualBox guest.

Oracle Label Security installation

Check if OLS is active on your database:

SQL> SELECT VALUE FROM v$option WHERE parameter = 'Oracle Label Security';
 
VALUE
----------------------------------------------------------------
FALSE

If not you can activate it using DataBase Configuration Assistant (DBCA):

ols03
ols03

Or command line with:

SQL> EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
BEGIN LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS; END;
 
*
ERROR AT line 1:
ORA-12459: Oracle Label Security NOT configured
ORA-06512: AT "LBACSYS.OLS_ENFORCEMENT", line 3
ORA-06512: AT "LBACSYS.OLS_ENFORCEMENT", line 25
ORA-06512: AT line 1
 
 
SQL> !oerr ORA 12459
12459, 00000, "Oracle Label Security not configured"
// *Cause:  An administrative operation was attempted without configuring
//          Oracle Label Security.
// *Action: Consult the Oracle Label Security documentation FOR information
//          ON how TO configure Oracle Label Security.

We need to configure OLS before enabling it (database must be restarted):

SQL> SELECT status FROM dba_ols_status WHERE name = 'OLS_CONFIGURE_STATUS';
 
STATU
-----
FALSE
 
SQL> EXEC LBACSYS.CONFIGURE_OLS;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> shutdown IMMEDIATE;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total SYSTEM Global Area  838860800 bytes
Fixed SIZE                  2929936 bytes
Variable SIZE             603982576 bytes
DATABASE Buffers          226492416 bytes
Redo Buffers                5455872 bytes
DATABASE mounted.
DATABASE opened.

Check the status:

SQL> SELECT status FROM dba_ols_status WHERE name = 'OLS_CONFIGURE_STATUS';
 
STATU
-----
TRUE
 
SQL> SELECT VALUE FROM v$option WHERE parameter = 'Oracle Label Security';
 
VALUE
----------------------------------------------------------------
TRUE

Unlock OLS database user (LBACSYS) and change its password:

SQL> ALTER USER lbacsys IDENTIFIED BY "secure_password" account unlock;
 
USER altered.

Oracle Label Security test data model

The schema owning the data model is an OS authenticated account, I grant it the LBAC_DBA role as this account will administer OLS directly:

SQL> CREATE USER app IDENTIFIED externally;
 
USER created.
 
SQL> GRANT CONNECT,RESOURCE TO app;
 
GRANT succeeded.
 
SQL> GRANT unlimited TABLESPACE TO app;
 
GRANT succeeded.
 
SQL> GRANT lbac_dba TO app;
 
GRANT succeeded.

The data model is made of three table. One fact table and two dimension table: SALES, PRODUT_GROUP and REGION. The creation script is self explaining I hope (no indexes except the one for primary keys as the post is not performance related):

SET pages 20
CREATE TABLE product_group(
  code VARCHAR2(4) PRIMARY KEY,
  descr VARCHAR2(20))
TABLESPACE users;
 
INSERT INTO product_group VALUES('0001','IoT');
INSERT INTO product_group VALUES('0002','Mems');
INSERT INTO product_group VALUES('0003','Smart Driving');
 
CREATE TABLE region(
  code VARCHAR2(4) PRIMARY KEY,
  descr VARCHAR2(30))
TABLESPACE users;
 
SET define '#'
INSERT INTO region VALUES('0001','America');
INSERT INTO region VALUES('0002','Asia Pacific');
INSERT INTO region VALUES('0003','Japan & Korea');
INSERT INTO region VALUES('0004','Greater China');
INSERT INTO region VALUES('0005','Europe Middle East & Africa');
 
CREATE TABLE sales(
  product_group__code VARCHAR2(4),
  region__code VARCHAR2(4),
  val NUMBER,
	CONSTRAINT product_group__code_fk foreign KEY (product_group__code) references product_group(code),
	CONSTRAINT region__code_fk foreign KEY (region__code) references region(code))
TABLESPACE users;
 
INSERT INTO sales VALUES('0001','0005',1500);
INSERT INTO sales VALUES('0002','0005',10000);
INSERT INTO sales VALUES('0003','0005',500);
INSERT INTO sales VALUES('0001','0001',5000);
INSERT INTO sales VALUES('0002','0001',7500);
INSERT INTO sales VALUES('0003','0001',400);
INSERT INTO sales VALUES('0001','0003',4000);
INSERT INTO sales VALUES('0002','0003',10400);
INSERT INTO sales VALUES('0003','0003',400);
INSERT INTO sales VALUES('0001','0004',3000);
INSERT INTO sales VALUES('0002','0004',5000);
INSERT INTO sales VALUES('0003','0004',200);
COMMIT;

You can get sales by product group and region using this below classical query:

SQL> SELECT app.product_group.descr, app.region.descr, SUM(app.sales.val)
FROM app.product_group, app.region, app.sales
WHERE app.sales.product_group__code=app.product_group.code
AND app.sales.region__code=app.region.code
GROUP BY app.product_group.descr, app.region.descr
ORDER BY 1,2,3;
 
DESCR                DESCR                          SUM(SALES.VAL)
-------------------- ------------------------------ --------------
IoT                  America                                  5000
IoT                  Europe Middle East & Africa              1500
IoT                  Greater China                            3000
IoT                  Japan & Korea                            4000
Mems                 America                                  7500
Mems                 Europe Middle East & Africa             10000
Mems                 Greater China                            5000
Mems                 Japan & Korea                           10400
Smart Driving        America                                   400
Smart Driving        Europe Middle East & Africa               500
Smart Driving        Greater China                             200
Smart Driving        Japan & Korea                             400
 
12 ROWS selected.

I also create a classical password authenticated account that would be used in your applciation:

SQL> CREATE USER app_read IDENTIFIED BY "secure_password";
 
USER created.
 
SQL> GRANT CONNECT TO app_read;
 
GRANT succeeded.
 
SQL> GRANT SELECT ON app.sales TO app_read;
 
GRANT succeeded.
 
SQL> GRANT SELECT ON app.region TO app_read;
 
GRANT succeeded.
 
SQL> GRANT SELECT ON app.product_group TO app_read;
 
GRANT succeeded.

I also grant few execute privileges on OLS packages to APP account:

SQL> GRANT EXECUTE ON sa_policy_admin TO app;
 
GRANT succeeded.
 
SQL> GRANT EXECUTE ON to_lbac_data_label TO app;
 
GRANT succeeded.

Oracle Label Security setup

Policy creation

As LBACSYS user I create policy sales_ols_pol using all enforcement options. I choose to call the OLS column ols_col and to keep it visible:

SQL> EXEC sa_sysdba.create_policy(policy_name => 'sales_ols_pol', column_name => 'ols_col', default_options => 'all_control');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col policy_options FOR a50 word_wrapped
SQL> col column_name FOR a10
SQL> SELECT * FROM dba_sa_policies;
 
POLICY_NAME                    COLUMN_NAM STATUS   POLICY_OPTIONS                                     POLIC
------------------------------ ---------- -------- -------------------------------------------------- -----
SALES_OLS_POL                  OLS_COL    ENABLED  READ_CONTROL, INSERT_CONTROL, UPDATE_CONTROL,      FALSE
                                                   DELETE_CONTROL, LABEL_DEFAULT, LABEL_UPDATE,
                                                   CHECK_CONTROL

Remark
HIDE option hide the OLS column in protected tables (use ‘all_control,hide’).

To allow APP account to manage its own Label Security I grant to it the policy_name_dba role:

SQL> GRANT sales_ols_pol_dba TO app;
 
GRANT succeeded.

Levels creation

I define two levels, a public and a confidential one. Remember that level_num value define the sensitivity ranking:

SQL> EXEC sa_components.create_level(policy_name => 'sales_ols_pol', level_num => 10, short_name => 'P', long_name => 'PUBLIC');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_components.create_level(policy_name => 'sales_ols_pol', level_num => 20, short_name => 'C', long_name => 'CONFIDENTIAL');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col long_name FOR a20
SQL> SELECT * FROM dba_sa_levels ORDER BY level_num;
 
POLICY_NAME                     LEVEL_NUM SHORT_NAME                     LONG_NAME
------------------------------ ---------- ------------------------------ --------------------
SALES_OLS_POL                          10 P                              PUBLIC
SALES_OLS_POL                          20 C                              CONFIDENTIAL

Compartments creation

I define three compartments that are my product groups. Comp_num parameter determines the order in which compartments are listed in labels. Names are not case sensitive, will be inserted in uppercase:

SQL> EXEC sa_components.create_compartment(policy_name => 'sales_ols_pol',comp_num => '10', short_name => 'IOT', long_name => 'IOT');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_components.create_compartment(policy_name => 'sales_ols_pol',comp_num => '20', short_name => 'MEMS', long_name => 'MEMS');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_components.create_compartment(policy_name => 'sales_ols_pol',comp_num => '30', short_name => 'SD', long_name => 'SMART DRIVING');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM dba_sa_compartments ORDER BY comp_num;
 
POLICY_NAME                      COMP_NUM SHORT_NAME                     LONG_NAME
------------------------------ ---------- ------------------------------ --------------------
SALES_OLS_POL                          10 IOT                            IOT
SALES_OLS_POL                          20 MEMS                           MEMS
SALES_OLS_POL                          30 SD                             SMART DRIVING

Groups creation

I define three main groups and two sub-group of a main groups so five in total. Names are not case sensitive, will be inserted in uppercase. Special character like & not allowed so replacing with words:

SQL> EXEC sa_components.create_group(policy_name => 'sales_ols_pol', group_num => 1000, short_name => 'USA', long_name => 'AMERICA');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_components.create_group(policy_name => 'sales_ols_pol', group_num => 2000, short_name => 'AP', long_name => 'ASIA PACIFIC');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_components.create_group(policy_name => 'sales_ols_pol', group_num => 2010, short_name => 'JK', long_name => 'JAPAN AND KOREA', parent_name=> 'AP');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_components.create_group(policy_name => 'sales_ols_pol', group_num => 2020, short_name => 'GC', long_name => 'GREATER CHINA', parent_name=> 'AP');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_components.create_group(policy_name => 'sales_ols_pol', group_num => 3000, short_name => 'EMEA', long_name => 'EUROPE MIDDLE EAST AND AFRICA');
 
PL/SQL PROCEDURE successfully completed.
SQL> col long_name FOR a30
SQL> SELECT group_num,short_name,long_name,parent_num,parent_name FROM dba_sa_groups ORDER BY group_num;
 
 GROUP_NUM SHORT_NAME                     LONG_NAME                      PARENT_NUM PARENT_NAME
---------- ------------------------------ ------------------------------ ---------- ------------------------------
      1000 USA                            AMERICA
      2000 AP                             ASIA PACIFIC
      2010 JK                             JAPAN AND KOREA                      2000 AP
      2020 GC                             GREATER CHINA                        2000 AP
      3000 EMEA                           EUROPE MIDDLE EAST AND AFRICA

You can also view created hierarchy using:

SQL> col group_name FOR a40
SQL> SELECT * FROM DBA_SA_GROUP_HIERARCHY;
 
POLICY_NAME                    HIERARCHY_LEVEL GROUP_NAME
------------------------------ --------------- ----------------------------------------
SALES_OLS_POL                                1   USA - AMERICA
SALES_OLS_POL                                1   AP - ASIA PACIFIC
SALES_OLS_POL                                2     JK - JAPAN AND KOREA
SALES_OLS_POL                                2     GC - GREATER CHINA
SALES_OLS_POL                                1   EMEA - EUROPE MIDDLE EAST AND AFRICA

Label function

This is the most tricky part. You need to write what Oracle call a label function. This function will compute a label for the row based on the values of inserted columns. The returned label must return short name of levels, compartments and groups. Here below Iot product group, as next future business, is defined as confidential figures. The non-sexy case could have been replaced by select into. Refer to Oracle documentation for another example:

CREATE OR REPLACE FUNCTION gen_sales_label(product_group__code VARCHAR2, region__code VARCHAR2)
RETURN lbacsys.lbac_label
AS
  i_label VARCHAR2(80);
BEGIN
  /************* determine level *************/
  IF product_group__code='0001' THEN --IOT
    i_label := 'C:';
  ELSE
    i_label := 'P:';
  END IF;
 
  /************* determine compartment *************/
  CASE product_group__code
	  WHEN '0001' THEN i_label := i_label || 'IOT:';
	  WHEN '0002' THEN i_label := i_label || 'MEMS:';
	  WHEN '0003' THEN i_label := i_label || 'SD:';
  END CASE;
 
  /************* determine groups *************/
	CASE region__code
	  WHEN '0001' THEN i_label := i_label || 'USA';
	  WHEN '0002' THEN i_label := i_label || 'AP';
	  WHEN '0003' THEN i_label := i_label || 'JK';
	  WHEN '0004' THEN i_label := i_label || 'GC';
	  WHEN '0005' THEN i_label := i_label || 'EMEA';
  END CASE;
 
  RETURN to_lbac_data_label('sales_ols_pol',i_label);
END;
/

As APP user I apply the OLS policy to my sales table using my label function:

SQL> EXEC sa_policy_admin.apply_table_policy(policy_name => 'sales_ols_pol', schema_name => 'app', table_name => 'sales', -
table_options => 'all_control', label_function => 'app.gen_sales_label(:new.product_group__code,:new.region__code)');
 
PL/SQL PROCEDURE successfully completed.

You can notice it adds a new column to your table (if not using HIDE option):

SQL> DESC sales
 Name                                                                                NULL?    TYPE
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 PRODUCT_GROUP__CODE                                                                          VARCHAR2(4)
 REGION__CODE                                                                                 VARCHAR2(4)
 VAL                                                                                          NUMBER
 OLS_COL                                                                                      NUMBER(10)

To remove the policy you can use:

SQL> EXEC sa_policy_admin.remove_table_policy(policy_name => 'sales_ols_pol', schema_name => 'app', table_name => 'sales', drop_column=> TRUE);
 
PL/SQL PROCEDURE successfully completed.

If you get ORA-12446 error message:

EXEC sa_policy_admin.apply_table_policy(policy_name => 'sales_ols_pol', schema_name => 'app', table_name => 'sales', table_options  => 'all_control', -
> label_function => 'app.gen_sales_label(:new.product_group__code,:new.region__code)');
BEGIN sa_policy_admin.apply_table_policy(policy_name => 'sales_ols_pol', schema_name => 'app', table_name => 'sales', table_options  => 'all_control',
label_function => 'app.gen_sales_label(:new.product_group__code,:new.region__code)'); END;
 
*
ERROR AT line 1:
ORA-12446: Insufficient authorization FOR administration OF policy
sales_ols_pol
ORA-06512: AT "LBACSYS.LBAC_POLICY_ADMIN", line 385
ORA-06512: AT line 1

Then grant sales_ols_pol_dba role to app or execute the apply table policy with LBACSYS account.

If you get ORA-12433:

EXEC sa_policy_admin.apply_table_policy(policy_name => 'sales_ols_pol', schema_name => 'app', table_name => 'sales', table_options  => 'all_control', -
> label_function => 'app.gen_sales_label(:new.product_group__code,:new.region__code)');
BEGIN sa_policy_admin.apply_table_policy(policy_name => 'sales_ols_pol', schema_name => 'app', table_name => 'sales', table_options  => 'all_control',
label_function => 'app.gen_sales_label(:new.product_group__code,:new.region__code)'); END;
 
*
ERROR AT line 1:
ORA-12433: CREATE TRIGGER failed, policy NOT applied
ORA-06512: AT "LBACSYS.LBAC_POLICY_ADMIN", line 385
ORA-06512: AT line 1

Then grant execute on app.gen_sales_label to LBACSYS. Clearly the error message is not at all self-explaining !

You could have done all this using Cloud Control 13cR1:

ols04
ols04

Oracle Label Security testing

At that stage APP and APP_READ user are not able to see anymore figures in SALES table because the OLS_COL is empty and also because we have not defined users’ security:

SQL> SELECT * FROM sales;
 
no ROWS selected

Update the OLS_COL column simulating a full update of your table with something like:

SQL> UPDATE sales SET product_group__code=product_group__code;
 
12 ROWS updated.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM sales;
 
PROD REGI        VAL    OLS_COL
---- ---- ---------- ----------
0001 0005       1500 1000000061
0002 0005      10000 1000000062
0003 0005        500 1000000063
0001 0001       5000 1000000064
0002 0001       7500 1000000065
0003 0001        400 1000000066
0001 0003       4000 1000000067
0002 0003      10400 1000000068
0003 0003        400 1000000069
0001 0004       3000 1000000070
0002 0004       5000 1000000071
0003 0004        200 1000000072
 
12 ROWS selected.

As LBACSYS we allow APP to bypass OLS and APP_READ to change its label and privileges to another user (by default the account still see nothing):

SQL> EXEC sa_user_admin.set_user_privs('sales_ols_pol','app','full');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_user_admin.set_user_privs('sales_ols_pol','app_read','profile_access');
 
PL/SQL PROCEDURE successfully completed.

Same as in real life each applicative users will not connect to the database with their own Oracle account. Either they are authenticated with an LDAP account or with an applicative security. So what we define is a set of accounts not linked to any database user. Those accounts name will have obvious name to ease understanding of what I plan to test:

Account Privileges
sales_p_ww Worldwide access to public information
sales_c_ww Worldwide access to public and confidential information
sales_p_jk Japan & Korea access to public information
sales_p_ap Asia Pacific access to public information
sales_c_ap Asia Pacific access to public and confidential information

With Cloud Control 13cR1 the user list is:

ols05
ols05

To simulate those application accounts we will use sa_session.set_access_profile procedure to set APP_READ behaving like our applicative users but I could have used a context as we have already seen with something like SYS_CONTEXT(‘userenv’,’CLIENT_IDENTIFIER’).

First test is an account with public worldwide access, IoT information must not be display:

SQL> EXEC sa_user_admin.set_levels(policy_name => 'sales_ols_pol', user_name => 'sales_p_ww', max_level => 'P');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_user_admin.set_compartments(policy_name => 'sales_ols_pol', user_name => 'sales_p_ww', read_comps => 'MEMS,SD');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_user_admin.set_groups(policy_name => 'sales_ols_pol', user_name => 'sales_p_ww', read_groups => 'EMEA,AP,USA');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_session.set_access_profile(policy_name => 'sales_ols_pol', user_name => 'sales_p_ww');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT sa_session.sa_user_name(policy_name => 'sales_ols_pol') FROM dual;
 
SA_SESSION.SA_USER_NAME(POLICY_NAME=>'SALES_OLS_POL')
--------------------------------------------------------------------------------
SALES_P_WW
 
SQL> SELECT sa_session.label(policy_name => 'sales_ols_pol') FROM dual;
 
SA_SESSION.LABEL(POLICY_NAME=>'SALES_OLS_POL')
--------------------------------------------------------------------------------
P:MEMS,SD:USA,AP,JK,GC,EMEA
 
SQL> SELECT sa_session.comp_read(policy_name => 'sales_ols_pol') FROM dual;
 
SA_SESSION.COMP_READ(POLICY_NAME=>'SALES_OLS_POL')
--------------------------------------------------------------------------------
MEMS,SD
 
SQL> SELECT app.product_group.descr, app.region.descr, SUM(app.sales.val)
FROM app.product_group, app.region, app.sales
WHERE app.sales.product_group__code=app.product_group.code
AND app.sales.region__code=app.region.code
GROUP BY app.product_group.descr, app.region.descr
ORDER BY 1,2,3;
 
DESCR                DESCR                          SUM(APP.SALES.VAL)
-------------------- ------------------------------ ------------------
Mems                 America                                      7500
Mems                 Europe Middle East & Africa                 10000
Mems                 Greater China                                5000
Mems                 Japan & Korea                               10400
Smart Driving        America                                       400
Smart Driving        Europe Middle East & Africa                   500
Smart Driving        Greater China                                 200
Smart Driving        Japan & Korea                                 400
 
8 ROWS selected.

Second test is an account with worldwide all level access (full sales table in other words):

SQL> EXEC sa_user_admin.set_levels(policy_name => 'sales_ols_pol', user_name => 'sales_c_ww', max_level => 'C');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_user_admin.set_compartments(policy_name => 'sales_ols_pol', user_name => 'sales_c_ww', read_comps => 'IOT,MEMS,SD');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_user_admin.set_groups(policy_name => 'sales_ols_pol', user_name => 'sales_c_ww', read_groups => 'EMEA,AP,USA');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_session.set_access_profile(policy_name => 'sales_ols_pol', user_name => 'sales_c_ww');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT app.product_group.descr, app.region.descr, SUM(app.sales.val)
FROM app.product_group, app.region, app.sales
WHERE app.sales.product_group__code=app.product_group.code
AND app.sales.region__code=app.region.code
GROUP BY app.product_group.descr, app.region.descr
ORDER BY 1,2,3;
 
DESCR                DESCR                          SUM(APP.SALES.VAL)
-------------------- ------------------------------ ------------------
IoT                  America                                      5000
IoT                  Europe Middle East & Africa                  1500
IoT                  Greater China                                3000
IoT                  Japan & Korea                                4000
Mems                 America                                      7500
Mems                 Europe Middle East & Africa                 10000
Mems                 Greater China                                5000
Mems                 Japan & Korea                               10400
Smart Driving        America                                       400
Smart Driving        Europe Middle East & Africa                   500
Smart Driving        Greater China                                 200
Smart Driving        Japan & Korea                                 400
 
12 ROWS selected.

Third test is an account with Japan and Korea access and only public information. You can also define directly the label of the user using sa_user_admin.set_user_labels:

SQL> EXEC sa_user_admin.set_user_labels(policy_name => 'sales_ols_pol', user_name => 'sales_p_jk', max_read_label => 'P:MEMS,SD:JK');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT app.product_group.descr, app.region.descr, SUM(app.sales.val)
FROM app.product_group, app.region, app.sales
WHERE app.sales.product_group__code=app.product_group.code
AND app.sales.region__code=app.region.code
GROUP BY app.product_group.descr, app.region.descr
ORDER BY 1,2,3;
 
DESCR                DESCR                          SUM(APP.SALES.VAL)
-------------------- ------------------------------ ------------------
Mems                 Japan & Korea                               10400
Smart Driving        Japan & Korea                                 400

Fourth test is an account with Asia Pacific access and only public information. Aim here is to see if two sub-groups are well displayed:

SQL> EXEC sa_user_admin.set_user_labels(policy_name => 'sales_ols_pol', user_name => 'sales_p_ap', max_read_label => 'P:MEMS,SD:AP');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT app.product_group.descr, app.region.descr, SUM(app.sales.val)
FROM app.product_group, app.region, app.sales
WHERE app.sales.product_group__code=app.product_group.code
AND app.sales.region__code=app.region.code
GROUP BY app.product_group.descr, app.region.descr
ORDER BY 1,2,3;
 
DESCR                DESCR                          SUM(APP.SALES.VAL)
-------------------- ------------------------------ ------------------
Mems                 Greater China                                5000
Mems                 Japan & Korea                               10400
Smart Driving        Greater China                                 200
Smart Driving        Japan & Korea                                 400

Fifth test is an account with Asia Pacific access and all levels. Aim is again to see if two sub-groups are well displayed:

SQL> EXEC sa_user_admin.set_user_labels(policy_name => 'sales_ols_pol', user_name => 'sales_c_ap', max_read_label => 'C:IOT,MEMS,SD:AP');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT /* Yannick */ app.product_group.descr, app.region.descr, SUM(app.sales.val)
FROM app.product_group, app.region, app.sales
WHERE app.sales.product_group__code=app.product_group.code
AND app.sales.region__code=app.region.code
GROUP BY app.product_group.descr, app.region.descr
ORDER BY 1,2,3;
 
DESCR                DESCR                          SUM(APP.SALES.VAL)
-------------------- ------------------------------ ------------------
IoT                  Greater China                                3000
IoT                  Japan & Korea                                4000
Mems                 Greater China                                5000
Mems                 Japan & Korea                               10400
Smart Driving        Greater China                                 200
Smart Driving        Japan & Korea                                 400
 
6 ROWS selected.

I also wanted to see how Oracle is handling those query and if any transformations are applied to the query before executing them. We can see than SQL queries remain unchanged and only a filter is applied. See Predicate Information number 9 in below explain plan extract:

SQL_ID  8qt810d4tkg1x, child NUMBER 0
-------------------------------------
SELECT /* Yannick */ app.product_group.descr, app.region.descr, 
SUM(app.sales.val) FROM app.product_group, app.region, app.sales WHERE 
app.sales.product_group__code=app.product_group.code AND 
app.sales.region__code=app.region.code GROUP BY 
app.product_group.descr, app.region.descr ORDER BY 1,2,3
 
PLAN hash VALUE: 4206122969
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | E-ROWS |E-Bytes| COST (%CPU)| E-TIME   | A-ROWS |   A-TIME   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |      1 |        |       |    11 (100)|          |     12 |00:00:00.01 |      16 |      6 |       |       |          |
|   1 |  SORT ORDER BY                   |               |      1 |     11 |   583 |    11  (28)| 00:00:01 |     12 |00:00:00.01 |      16 |      6 |  2048 |  2048 |     1/0/0|
|   2 |   HASH GROUP BY                  |               |      1 |     11 |   583 |    11  (28)| 00:00:01 |     12 |00:00:00.01 |      16 |      6 |   930K|   930K|     1/0/0|
|*  3 |    FILTER                        |               |      1 |        |       |            |          |     12 |00:00:00.01 |      16 |      6 |       |       |          |
|*  4 |     HASH JOIN                    |               |      1 |     12 |   636 |     9  (12)| 00:00:01 |     12 |00:00:00.01 |      16 |      6 |  1393K|  1393K|     1/0/0|
|   5 |      MERGE JOIN                  |               |      1 |     12 |   396 |     6  (17)| 00:00:01 |     12 |00:00:00.01 |       9 |      4 |       |       |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| PRODUCT_GROUP |      1 |      3 |    39 |     2   (0)| 00:00:01 |      3 |00:00:00.01 |       2 |      1 |       |       |          |
|   7 |        INDEX FULL SCAN           | SYS_C0010354  |      1 |      3 |       |     1   (0)| 00:00:01 |      3 |00:00:00.01 |       1 |      1 |       |       |          |
|*  8 |       SORT JOIN                  |               |      3 |     12 |   240 |     4  (25)| 00:00:01 |     12 |00:00:00.01 |       7 |      3 |  2048 |  2048 |     1/0/0|
|*  9 |        TABLE ACCESS FULL         | SALES         |      1 |     12 |   240 |     3   (0)| 00:00:01 |     12 |00:00:00.01 |       7 |      3 |       |       |          |
|  10 |      TABLE ACCESS FULL           | REGION        |      1 |      5 |   100 |     3   (0)| 00:00:01 |      5 |00:00:00.01 |       7 |      2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   6 - SEL$F5BB74E1 / PRODUCT_GROUP@SEL$1
   7 - SEL$F5BB74E1 / PRODUCT_GROUP@SEL$1
   9 - SEL$F5BB74E1 / SALES@SEL$2
  10 - SEL$F5BB74E1 / REGION@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - filter(TO_NUMBER(SYS_CONTEXT('LBAC$0_LAB','LBAC$MAXLABEL'))>=TO_NUMBER(SYS_CONTEXT('LBAC$0_LAB','LBAC$MINLABEL')))
   4 - ACCESS("REGION__CODE"="REGION"."CODE")
   8 - ACCESS("PRODUCT_GROUP__CODE"="PRODUCT_GROUP"."CODE")
       filter("PRODUCT_GROUP__CODE"="PRODUCT_GROUP"."CODE")
   9 - filter(("OLS_COL">=TO_NUMBER(SYS_CONTEXT('LBAC$0_LAB','LBAC$MINLABEL')) AND "OLS_COL"<=TO_NUMBER(SYS_CONTEXT('LBAC$0_LAB','LBAC$MAXLABEL')) AND 
              TO_NUMBER(SYS_CONTEXT('LBAC$LABELS',TO_CHAR("OLS_COL")))>=0))
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - (#keys=3) "PRODUCT_GROUP"."DESCR"[VARCHAR2,20], "REGION"."DESCR"[VARCHAR2,30], SUM("VAL")[22]
   2 - "PRODUCT_GROUP"."DESCR"[VARCHAR2,20], "REGION"."DESCR"[VARCHAR2,30], SUM("VAL")[22]
   3 - "PRODUCT_GROUP"."DESCR"[VARCHAR2,20], "REGION"."DESCR"[VARCHAR2,30], "VAL"[NUMBER,22], "REGION"."DESCR"[VARCHAR2,30]
   4 - (#keys=1) "PRODUCT_GROUP"."DESCR"[VARCHAR2,20], "REGION"."DESCR"[VARCHAR2,30], "VAL"[NUMBER,22], "REGION"."DESCR"[VARCHAR2,30]
   5 - "PRODUCT_GROUP"."DESCR"[VARCHAR2,20], "REGION__CODE"[VARCHAR2,4], "VAL"[NUMBER,22]
   6 - "PRODUCT_GROUP"."CODE"[VARCHAR2,4], "PRODUCT_GROUP"."DESCR"[VARCHAR2,20]
   7 - "PRODUCT_GROUP".ROWID[ROWID,10], "PRODUCT_GROUP"."CODE"[VARCHAR2,4]
   8 - (#keys=1) "PRODUCT_GROUP__CODE"[VARCHAR2,4], "REGION__CODE"[VARCHAR2,4], "VAL"[NUMBER,22]
   9 - "PRODUCT_GROUP__CODE"[VARCHAR2,4], "REGION__CODE"[VARCHAR2,4], "VAL"[NUMBER,22]
  10 - "REGION"."CODE"[VARCHAR2,4], "REGION"."DESCR"[VARCHAR2,30]
 
Note
-----
   - this IS an adaptive PLAN

Oracle Label Security auditing

Oracle Label Security policies can be audited using SA_AUDIT_ADMIN package. First ensure audit_trail parameter is not set to none value:

SQL> show parameter audit_trail
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

I activate all possible audit for my policy by access:

SQL> EXEC sa_audit_admin.AUDIT(policy_name => 'sales_ols_pol', audit_type => 'BY ACCESS');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC sa_audit_admin.AUDIT(policy_name => 'sales_ols_pol', audit_option => 'PRIVILEGES', audit_type => 'BY ACCESS');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col user_name FOR a30
SQL> SELECT * FROM dba_sa_audit_options;
 
POLICY_NAME                    USER_NAME                      APY REM SET PRV
------------------------------ ------------------------------ --- --- --- ---
SALES_OLS_POL                  ALL_USERS                      A/A A/A A/A A/A

I also activate policy label recording with:

SQL> EXEC sa_audit_admin.audit_label(policy_name => 'sales_ols_pol');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET serveroutput ON
BEGIN
  IF sa_audit_admin.audit_label_enabled('sales_ols_pol')
    THEN dbms_output.put_line('OLS sales_ols_pol labels are being audited.');
  ELSE
    dbms_output.put_line('OLS sales_ols_pol labels not being audited.');
  END IF;
END;
/
OLS sales_ols_pol labels are being audited.
 
PL/SQL PROCEDURE successfully completed.

I also create the dedicated view to display audit records:

SQL> EXEC sa_audit_admin.create_view(policy_name => 'sales_ols_pol');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> DESC dba_sales_ols_pol_audit_trail
 Name                                                                                NULL?    TYPE
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 USERNAME                                                                                     VARCHAR2(128)
 USERHOST                                                                                     VARCHAR2(128)
 TERMINAL                                                                                     VARCHAR2(255)
 TIMESTAMP                                                                                    DATE
 OWNER                                                                                        VARCHAR2(128)
 OBJ_NAME                                                                                     VARCHAR2(128)
 ACTION                                                                              NOT NULL NUMBER
 ACTION_NAME                                                                                  VARCHAR2(47)
 COMMENT_TEXT                                                                                 VARCHAR2(4000)
 SESSIONID                                                                           NOT NULL NUMBER
 ENTRYID                                                                             NOT NULL NUMBER
 STATEMENTID                                                                         NOT NULL NUMBER
 RETURNCODE                                                                          NOT NULL NUMBER
 EXTENDED_TIMESTAMP                                                                           TIMESTAMP(6) WITH TIME ZONE
 OLS_COL                                                                                      VARCHAR2(4000)

If I select in SALES table with APP account I get:

SQL> col comment_text FOR a40
SQL> col username FOR a10
SQL> SELECT username,TIMESTAMP,action_name,comment_text FROM lbacsys.dba_sales_ols_pol_audit_trail;
 
USERNAME   TIMESTAMP ACTION_NAME                                     COMMENT_TEXT
---------- --------- ----------------------------------------------- ----------------------------------------
APP        04-NOV-16 PRIVILEGED ACTION                               SALES_OLS_POL: BYPASSALL PRIVILEGE SET

But if I select with APP_READ using SA_SESSION.SET_ACCESS_PROFILE package then it does not generate any audit record, so a bit disappointed…

Auditing can also be managed with Cloud Control:

ols06
ols06

Oracle Label Security cleaning

Cleaning everything simply means dropping the policy with LBACSYS account:

SQL> EXEC sa_sysdba.drop_policy(policy_name => 'sales_ols_pol', drop_column => TRUE);
 
PL/SQL PROCEDURE successfully completed.

References

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>