Oracle Label Security (OLS) is an extension of the Virtual Private Database (VPD) technology introduced in Oracle 8i. OLS allows access control down to individual rows based on attached labels. Similar functionality can be reproduced using Fine Grained Access Control (FGAC) but OLS provides an out-of-the-box solution to row-level security. In this article I'll present a simple example of the configuration of OLS.
Related articles.
If the Oracle Label Security option was not loaded onto your server during the software installtion then install it as follows.
If you are trying to use label security in 11gR2 on Linux, you will probably have to relink Oracle with the following command to see the option in the DBCA.
$ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk lbac_on ioracle
Once Oracle Label Security option has been installed on the server it can be added to an instance as follows.
With the OLS option added to the instance we are able to start using it. First we create a test user to hold the OLS sample schema.
CONN / AS SYSDBA CREATE USER ols_test IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO ols_test;
Next we grant the appropriate authorisation on the OLS packages to the test user.
ALTER USER lbacsys IDENTIFIED BY lbacsys ACCOUNT UNLOCK; CONN lbacsys/lbacsys GRANT EXECUTE ON sa_components TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_user_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_user_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_label_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_policy_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_audit_admin TO ols_test WITH GRANT OPTION; GRANT LBAC_DBA TO ols_test; GRANT EXECUTE ON sa_sysdba TO ols_test; GRANT EXECUTE ON to_lbac_data_label TO ols_test;
Next we create a policy and specify the name of the column where the label will be stored.
CONN ols_test/password BEGIN SA_SYSDBA.CREATE_POLICY( policy_name => 'region_policy', column_name => 'region_label'); END; / GRANT region_policy_DBA TO ols_test;
Next we create the components of the labels for the policy.
EXECUTE SA_COMPONENTS.CREATE_LEVEL('region_policy',20,'L1','Level 1'); EXECUTE SA_COMPONENTS.CREATE_LEVEL('region_policy',40,'L2','Level 2'); EXECUTE SA_COMPONENTS.CREATE_LEVEL('region_policy',60,'L3','Level 3'); EXECUTE SA_COMPONENTS.CREATE_COMPARTMENT('region_policy',100,'M','MANAGEMENT'); EXECUTE SA_COMPONENTS.CREATE_COMPARTMENT('region_policy',120,'E','EMPLOYEE'); EXECUTE SA_COMPONENTS.CREATE_GROUP('region_policy',20,'R20','REGION NORTH'); EXECUTE SA_COMPONENTS.CREATE_GROUP('region_policy',40,'R40','REGION SOUTH'); EXECUTE SA_COMPONENTS.CREATE_GROUP('region_policy',60,'R60','REGION EAST'); EXECUTE SA_COMPONENTS.CREATE_GROUP('region_policy',80,'R80','REGION WEST'); EXECUTE SA_USER_ADMIN.SET_USER_PRIVS('region_policy','ols_test','FULL,PROFILE_ACCESS');
Next we create and populate a test schema.
CONN ols_test/password CREATE TABLE customers ( id NUMBER(10) NOT NULL, cust_type VARCHAR2(10), first_name VARCHAR2(30), last_name VARCHAR2(30), region VARCHAR2(5), credit NUMBER(10,2), CONSTRAINT customer_pk PRIMARY KEY (id)); GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO PUBLIC; INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 1, 'SILVER', 'Harry', 'Hill', 'NORTH', 11000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 2, 'SILVER', 'Vic', 'Reeves', 'NORTH', 2000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 3, 'SILVER', 'Bob', 'Mortimer', 'WEST', 500.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 4, 'SILVER', 'Paul', 'Whitehouse', 'SOUTH', 1000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 5, 'SILVER', 'Harry', 'Enfield', 'EAST', 20000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 6, 'GOLD', 'Jenifer', 'Lopez', 'WEST', 500.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 7, 'GOLD', 'Kylie', 'Minogue', 'NORTH', 1000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 8, 'GOLD', 'Maria', 'Carey', 'WEST', 1000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES ( 9, 'GOLD', 'Dani', 'Minogue', 'SOUTH', 20000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES (10, 'GOLD', 'Whitney', 'Houston', 'EAST', 500.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES (11, 'PLATINUM', 'Robbie', 'Williams', 'SOUTH', 500.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES (12, 'PLATINUM', 'Thom', 'Yorke', 'NORTH', 2000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES (13, 'PLATINUM', 'Gareth', 'Gates', 'WEST', 10000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES (14, 'PLATINUM', 'Darius', 'Dinesh', 'EAST', 2000.00); INSERT INTO customers (id, cust_type, first_name, last_name, region, credit) VALUES (15, 'PLATINUM', 'Will', 'Young', 'EAST', 100.00); COMMIT;
Next we create a function to produce a label.
CREATE OR REPLACE FUNCTION get_customer_label ( p_cust_type IN VARCHAR2, p_region IN VARCHAR2, p_credit IN NUMBER) RETURN LBACSYS.LBAC_LABEL AS v_label VARCHAR2(80); BEGIN IF p_credit > 2000 THEN v_label := 'L3:'; ELSIF p_credit > 500 THEN v_label := 'L2:'; ELSE v_label := 'L1:'; END IF; IF p_cust_type = 'PLATINUM' THEN v_label := v_label || 'M:'; ELSE v_label := v_label || 'E:'; END IF; IF p_region = 'NORTH' THEN v_label := v_label || 'R20'; ELSIF p_region = 'SOUTH' THEN v_label := v_label || 'R40'; ELSIF p_region = 'EAST' THEN v_label := v_label || 'R60'; ELSIF p_region = 'WEST' THEN v_label := v_label || 'R80'; END IF; RETURN TO_LBAC_DATA_LABEL('region_policy',v_label); END get_customer_label; / SHOW ERRORS
Next we apply the policy to the table which results in the label column being added.
CONN ols_test/password BEGIN SA_POLICY_ADMIN.APPLY_TABLE_POLICY( policy_name => 'REGION_POLICY', schema_name => 'OLS_TEST', table_name => 'CUSTOMERS', table_options => 'NO_CONTROL'); END; /
Next we initialize the label column for existing data. Without this no access would be allowed.
UPDATE customers SET region_label = CHAR_TO_LABEL('REGION_POLICY','L1'); COMMIT;
Next we re-apply the policy to the table and link in the label function.
BEGIN SA_POLICY_ADMIN.REMOVE_TABLE_POLICY('REGION_POLICY','OLS_TEST','CUSTOMERS'); SA_POLICY_ADMIN.APPLY_TABLE_POLICY ( policy_name => 'REGION_POLICY', schema_name => 'OLS_TEST', table_name => 'CUSTOMERS', table_options => 'READ_CONTROL,WRITE_CONTROL,CHECK_CONTROL', label_function => 'ols_test.get_customer_label(:new.cust_type,:new.region,:new.credit)', predicate => NULL); END; /
Next we re-label the rows using the label function.
UPDATE customers SET first_name = first_name; COMMIT;
Next we create some more users to test the security settings.
CONN / AS SYSDBA; CREATE USER sales_manager IDENTIFIED BY password; CREATE USER sales_north IDENTIFIED BY password; CREATE USER sales_south IDENTIFIED BY password; CREATE USER sales_east IDENTIFIED BY password; CREATE USER sales_west IDENTIFIED BY password; GRANT CONNECT TO sales_manager, sales_north, sales_south, sales_east, sales_west; CONN ols_test/password BEGIN SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_manager','L3:M,E:R20,R40,R60,R80'); SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_north','L3:E:R20,R40'); SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_south','L3:E:R20,R40,R60,R80'); SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_east','L3:E:R60'); SA_USER_ADMIN.SET_USER_LABELS('region_policy','sales_west','L3:E:R80'); END; /
Finally we can connect to each user and test the security policy.
COLUMN first_name FORMAT A20 COLUMN last_name FORMAT A20 CONN sales_manager/password SELECT * FROM ols_test.customers; ID CUST_TYPE FIRST_NAME LAST_NAME REGIO CREDIT REGION_LABEL ---------- ---------- -------------------- -------------------- ----- ---------- ------------ 1 SILVER Harry Hill NORTH 11000 1000000001 2 SILVER Vic Reeves NORTH 2000 1000000002 3 SILVER Bob Mortimer WEST 500 1000000003 4 SILVER Paul Whitehouse SOUTH 1000 1000000004 5 SILVER Harry Enfield EAST 20000 1000000005 6 GOLD Jenifer Lopez WEST 500 1000000003 7 GOLD Kylie Minogue NORTH 1000 1000000002 8 GOLD Maria Carey WEST 1000 1000000006 9 GOLD Dani Minogue SOUTH 20000 1000000007 10 GOLD Whitney Houston EAST 500 1000000008 11 PLATINUM Robbie Williams SOUTH 500 1000000009 12 PLATINUM Thom Yorke NORTH 2000 1000000010 13 PLATINUM Gareth Gates WEST 10000 1000000011 14 PLATINUM Darius Dinesh EAST 2000 1000000012 15 PLATINUM Will Young EAST 100 1000000013 15 rows selected. SQL> CONN sales_north/password SELECT * FROM ols_test.customers; ID CUST_TYPE FIRST_NAME LAST_NAME REGIO CREDIT REGION_LABEL ---------- ---------- -------------------- -------------------- ----- ---------- ------------ 1 SILVER Harry Hill NORTH 11000 1000000001 2 SILVER Vic Reeves NORTH 2000 1000000002 4 SILVER Paul Whitehouse SOUTH 1000 1000000004 7 GOLD Kylie Minogue NORTH 1000 1000000002 9 GOLD Dani Minogue SOUTH 20000 1000000007 5 rows selected. SQL> CONN sales_south/password SELECT * FROM ols_test.customers; ID CUST_TYPE FIRST_NAME LAST_NAME REGIO CREDIT REGION_LABEL ---------- ---------- -------------------- -------------------- ----- ---------- ------------ 1 SILVER Harry Hill NORTH 11000 1000000001 2 SILVER Vic Reeves NORTH 2000 1000000002 3 SILVER Bob Mortimer WEST 500 1000000003 4 SILVER Paul Whitehouse SOUTH 1000 1000000004 5 SILVER Harry Enfield EAST 20000 1000000005 6 GOLD Jenifer Lopez WEST 500 1000000003 7 GOLD Kylie Minogue NORTH 1000 1000000002 8 GOLD Maria Carey WEST 1000 1000000006 9 GOLD Dani Minogue SOUTH 20000 1000000007 10 GOLD Whitney Houston EAST 500 1000000008 10 rows selected. SQL> CONN sales_east/password SELECT * FROM ols_test.customers; ID CUST_TYPE FIRST_NAME LAST_NAME REGIO CREDIT REGION_LABEL ---------- ---------- -------------------- -------------------- ----- ---------- ------------ 5 SILVER Harry Enfield EAST 20000 1000000005 10 GOLD Whitney Houston EAST 500 1000000008 2 rows selected. SQL> CONN sales_west/password SELECT * FROM ols_test.customers; ID CUST_TYPE FIRST_NAME LAST_NAME REGIO CREDIT REGION_LABEL ---------- ---------- -------------------- -------------------- ----- ---------- ------------ 3 SILVER Bob Mortimer WEST 500 1000000003 6 GOLD Jenifer Lopez WEST 500 1000000003 8 GOLD Maria Carey WEST 1000 1000000006 3 rows selected. SQL>
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/9i/oracle-label-security-9i