| Actions As SYSTEM |
| Create Users |
CREATE USER secdemo
IDENTIFIED BY secdemo
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
PROFILE DEFAULT
QUOTA 0 ON system
QUOTA UNLIMITED ON uwdata
ACCOUNT UNLOCK;
CREATE USER morgan
IDENTIFIED BY morgan
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
PROFILE DEFAULT
QUOTA 0 ON system
QUOTA UNLIMITED ON uwdata
ACCOUNT UNLOCK;
CREATE USER cline
IDENTIFIED BY cline
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
PROFILE DEFAULT
QUOTA UNLIMITED ON uwdata
ACCOUNT UNLOCK; |
| Grant System Privileges To Users |
GRANT connect TO
secdemo;
GRANT create any context TO secdemo;
GRANT create any synonym TO secdemo;
GRANT create table TO secdemo;
GRANT create trigger TO secdemo;
GRANT create procedure TO secdemo;
GRANT execute_catalog_role TO secdemo;
ALTER USER secdemo DEFAULT ROLE ALL;
/*
EXECUTE_CATALOG_ROLE definition:
This role can be granted to users to allow EXECUTE privileges for
packages and procedures in the data dictionary.
*/
GRANT connect TO morgan;
GRANT execute_catalog_role TO morgan;
ALTER USER morgan DEFAULT ROLE ALL;
GRANT connect TO cline;
GRANT execute_catalog_role TO cline;
ALTER USER cline DEFAULT ROLE ALL; |
| Create Public Synonym |
CREATE PUBLIC SYNONYM exprep_ctx
FOR secdemo.exprep_ctx; |
| |
| Actions As SECDEMO |
Create Tables |
CREATE TABLE employee (
employee_id NUMBER(4),
last_name VARCHAR2(15),
first_name VARCHAR2(15),
middle_initial VARCHAR2(1),
cost_center_id NUMBER(4),
manager_id NUMBER(4));
CREATE TABLE cost_center (
cost_center_id NUMBER(4),
manager_id NUMBER(4),
description VARCHAR2(30));
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));
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));
CREATE TABLE exp_type (
type_id NUMBER(4),
description VARCHAR2(30));
CREATE TABLE exp_currency (
currency_id NUMBER(4),
description VARCHAR2(30),
rate NUMBER(7,2),
symbol VARCHAR2(2)); |
| 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 ... 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 |
INSERT INTO exp_currency
VALUES (1, '$ US DOLLAR',1.0,'$');
INSERT INTO exp_currency
VALUES (2, 'FF - FRENCH FRANC',0.2,'FF');
INSERT INTO exp_currency
VALUES (3, '� - UK POUNDS',2.0,'�');
INSERT INTO exp_currency
VALUES (4, 'DM - DEUTCH MARKS',0.6,'DM'); |
| Insert Records Into Exp_Type |
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'); |
| Insert Records Into Cost_Center |
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'); |
Insert Into Employee Table |
INSERT INTO employee VALUES
(7369,'SMITH','JOHN','Q',667,7902);
INSERT INTO employee VALUES (7499,'ALLEN','KEVIN','J',670,7698);
INSERT INTO employee VALUES (7505,'DOYLE','JEAN','K',671,7839);
INSERT INTO employee VALUES (7506,'DENNIS','LYNN','S',671,7839);
INSERT INTO employee VALUES (7507,'BAKER','LESLIE','D',671,7839);
INSERT INTO employee VALUES (7521,'WARD','CYNTHIA','D',670,7698);
INSERT INTO employee VALUES (7555,'PETERS','DANIEL','T',670,7505);
INSERT INTO employee VALUES (7557,'SHAW','KAREN','P',670,7505);
INSERT INTO employee VALUES (7560,'DUNCAN','SARAH','S',670,7506);
INSERT INTO employee VALUES (7564,'LANGE','GREGORY','J',670,7506);
INSERT INTO employee VALUES (7566,'JONES','TERRY','M',671,7839);
INSERT INTO employee VALUES (7569,'MORGAN','DANIEL','L',670,7839);
INSERT INTO employee VALUES (7600,'PORTER','RAYMOND','Y', 670, 7505);
INSERT INTO employee VALUES (7609,'LEWIS','RICHARD','M',668,7507);
INSERT INTO employee VALUES (7654,'MARTIN','KENNETH','J',670, 7698);
INSERT INTO employee VALUES (7676,'SOMMERS','DENISE','D',668, 7507);
INSERT INTO employee VALUES (7698,'JURGEN','DIETER','S',670,7839);
INSERT INTO employee VALUES (7782,'CLARK','CAROL','F',671,7839);
INSERT INTO employee VALUES (7788,'SCOTT','DONALD','T',669,7566);
INSERT INTO employee VALUES (7789,'WEST','LIVIA','N',670,7506);
INSERT INTO employee VALUES (7799,'FISHER','MATTHEW','G',669, 7569);
INSERT INTO employee VALUES (7820,'ROSS','PAUL','S',670,7505);
INSERT INTO employee VALUES (7839,'KING','FRANCIS','A',672,7839);
INSERT INTO employee VALUES (7844,'TURNER','MARY','A',670,7698);
INSERT INTO employee VALUES (7876,'ADAMS','DIANE','G',667,7788);
INSERT INTO employee VALUES (7900,'JAMES','FRED','S',667,7698);
INSERT INTO employee VALUES (7902,'FORD','JENNIFER','D',669,7566);
INSERT INTO employee VALUES (7916,'ROBERTS','GRACE','M',669,7569);
INSERT INTO employee VALUES (7919,'DOUGLAS','MICHAEL','A',667, 7799);
INSERT INTO employee VALUES (7934,'CLINE','JACK','M',670, 7782);
INSERT INTO employee VALUES (7950,'JENSEN','ALICE','B',667,7505);
INSERT INTO employee VALUES (7954,'MURRAY','JAMES','T',670,7506); |
| Insert Records Into exp_report |
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,'Customer Visit');
INSERT INTO exp_report VALUES (11,7954,670,SYSDATE-20,SYSDATE-17,'Customer Visit');
INSERT INTO exp_report VALUES (12,7569,670,SYSDATE-22,SYSDATE-19,'Customer Visit'); |
Insert Records Into Exp_Line |
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); |
Add Constraints |
ALTER TABLE employee
ADD CONSTRAINT pk_employee
PRIMARY KEY (employee_id);
ALTER TABLE cost_center
ADD CONSTRAINT pk_cost_center
PRIMARY KEY (cost_center_id);
ALTER TABLE exp_report
ADD CONSTRAINT pk_exp_report
PRIMARY KEY (report_id);
ALTER TABLE exp_line
ADD CONSTRAINT pk_exp_line
PRIMARY KEY (report_id, line_id);
ALTER TABLE exp_type
ADD CONSTRAINT pk_exp_type
PRIMARY KEY (type_id);
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); |
| Create The Application Context |
CREATE CONTEXT exp_rpt USING
secdemo.exprep_ctx; |
Create the package that implements the context |
CREATE OR REPLACE PACKAGE
exprep_ctx AS
PROCEDURE set_ctx;
END;
/
--====================================
CREATE OR REPLACE PACKAGE BODY exprep_ctx IS
PROCEDURE set_ctx IS
empnum NUMBER;
countrec NUMBER;
cc NUMBER;
role VARCHAR2(20);
BEGIN
-- SET emp_number
SELECT employee_id
INTO empnum
FROM employee
WHERE last_name = sys_context('userenv', 'session_user');
dbms_session.set_context('exp_rpt', 'emp_number', empnum);
SELECT count(*)
INTO countrec
FROM cost_center
WHERE manager_id = empnum;
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 cc_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 set_ctx;
END exprep_ctx;
/
GRANT EXECUTE ON secdemo.exprep_ctx TO public; |
Create The Policy Function |
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;
/ |
| |
| Associate The
Policy Function With Tables / Views |
| Policy #1 |
dbms_rls.add_policy(<object_owner >, <object_name>,
<policy_name>, <security_function_owner>, <security_function_name>,
<applicable_DML_action>; |
You can select only your reports if
you are an employee. You can select your reports and all the reports in your cost center
if you are the cost center manager
exec dbms_rls.add_policy('secdemo', 'exp_report_sum_view',
'exp_report_view_policy', 'secdemo',
'exp_security.empview_sec', 'SELECT'); |
| Policy #2 |
You can delete your report only
exec dbms_rls.add_policy('secdemo', 'exp_report',
'exp_report_policy', 'secdemo',
'exp_security.empnum_sec', 'DELETE'); |
| Policy #3 |
You can insert a report for
yourself only
exec dbms_rls.add_policy('secdemo', 'exp_report',
'exp_report_insert_policy', 'secdemo',
'exp_security.empnum_sec', 'INSERT'); |
| Policy #4 |
You can delete a line in your
report only if you are the owner of the report
exec dbms_rls.add_policy('secdemo','exp_line',
'exp_line_policy', 'secdemo', 'exp_security.empnumline_sec', 'DELETE'); |
| Policy #5 |
You can approve (update) a report
only if you are the cost center manager
exec dbms_rls.add_policy('secdemo', 'exp_report',
'exp_report_approve_policy', 'secdemo',
'exp_security.ccid_mgr_sec', 'UPDATE'); |
| Drop Policies 1 - 5 |
dbms_rls.drop_policy(<policy_owner>, <object_name>,
<policy_name>); |
exec dbms_rls.drop_policy('secdemo', 'exp_report_sum_view',
'exp_report_view_policy'); |
exec dbms_rls.drop_policy('secdemo', 'exp_report',
'exp_report_policy'); |
exec dbms_rls.drop_policy('secdemo', 'exp_report_sum_view',
'exp_report_insert_policy'); |
exec dbms_rls.drop_policy('secdemo', 'exp_line',
'exp_line_policy'); |
exec dbms_rls.drop_policy('secdemo', 'exp_report_approve_poliy',
'exp_report_approve_policy'); |
| |
| Final Action As
SYSTEM |
| Create On-Logon Trigger |
conn system/manager
CREATE OR REPLACE TRIGGER secdemo.set_expense_ctx
AFTER LOGON
ON DATABASE
BEGIN
secdemo.exprep_ctx.set_ctx;
END;
/ |
| |
| Add New
Fuctionality |
Create The Policy Function |
-- view
CREATE VIEW secdemo.uw_view AS
SELECT employee_id, first_name || ' ' || last_name EMPNAME
FROM employee;
GRANT all ON secdemo.uw_view TO PUBLIC;
-- add to the package header
FUNCTION uw_test(owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2;
-- add to the package body
FUNCTION uw_test(owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2 IS
predicate VARCHAR2(2000);
BEGIN
predicate := 'employee_id = (
SELECT employee_id
FROM employee
WHERE last_name = sys_context(''userenv'',''session_user''))';
RETURN predicate;
END uw_test;
exec dbms_rls.add_policy('secdemo', 'uw_view',
'uw_policy', 'secdemo', 'exp_security.uw_test', 'SELECT');
exec dbms_rls.add_policy('secdemo', 'uw_view',
'uw_update', 'secdemo', 'exp_security.uw_test', 'UPDATE');
exec dbms_rls.add_policy('secdemo', 'uw_view',
'uw_delete', 'secdemo', 'exp_security.uw_test', 'DELETE');
select * from secdemo.uw_view; |
|