Oracle Label Security (OLS) is an extension of the Virtual Private Database (VPD) technology introduced in Oracle8i. 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

If the Oracle Label Security option was not loaded onto your server during the software installtion then:

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.

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).
(Start -> Programs -> Oracle - 92010 -> Configuration and Migration Tools -> Database Configuration Assistant)
$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:

CONNECT system/password@w2k1;

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:

CONNECT lbacsys/lbacsys@w2k1

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:

CONNECT ols_test/password@w2k1

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:

CONNECT ols_test/password@w2k1

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:

CONNECT ols_test/password@w2k1

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:

CONNECT system/password@w2k1;

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;

CONNECT ols_test/password@w2k1

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:

CONNECT sales_manager/password@w2k1
SELECT * FROM ols_test.customers;

CONNECT sales_north/password@w2k1
SELECT * FROM ols_test.customers;

CONNECT sales_south/password@w2k1
SELECT * FROM ols_test.customers;

CONNECT sales_east/password@w2k1
SELECT * FROM ols_test.customers;

CONNECT sales_west/password@w2k1
SELECT * FROM ols_test.customers;

For further information see: Oracle Label Security Administrator's Guide Release 2 (9.2)

[转自: http://www.oracle-base.com/articles/9i/OracleLabelSecurity9i.php]

ITPub博客

Oracle Label Security (OLS)