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