8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

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.

Related articles.

Installation

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

Configure Instance

Once Oracle Label Security option has been installed on the server it can be added to an instance as follows.

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:

Hope this helps. Regards Tim...

Back to the Top.