8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle Label Security
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.
- Installation
- Configure Instance
- Create Test User
- Create Policy
- Define Components Of Labels
- Create Test Schema
- Create Label Function
- Apply Policy To Table
- Initialize Label
- Re-Apply Policy
- Re-Label Rows
- Create Users
- Test the Label Security Policy
Related articles.
- Virtual Private Databases (VPD)
- Security Enhancements In Oracle 9i
- Security Enhancements in Oracle Database 10g - VPD
Installation
If the Oracle Label Security option was not loaded onto your server during the software installtion then install it as follows.
- Start the Oracle Universal Installer (OUI).
- On the Welcome screen click the Next button.
- On the File Locations screen select the appropriate paths and click the Next button.
- On the Available Products screen select the Oracle9i Database option and click the Next button.
- On the Installation Types screen select the Custom option and click the Next button.
- On the Available Product Components screen select the Oracle Label Security option under the Enterprise Edition Options section. Make sure no other options are checked unless you want these features to be installed also. Then click the Next button.
- On the Summary screen click the Install button.
- On the End of Installation screen click the Exit button and confirm your action.
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
Configure Instance
Once Oracle Label Security option has been installed on the server it can be added to an instance as follows.
- Start the Database Configuration Assistant (DBCA). On Windows (Start -> Programs -> Oracle - 92010 -> Configuration and Migration Tools -> Database Configuration Assistant). ON Linux use "$ORACLE_HOME/bin/dbca"
- On the Welcome screen click the Next button.
- On the Operations screen select the "Configure database options in a database" option and click the Next button.
- On the Databases screen select the relevant instance and click the Next button.
- On the Database Features screen select the Oracle Label Security option and click the Next button.
- Click the Finish button.
- Popup screens will appear to tell you what operations will be performed and that the database must be restarted once complete. Click the OK button on these popups.
- Once complete exit the DBCA and stop and start your instance.
Create Test User
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;
Create Policy
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;
Define Components Of Labels
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');
Create Test Schema
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;
Create Label Function
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
Apply Policy To Table
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; /
Initialize Label
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;
Re-ApplyPolicy
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; /
Re-Label Rows
Next we re-label the rows using the label function.
UPDATE customers SET first_name = first_name; COMMIT;
Create Users
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; /
Test the Label Security Policy
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:
- Oracle Label Security Administrator's Guide Release 2 (9.2)
- Virtual Private Databases (VPD)
- Security Enhancements In Oracle 9i
- Security Enhancements in Oracle Database 10g - VPD
Hope this helps. Regards Tim...