Table of contents
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):
This can also be seen as a 3D security model (copyright Oracle):
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):
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:
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:
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:
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
- Label Security Administrator’s Guide
- Oracle Label Security
- Oracle Label Security Part 1: Intro
- Oracle Label Security Part 2: Policy Creation
- Oracle Label Security (OLS)
manoj says:
hey thanks for your post. while trying these steps adding the policy I am not able to see any data with any user. also no unique lable is genertating.
please help me if i missed any steps.
Yannick Jaquier says:
Thanks for your comment. Try activate the audit, it will tell you if policy is mis-used or not used at all…