| 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; |
|
