CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Fine Grained Access Control Demo
Version 11.1
 
Preparation As DBA

Create Demo Users
conn / as sysdba

CREATE USER secdemo
IDENTIFIED BY secdemo
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
PROFILE DEFAULT
QUOTA 0 ON system
QUOTA 0 ON sysaux
QUOTA UNLIMITED ON uwdata;

GRANT create session TO secdemo;
GRANT create any context TO secdemo;
GRANT create public synonym TO secdemo;
GRANT create table TO secdemo;
GRANT create view TO secdemo;
GRANT create procedure TO secdemo;
GRANT execute on dbms_rls TO secdemo;
GRANT create trigger TO secdemo;
GRANT administer database trigger TO secdemo;

CREATE USER morgan
IDENTIFIED BY morgan;

GRANT create session TO morgan;

CREATE USER wood
IDENTIFIED BY wood;

GRANT create session TO wood;
 
Preparation As  Application Owner

Create Demo Tables
conn secdemo/secdemo

CREATE TABLE employee (
employee_id    NUMBER(4),
last_name      VARCHAR2(15),
first_name     VARCHAR2(15),
cost_center_id NUMBER(4),
manager_id     NUMBER(4));

ALTER TABLE employee
ADD CONSTRAINT pk_employee
PRIMARY KEY (employee_id);

CREATE TABLE cost_center (
cost_center_id NUMBER(4),
manager_id     NUMBER(4),
description    VARCHAR2(30));

ALTER TABLE cost_center
ADD CONSTRAINT pk_cost_center
PRIMARY KEY (cost_center_id);

CREATE TABLE exp_report (
report_id       NUMBER(4),
employee_id     NUMBER(4) NOT NULL,
cost_center_id  NUMBER(4) NOT NULL,
submission_date DATE,
approval_date   DATE,
purpose         VARCHAR2(30));

ALTER TABLE exp_report
ADD CONSTRAINT pk_exp_report
PRIMARY KEY (report_id);

CREATE TABLE exp_line (
report_id       NUMBER(4),
line_id         NUMBER(4),
type_id         NUMBER(4),
received_amount NUMBER(7,2),
receipt         NUMBER(1),
exp_date        DATE,
currency_id     NUMBER(4));

ALTER TABLE exp_line
ADD CONSTRAINT pk_exp_line
PRIMARY KEY (report_id, line_id);

CREATE TABLE exp_type (
type_id     NUMBER(4),
description VARCHAR2(30));

ALTER TABLE exp_type
ADD CONSTRAINT pk_exp_type
PRIMARY KEY (type_id);

CREATE TABLE exp_currency (
currency_id NUMBER(4),
description VARCHAR2(30),
rate        NUMBER(7,2),
symbol      VARCHAR2(2));

ALTER TABLE exp_currency
ADD CONSTRAINT pk_exp_currency
PRIMARY KEY (currency_id);

ALTER TABLE employee
ADD CONSTRAINT fk_employee_cost_center_id
FOREIGN KEY (cost_center_id)
REFERENCES cost_center (cost_center_id);

ALTER TABLE employee
ADD CONSTRAINT fk_employee_manager_id
FOREIGN KEY (manager_id)
REFERENCES employee (employee_id);

ALTER TABLE exp_report
ADD CONSTRAINT fk_exp_report_employee_id
FOREIGN KEY (employee_id)
REFERENCES employee (employee_id);

ALTER TABLE exp_report
ADD CONSTRAINT fk_exp_report_cost_center_id
FOREIGN KEY (cost_center_id)
REFERENCES cost_center (cost_center_id);

ALTER TABLE exp_line
ADD CONSTRAINT fk_exp_line_report_id
FOREIGN KEY (report_id)
REFERENCES exp_report (report_id);

ALTER TABLE exp_line
ADD CONSTRAINT fk_exp_line_type_id
FOREIGN KEY (type_id)
REFERENCES exp_type (type_id);

ALTER TABLE exp_line
ADD CONSTRAINT fk_exp_line_currency_id
FOREIGN KEY (currency_id)
REFERENCES exp_currency (currency_id);

exec dbms_stats.gather_schema_stats(USER);

SELECT object_name, object_type
FROM user_objects
ORDER BY 2,1;

SELECT table_name, num_rows
FROM user_tables
ORDER BY 1;

SELECT table_name, constraint_name, constraint_type
FROM user_constraints
ORDER BY 1,3;

Create View
CREATE OR REPLACE VIEW exp_report_sum_view AS
SELECT e.employee_id, e.last_name, r.report_id, r.purpose,
SUM(l.received_amount) total_amount, r.cost_center_id,
r.submission_date, r.approval_date
FROM employee e, exp_report r, exp_line l
WHERE e.employee_id = r.employee_id
AND r.report_id = l.report_id
GROUP BY E.employee_id, r.report_id, e.last_name, r.purpose,
r.cost_center_id, r.submission_date, r.approval_date;

Grant Object Privileges

PUBLIC access not required ... or desired ... this is done this way for demo purposes only.
GRANT SELECT ON employee TO PUBLIC;
GRANT SELECT ON cost_center TO PUBLIC;
GRANT SELECT ON exp_report TO PUBLIC;
GRANT SELECT ON exp_line TO PUBLIC;
GRANT SELECT ON exp_type TO PUBLIC;
GRANT SELECT ON exp_currency TO PUBLIC;
GRANT SELECT ON exp_report_sum_view TO PUBLIC;

GRANT UPDATE ON exp_report TO PUBLIC;
GRANT DELETE ON exp_report TO PUBLIC;
GRANT INSERT ON exp_report TO PUBLIC;
GRANT DELETE ON exp_line TO PUBLIC;
GRANT INSERT ON exp_line TO PUBLIC;

Insert Records Into Exp_Currency Table
INSERT INTO exp_currency
VALUES (1, '$ US DOLLAR',2.0,'$');

INSERT INTO exp_currency
VALUES (2, 'FF - FRENCH FRANC',0.2,'FF');

INSERT INTO exp_currency
VALUES (3, ' - UK POUNDS',1.0,'');

INSERT INTO exp_currency
VALUES (4, 'DM - DEUTCH MARKS',0.6,'DM');

SELECT * FROM exp_currency;

Insert Records Into Exp_Type Table
INSERT INTO exp_type VALUES (1, 'AIRFARE');
INSERT INTO exp_type VALUES (2, 'TAXI');
INSERT INTO exp_type VALUES (3, 'RENTAL CAR');
INSERT INTO exp_type VALUES (4, 'LIMO/CAR SERVICE');
INSERT INTO exp_type VALUES (5, 'ROOM');
INSERT INTO exp_type VALUES (6, 'FOOD/DRINKS-HOTEL BILL');
INSERT INTO exp_type VALUES (7, 'PHONE/OTHER-HOTEL BILL');
INSERT INTO exp_type VALUES (8, 'BREAKFAST');
INSERT INTO exp_type VALUES (9, 'LUNCH');
INSERT INTO exp_type VALUES (10, 'DINNER');

SELECT * FROM exp_type;

Insert Records Into Cost_Center Table
INSERT INTO cost_center VALUES (692, 7839, 'ADMIN.');
INSERT INTO cost_center VALUES (672, 7839, 'US SALES');
INSERT INTO cost_center VALUES (667, 7506, 'ASIAN SALES');
INSERT INTO cost_center VALUES (670, 7569, 'EURO SALES');
INSERT INTO cost_center VALUES (668, 7507, 'WW SUPPORT');
INSERT INTO cost_center VALUES (671, 7839, 'WW MKTG');
INSERT INTO cost_center VALUES (673, 7505, 'US MKTG');
INSERT INTO cost_center VALUES (674, 7698, 'ASIAN MKTG');
INSERT INTO cost_center VALUES (669, 7566, 'EUROPEAN MKTG');

SELECT * FROM cost_center;

Insert Into Employee Table
INSERT INTO employee VALUES (7369,'SMITH','JOHN',667,7902);
INSERT INTO employee VALUES (7499,'ALLEN','KEVIN',670,7698);
INSERT INTO employee VALUES (7505,'DOYLE','JEAN',671,7839);
INSERT INTO employee VALUES (7506,'DENNIS','LYNN',671,7839);
INSERT INTO employee VALUES (7507,'BAKER','LESLIE',671,7839);
INSERT INTO employee VALUES (7521,'WARD','CYNTHIA',670,7698);
INSERT INTO employee VALUES (7555,'PETERS','DANIEL',670,7505);
INSERT INTO employee VALUES (7557,'SHAW','KAREN',670,7505);
INSERT INTO employee VALUES (7560,'DUNCAN','SARAH',670,7506);
INSERT INTO employee VALUES (7564,'LANGE','GREGORY',670,7506);
INSERT INTO employee VALUES (7566,'JONES','TERRY',671,7839);
INSERT INTO employee VALUES (7569,'MORGAN','DANIEL',670,7839);
INSERT INTO employee VALUES (7600,'PORTER','RAYMOND',670,7505);
INSERT INTO employee VALUES (7609,'LEWIS','RICHARD',668,7507);
INSERT INTO employee VALUES (7654,'MARTIN','KENNETH',670,7698);
INSERT INTO employee VALUES (7676,'SOMMERS','DENISE',668,7507);
INSERT INTO employee VALUES (7698,'JURGEN','DIETER',670,7839);
INSERT INTO employee VALUES (7782,'CLARK','CAROL',671,7839);
INSERT INTO employee VALUES (7788,'SCOTT','DONALD',669,7566);
INSERT INTO employee VALUES (7789,'WEST','LIVIA',670,7506);
INSERT INTO employee VALUES (7799,'FISHER','MATTHEW',669,7569);
INSERT INTO employee VALUES (7820,'ROSS','PAUL',670,7505);
INSERT INTO employee VALUES (7839,'KING','FRANCIS',672,7839);
INSERT INTO employee VALUES (7844,'TURNER','MARY',670,7698);
INSERT INTO employee VALUES (7876,'ADAMS','DIANE',667,7788);
INSERT INTO employee VALUES (7900,'JAMES','FRED',667,7698);
INSERT INTO employee VALUES (7902,'FORD','JENNIFER',669,7566);
INSERT INTO employee VALUES (7916,'ROBERTS','GRACE',669,7569);
INSERT INTO employee VALUES (7919,'DOUGLAS','MICHAEL',667,7799);
INSERT INTO employee VALUES (7934,'WOOD','GRAHAM',670,7782);
INSERT INTO employee VALUES (7950,'JENSEN','ALICE',667,7505);
INSERT INTO employee VALUES (7954,'MURRAY','JAMES',670,7506);

SELECT last_name, employee_id, manager_id, LEVEL
FROM employee
START WITH employee_id = 7934
CONNECT BY PRIOR employee_id = manager_id;

SELECT last_name, employee_id, manager_id, LEVEL
FROM employee
START WITH employee_id = 7569
CONNECT BY PRIOR employee_id = manager_id
ORDER BY level;

Insert Records Into Exp_Report Table
INSERT INTO exp_report VALUES (1,7954,667,SYSDATE-3,SYSDATE,'Cust Visit');
INSERT INTO exp_report VALUES (2,7950,670,SYSDATE,NULL,'Cust Visit');
INSERT INTO exp_report VALUES (3,7954,670,SYSDATE,NULL,'Cust Visit');
INSERT INTO exp_report VALUES (4,7954,671,SYSDATE,NULL,'Cust Visit');
INSERT INTO exp_report VALUES (5,7934,670,SYSDATE-1,NULL,'Training');
INSERT INTO exp_report VALUES (6,7698,670,SYSDATE-7,NULL,'Training');
INSERT INTO exp_report VALUES (7,7698,670,SYSDATE-7,NULL,'Cust Visit');
INSERT INTO exp_report VALUES (8,7934,671,SYSDATE-7,NULL,'Training');
INSERT INTO exp_report VALUES (9,7934,670,SYSDATE-7,NULL,'Training');
INSERT INTO exp_report VALUES (10,7954,670,SYSDATE-20,SYSDATE-20,'Cust Visit');
INSERT INTO exp_report VALUES (11,7954,670,SYSDATE-20,SYSDATE-17,'Cust Visit');
INSERT INTO exp_report VALUES (12,7569,670,SYSDATE-22,SYSDATE-19,'Cust Visit');

SELECT * FROM exp_report;

Insert Records Into Exp_Line Table
INSERT INTO exp_line VALUES (1,1,1,500.2,1,SYSDATE-33,1);
INSERT INTO exp_line VALUES (1,2,8,12.2,1,SYSDATE-33,1);
INSERT INTO exp_line VALUES (1,3,9,20.0,1,SYSDATE-33,1);
INSERT INTO exp_line VALUES (2,1,2,21.0,1,SYSDATE-23,1);
INSERT INTO exp_line VALUES (2,2,5,200.0,1,SYSDATE-23,1);
INSERT INTO exp_line VALUES (2,3,9,12.0,1,SYSDATE-23,1);
INSERT INTO exp_line VALUES (2,4,10,20.0,1,SYSDATE-23,1);
INSERT INTO exp_line VALUES (3,1,9,10.2,1,SYSDATE-33,1);
INSERT INTO exp_line VALUES (4,1,5,210.3,1,SYSDATE-31,1);
INSERT INTO exp_line VALUES (4,2,6,21.0,1,SYSDATE-31,1);
INSERT INTO exp_line VALUES (4,3,7,12.1,1,SYSDATE-31,1);
INSERT INTO exp_line VALUES (4,4,8,10.3,1,SYSDATE-31,1);
INSERT INTO exp_line VALUES (5,1,10,53.2,1,SYSDATE-28,1);
INSERT INTO exp_line VALUES (6,1,10,23.2,1,SYSDATE-44,2);
INSERT INTO exp_line VALUES (7,1,5,210.3,1,SYSDATE-28,1);
INSERT INTO exp_line VALUES (7,2,6,21.0,1,SYSDATE-28,1);
INSERT INTO exp_line VALUES (7,3,7,12.1,1,SYSDATE-28,1);
INSERT INTO exp_line VALUES (7,4,8,10.3,1,SYSDATE-28,1);
INSERT INTO exp_line VALUES (8,1,5,1120.3,1,SYSDATE-27,3);
INSERT INTO exp_line VALUES (8,2,6,20.0,1,SYSDATE-27,3);
INSERT INTO exp_line VALUES (8,3,7,17.1,1,SYSDATE-27,3);
INSERT INTO exp_line VALUES (8,4,8,20.3,1,SYSDATE-27,3);
INSERT INTO exp_line VALUES (9,1,5,1120.3,1,SYSDATE-20,3);
INSERT INTO exp_line VALUES (9,2,6,20.0,1,SYSDATE-20,3);
INSERT INTO exp_line VALUES (9,3,7,17.1,1,SYSDATE-20,3);
INSERT INTO exp_line VALUES (9,4,8,20.3,1,SYSDATE-20,3);
INSERT INTO exp_line VALUES (9,5,5,1120.3,1,SYSDATE-20,3);
INSERT INTO exp_line VALUES (9,6,6,20.0,1,SYSDATE-20,3);
INSERT INTO exp_line VALUES (10,1,5,1120.3,1,SYSDATE-45,1);
INSERT INTO exp_line VALUES (11,1,5,1120.3,1,SYSDATE-38,1);
INSERT INTO exp_line VALUES (12,1,5,1120.3,1,SYSDATE-38,2);
INSERT INTO exp_line VALUES (12,2,5,1120.3,1,SYSDATE-38,2);

SELECT * FROM exp_line;

Create The Application Context
CREATE OR REPLACE CONTEXT exp_rpt USING secdemo.exprep_ctx;

SELECT owner, object_type
FROM all_objects
WHERE object_name = 'EXP_RPT';

CREATE OR REPLACE PUBLIC SYNONYM exprep_ctx FOR secdemo.exprep_ctx;

SELECT owner, object_type
FROM all_objects
WHERE object_name = 'EXPREP_CTX';

Create the package that implements the context
CREATE OR REPLACE PACKAGE exprep_ctx AS
 PROCEDURE set_ctx;
END exprep_ctx;
/

--====================================

CREATE OR REPLACE PACKAGE BODY exprep_ctx IS

PROCEDURE set_ctx IS
 empid    employee.employee_id%TYPE;
 countrec NUMBER;
 cc       employee.cost_center_id%TYPE;
 role     VARCHAR2(20);
 whoami   user_users.username%TYPE;
BEGIN
  SELECT sys_context('userenv', 'session_user')
  INTO whoami
  FROM dual;

  IF whoami NOT IN ('SYS', 'SYSTEM', 'SECDEMO') THEN
    -- SET employee id
    SELECT employee_id
    INTO empid
    FROM employee
    WHERE last_name = whoami;

    dbms_session.set_context('exp_rpt', 'emp_number', empid);

    SELECT COUNT(*)
    INTO countrec
    FROM cost_center
    WHERE manager_id = empid;

    IF (countrec > 0) THEN
      dbms_session.set_context('exp_rpt', 'exp_role', 'manager');
    ELSE
      dbms_session.set_context('exp_rpt', 'exp_role', 'employee');
    END IF;

    -- SET cost center number
    SELECT cost_center_id
    INTO cc
    FROM employee
    WHERE last_name = sys_context('userenv', 'session_user');

    dbms_session.set_context('exp_rpt', 'cc_number', cc);
  END IF;
END set_ctx;

END exprep_ctx;
/

GRANT EXECUTE ON secdemo.exprep_ctx TO PUBLIC;

Create The Policy Functions Package
CREATE OR REPLACE PACKAGE exp_security AS
  FUNCTION empview_sec(owner VARCHAR2, objname VARCHAR2)
 RETURN VARCHAR2;


  FUNCTION empnum_sec(owner VARCHAR2, objname VARCHAR2)
 RETURN VARCHAR2;


  FUNCTION empnumline_sec(owner VARCHAR2, objname VARCHAR2)
 RETURN VARCHAR2;


  FUNCTION ccid_mgr_sec(owner VARCHAR2, objname VARCHAR2)
 RETURN VARCHAR2;

END exp_security;
/

--====================================

CREATE OR REPLACE PACKAGE BODY exp_security IS

FUNCTION empview_sec(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS
 predicate VARCHAR2(2000);
BEGIN
  IF (sys_context('exp_rpt', 'exp_role') = 'manager') THEN
    predicate := 'cost_center_id = sys_context(''exp_rpt'', ''cc_number'')';
  ELSE
    predicate := 'employee_id = sys_context(''exp_rpt'', ''emp_number'')';
  END IF;
  RETURN predicate;
END empview_sec;

--------------------
FUNCTION empnum_sec(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS
 predicate  VARCHAR2(2000);
BEGIN
  predicate := 'employee_id = sys_context(''exp_rpt'',''emp_number'')';
  RETURN predicate;
END empnum_sec;

--------------------
FUNCTION empnumline_sec(owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2 IS

predicate  VARCHAR2(2000);

BEGIN
  predicate := 'report_id IN (
    SELECT report_id
    FROM exp_report
    WHERE employee_id = sys_context(''exp_rpt'',''emp_number''))';
  RETURN predicate;
END empnumline_sec;

--------------------
FUNCTION ccid_mgr_sec(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS
 predicate  VARCHAR2(2000);
BEGIN
  predicate := 'cost_center_id = (
    SELECT cost_center_id
    FROM cost_center
    WHERE manager_id = sys_context(''exp_rpt'',''emp_number''))';
  RETURN predicate;
END ccid_mgr_sec;

END exp_security;
/

SELECT object_name, object_type
FROM user_objects
ORDER BY 2,1;
 
Create Access Policies
Policy #1 User can select only their reports if they are an employee. They can select their reports and all the reports in their cost center if they  are the cost center manager

exec dbms_rls.add_policy('secdemo', 'exp_report_sum_view', 'exp_rpt_view_policy', 'secdemo', 'exp_security.empview_sec', 'SELECT');
Policy #2 User can delete their own reports only

exec dbms_rls.add_policy(USER, 'exp_report', 'exp_rpt_policy', USER, 'exp_security.empnum_sec', 'DELETE');
Policy #3 User can insert a report for themselves only

exec dbms_rls.add_policy(USER, 'exp_report', 'exp_rpt_insert_policy', USER, 'exp_security.empnum_sec', 'INSERT');
Policy #4 User can delete a line in their report only if they are the report owner

exec dbms_rls.add_policy(USER,'exp_line', 'exp_line_policy', USER, 'exp_security.empnumline_sec', 'DELETE');
Policy #5 User can approve (update) a report only if they are the cost center manager

exec dbms_rls.add_policy(USER, 'exp_report', 'exp_rpt_approve_policy', USER, 'exp_security.ccid_mgr_sec', 'UPDATE');

Examine Policies
desc user_policies

col object_name format a20
col policy_group format a12
col policy_name format a22
col pf_owner format a8
col package format a12
col function format a14
col enable format a6
col static_policy format a13
col policy_type format a11
col long_predicate format a14

SELECT object_name, policy_group, policy_name, pf_owner, package, function
FROM user_policies;

SELECT policy_name, sel, ins, upd, del, idx, chk_option, enable, static_policy, policy_type, long_predicate
FROM user_policies;

Disable and Re-enable a Policy
SELECT object_name, policy_group, policy_name, enable
FROM user_policies
WHERE policy_name ='EXP_RPT_VIEW_POLICY';

exec dbms_rls.enable_policy(USER, 'exp_report_sum_view', 'exp_rpt_view_policy', FALSE);

SELECT object_name, policy_group, policy_name, enable
FROM user_policies
WHERE policy_name ='EXP_RPT_VIEW_POLICY';

exec dbms_rls.enable_policy(USER, 'exp_report_sum_view', 'exp_rpt_view_policy', TRUE);

SELECT object_name, policy_group, policy_name, enable
FROM user_policies
WHERE policy_name ='EXP_RPT_VIEW_POLICY';

Drop Policies
exec dbms_rls.drop_policy('secdemo', 'exp_report_sum_view', 'exp_rpt_view_policy');

exec dbms_rls.drop_policy('secdemo', 'exp_report', 'exp_rpt_policy');

exec dbms_rls.drop_policy('secdemo', 'exp_report', 'exp_rpt_insert_policy');

exec dbms_rls.drop_policy('secdemo', 'exp_line', 'exp_line_policy');

exec dbms_rls.drop_policy('secdemo', 'exp_report', 'exp_rpt_approve_policy');

SELECT policy_name
FROM user_policies;
 
Create Policy Groups and Access Policies

Create Policy Group
exec dbms_rls.create_policy_group('secdemo', 'exp_report_sum_view', 'demo_group1');

exec dbms_rls.create_policy_group('secdemo', 'exp_report', 'demo_group1');

exec dbms_rls.create_policy_group('secdemo', 'exp_line', 'demo_group2');

SELECT * FROM user_policy_groups;

Recreate Policies
exec dbms_rls.add_grouped_policy(USER, 'exp_report_sum_view', 'demo_group1', 'policy1', USER, 'exp_security.empview_sec', 'SELECT');

exec dbms_rls.add_grouped_policy(USER, 'exp_report', 'demo_group1', 'policy2', USER, 'exp_security.empnum_sec', 'DELETE');

exec dbms_rls.add_grouped_policy(USER, 'exp_report', 'demo_group1', 'policy3', USER, 'exp_security.empnum_sec', 'INSERT');

exec dbms_rls.add_grouped_policy(USER,'exp_line', 'demo_group2', 'policy4', USER, 'exp_security.empnumline_sec', 'DELETE');

exec dbms_rls.add_grouped_policy(USER, 'exp_report', 'demo_group1','policy5',  USER, 'exp_security.ccid_mgr_sec', 'UPDATE');

SELECT object_name, policy_group, policy_name, pf_owner, package, function
FROM user_policies;

SELECT policy_name, sel, ins, upd, del, idx, chk_option, enable, static_policy, policy_type, long_predicate
FROM user_policies;

Disable and Re-enable Grouped Policy
SELECT object_name, policy_group, policy_name, enable
FROM user_policies
WHERE policy_name ='POLICY1';

exec dbms_rls.disable_grouped_policy(USER, 'exp_report_sum_view', 'demo_group1', 'policy1');

SELECT object_name, policy_group, policy_name, enable
FROM user_policies
WHERE policy_name ='POLICY1';

exec dbms_rls.enable_grouped_policy(USER, 'exp_report_sum_view', 'demo_group1', 'policy1');

SELECT object_name, policy_group, policy_name, enable
FROM user_policies
WHERE policy_name ='POLICY1';
 
Set Context On Logon

Create Logon Trigger to enable context setting
CREATE OR REPLACE TRIGGER secdemo.set_expense_ctx
AFTER LOGON
ON DATABASE
BEGIN
  secdemo.exprep_ctx.set_ctx;
END;
/
 
Run Demo

View data as manager Morgan
conn morgan/morgan

SELECT table_name
FROM all_tables
WHERE owner = 'SECDEMO';

SELECT view_name
FROM all_views
WHERE owner = 'SECDEMO';

-- no controlling policy
SELECT * FROM secdemo.employee;

col last_name format a9
col purpose format a10
col submission_date format a15
col approval_date format a15

-- controlled by policy #1
SELECT * FROM secdemo.exp_report_sum_view;

View data as employee Wood
conn secdemo/secdemo

exec dbms_rls.disable_grouped_policy(USER, 'exp_report_sum_view', 'demo_group1', 'policy1');

conn wood/wood

SELECT * FROM secdemo.employee;

EXPLAIN PLAN FOR
SELECT * FROM secdemo.employee;

SELECT * FROM TABLE(dbms_xplan.display);

SELECT * FROM secdemo.exp_report_sum_view;

EXPLAIN PLAN FOR
SELECT * FROM secdemo.exp_report_sum_view;

SELECT * FROM TABLE(dbms_xplan.display);

conn secdemo/secdemo

exec dbms_rls.enable_grouped_policy(USER, 'exp_report_sum_view', 'demo_group1', 'policy1');

conn wood/wood

-- no controlling policy
SELECT * FROM secdemo.employee;

EXPLAIN PLAN FOR
SELECT * FROM secdemo.employee;

SELECT * FROM TABLE(dbms_xplan.display);

-- controlled by policy #1
SELECT * FROM secdemo.exp_report_sum_view;

EXPLAIN PLAN FOR
SELECT * FROM secdemo.exp_report_sum_view;

SELECT * FROM TABLE(dbms_xplan.display);
 
Demo Clean-up

Dismantle the demo infrastructure
conn secdemo/secdemo

SELECT object_name, policy_group, policy_name, enable
FROM user_policies;

exec dbms_rls.drop_grouped_policy(USER, 'exp_report_sum_view', 'demo_group1', 'policy1');

exec dbms_rls.drop_grouped_policy(USER, 'exp_report', 'demo_group1', 'policy2');

exec dbms_rls.drop_grouped_policy(USER, 'exp_report', 'demo_group1', 'policy3');

exec dbms_rls.drop_grouped_policy(USER, 'exp_line', 'demo_group2', 'policy4');

exec dbms_rls.drop_grouped_policy(USER, 'exp_report', 'demo_group1', 'policy5');

SELECT object_name, policy_group, policy_name, enable
FROM user_policies;

SELECT * FROM user_policy_groups;

exec dbms_rls.delete_policy_group(USER, 'exp_report_sum_view', 'demo_group1');

exec dbms_rls.delete_policy_group(USER, 'exp_report', 'demo_group1');

exec dbms_rls.delete_policy_group(USER, 'exp_line', 'demo_group2');

SELECT * FROM user_policy_groups;

conn / as sysdba

drop user wood cascade;

drop user morgan cascade;

drop user secdemo cascade;

col package format a20

SELECT * FROM dba_context;

drop context exp_rpt;
 
Related Topics
DBMS_RLS
DBMS_SESSION
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [105 users online]    © 2010 psoug.org