Oracle Fine Grained Access Control (FGAC) Demo
Version 18c

Preparation As DBA
Create Demo Users conn sys@pdbdev 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;

ALTER USER secdemo ENABLE EDITIONS;

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

GRANT create session TO morgan;

CREATE USER kyte
IDENTIFIED BY tom;

GRANT create session TO kyte;
 
Preparation As Application Owner
Create Demo Tables and constraints conn secdemo/secdemo@pdbdev

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(6));

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

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

OBJECT_NAME        OBJECT_TYPE
------------------ -------------
PK_COST_CENTER     INDEX
PK_EMPLOYEE        INDEX
PK_EXP_CURRENCY    INDEX
PK_EXP_LINE        INDEX
PK_EXP_REPORT      INDEX
PK_EXP_TYPE        INDEX
COST_CENTER        TABLE
EMPLOYEE           TABLE
EXP_CURRENCY       TABLE
EXP_LINE           TABLE
EXP_REPORT         TABLE
EXP_TYPE           TABLE

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

TABLE_NAME         CONSTRAINT_NAME              C
------------------ ---------------------------- -
COST_CENTER        PK_COST_CENTER               P
EMPLOYEE           PK_EMPLOYEE                  P
EMPLOYEE           FK_EMPLOYEE_COST_CENTER_ID   R
EMPLOYEE           FK_EMPLOYEE_MANAGER_ID       R
EXP_CURRENCY       PK_EXP_CURRENCY              P
EXP_LINE           PK_EXP_LINE                  P
EXP_LINE           FK_EXP_LINE_REPORT_ID        R
EXP_LINE           FK_EXP_LINE_CURRENCY_ID      R
EXP_LINE           FK_EXP_LINE_TYPE_ID          R
EXP_REPORT         SYS_C008210                  C
EXP_REPORT         SYS_C008211                  C
EXP_REPORT         PK_EXP_REPORT                P
EXP_REPORT         FK_EXP_REPORT_EMPLOYEE_ID    R
EXP_REPORT         FK_EXP_REPORT_COST_CENTER_ID R
EXP_TYPE           PK_EXP_TYPE                  P
Create Demo 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 morgan, kyte;
GRANT SELECT ON cost_center TO kyte, morgan;
GRANT SELECT ON exp_report TO morgan, kyte;
GRANT SELECT ON exp_line TO kyte, morgan;
GRANT SELECT ON exp_type TO morgan, kyte;
GRANT SELECT ON exp_currency TO kyte, morgan;
GRANT SELECT ON exp_report_sum_view TO morgan, kyte;

GRANT UPDATE ON exp_report TO kyte, morgan;
GRANT DELETE ON exp_report TO morgan, kyte;
GRANT INSERT ON exp_report TO kyte, morgan;
GRANT DELETE ON exp_line TO morgan, kyte;
GRANT INSERT ON exp_line TO kyte, morgan;
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');
COMMIT;

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');
COMMIT;

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');
COMMIT;

SELECT * FROM cost_center;
Insert Into Employee Table INSERT INTO employee VALUES (7839,'KING','FRANCIS',672,NULL);
INSERT INTO employee VALUES (7566,'JONES','TERRY',671,7839);
INSERT INTO employee VALUES (7902,'FORD','JENNIFER',669,7566);
INSERT INTO employee VALUES (7369,'SMITH','JOHN',667,7902);
INSERT INTO employee VALUES (7698,'JURGEN','DIETER',670,7839);
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 (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 (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 (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 (7916,'ROBERTS','GRACE',669,7569);
INSERT INTO employee VALUES (7919,'DOUGLAS','MICHAEL',667,7799);
INSERT INTO employee VALUES (7934,'KYTE','TOM',670,7782);
INSERT INTO employee VALUES (7950,'JENSEN','ALICE',667,7505);
INSERT INTO employee VALUES (7954,'MURRAY','JAMES',670,7506);
COMMIT;

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

LAST_NAME       EMPLOYEE_ID MANAGER_ID  LEVEL
--------------- ----------- ---------- ------
KYTE                   7934       7782      1

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;

LAST_NAME       EMPLOYEE_ID MANAGER_ID  LEVEL
--------------- ----------- ---------- ------
MORGAN                 7569       7839      1
ROBERTS                7916       7569      2
FISHER                 7799       7569      2
DOUGLAS                7919       7799      3
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');
COMMIT;

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

SELECT * FROM exp_line;

exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);
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';

no rows selected

CREATE OR REPLACE PUBLIC SYNONYM exprep_ctx FOR secdemo.exprep_ctx;

Synonym created.

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

OWNER                     OBJECT_TYPE
------------------------- -------------------------
PUBLIC                    SYNONYM
Create the package that implements the context and its audit table -- create login audit table
CREATE TABLE ctx_audit (
login_date TIMESTAMP WITH LOCAL TIME ZONE,
user_name  VARCHAR2(30),
emp_id     VARCHAR2(30),
cc         VARCHAR2(30));

-- create context setting package
CREATE OR REPLACE PACKAGE exprep_ctx AUTHID DEFINER IS
 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 the 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;

    -- seet the 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);

    INSERT INTO ctx_audit VALUES (SYSTIMESTAMP, USER, empid, cc);
    COMMIT;
  END IF;
END set_ctx;

END exprep_ctx;
/

GRANT EXECUTE ON secdemo.exprep_ctx TO PUBLIC;
GRANT SELECT ON ctx_audit TO morgan, kyte;
Create The Policy Functions Package CREATE OR REPLACE PACKAGE exp_security AUTHID DEFINER 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 AS
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
Add a row level security policy to a table or view dbms_rls.add_policy(
object_schema         IN VARCHAR2       := NULL,
object_name           IN VARCHAR2,
policy_name           IN VARCHAR2,
function_schema       IN VARCHAR2       := NULL,
policy_function       IN VARCHAR2,
statement_types       IN VARCHAR2       := NULL,
update_check          IN BOOLEAN        := FALSE,
enable                IN BOOLEAN        := TRUE,
static_policy         IN BOOLEAN        := FALSE,
policy_type           IN BINARY_INTEGER := NULL,
long_predicate        IN BOOLEAN        := FALSE,
sec_relevant_cols     IN VARCHAR2       := NULL,
sec_relevant_cols_opt IN BINARY_INTEGER := NULL,
namespace             IN VARCHAR2       := NULL,
attribute             IN VARCHAR2       := NULL);
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 -- users can delete only their own reports

exec dbms_rls.add_policy(USER, 'exp_report', 'exp_rpt_policy', USER, 'exp_security.empnum_sec', 'DELETE');
Policy #3 -- 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 #4 -- only a cost center manager can approve (update) a report
exec dbms_rls.add_policy(USER, 'exp_report', 'exp_rpt_approve_policy', USER, 'exp_security.ccid_mgr_sec', 'UPDATE');

SELECT policy_name
FROM user_policies;

POLICY_NAME
----------------------
EXP_RPT_APPROVE_POLICY
EXP_RPT_POLICY
EXP_LINE_POLICY
EXP_RPT_VIEW_POLICY
Review Policy Metadata 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
FROM user_policies;

OBJECT_NAME          POLICY_GROUP POLICY_NAME        
-------------------- ------------ -------------------
EXP_REPORT_SUM_VIEW  SYS_DEFAULT  EXP_RPT_VIEW_POLICY
EXP_REPORT           SYS_DEFAULT  EXP_RPT_POLICY
EXP_LINE             SYS_DEFAULT  EXP_LINE_POLICY
EXP_REPORT           SYS_DEFAULT  EXP_RPT_APPROVE_POLICY

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


OBJECT_NAME          POLICY_NAME            PF_OWNER PACKAGE      FUNCTION
-------------------- ---------------------- -------- ------------ --------------
EXP_REPORT_SUM_VIEW  EXP_RPT_VIEW_POLICY    SECDEMO  EXP_SECURITY EMPVIEW_SEC
EXP_REPORT           EXP_RPT_POLICY         SECDEMO  EXP_SECURITY EMPNUM_SEC
EXP_LINE             EXP_LINE_POLICY        SECDEMO  EXP_SECURITY EMPNUMLINE_SEC
EXP_REPORT           EXP_RPT_APPROVE_POLICY SECDEMO  EXP_SECURITY CCID_MGR_SEC

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

POLICY_NAME            SEL INS UPD DEL IDX CHK ENABLE STATIC_POL POLICY_TYPE
---------------------- --- --- --- --- --- --- ------ ---------- -----------
EXP_RPT_VIEW_POLICY    YES NO  NO  NO  NO  NO  YES    NO         DYNAMIC
EXP_RPT_POLICY         NO   NO NO  YES NO  NO  YES    NO         DYNAMIC
EXP_LINE_POLICY        NO   NO NO  YES NO  NO  YES    NO         DYNAMIC
EXP_RPT_APPROVE_POLICY NO   NO YES NO  NO  NO  YES    NO         DYNAMIC
Disable and Re-enable a Policy SELECT object_name, policy_group, policy_name, enable
FROM user_policies
WHERE policy_name ='EXP_RPT_VIEW_POLICY';

OBJECT_NAME          POLICY_GROUP POLICY_NAME            ENABLE
-------------------- ------------ ---------------------- ------
EXP_REPORT_SUM_VIEW  SYS_DEFAULT  EXP_RPT_VIEW_POLICY    YES

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

OBJECT_NAME          POLICY_GROUP POLICY_NAME            ENABLE
-------------------- ------------ ---------------------- ------
EXP_REPORT_SUM_VIEW  SYS_DEFAULT  EXP_RPT_VIEW_POLICY    NO
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';

OBJECT_NAME          POLICY_GROUP POLICY_NAME            ENABLE
-------------------- ------------ ---------------------- ------
EXP_REPORT_SUM_VIEW  SYS_DEFAULT  EXP_RPT_VIEW_POLICY    YES
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_line', 'exp_line_policy');

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

SELECT policy_name
FROM user_policies;

no rows selected
 
Create Policy Groups and Access Policies
Create Policy Groups 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;

OBJECT_NAME          POLICY_GROUP COM INH
-------------------- ------------ --- ---
EXP_REPORT_SUM_VIEW  DEMO_GROUP1  NO  NO
EXP_REPORT           DEMO_GROUP1  NO  NO
EXP_LINE             DEMO_GROUP2  NO  NO
Create Grouped 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_line', 'demo_group2', 'policy3', USER, 'exp_security.empnumline_sec', 'DELETE');

exec dbms_rls.add_grouped_policy(USER, 'exp_report', 'demo_group1','policy4',  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 a 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 conn sys@pdbdev as sysdba

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/dan@pdbdev

col emp_id format a7
col cc format a4
col login_date format a30

SELECT *
FROM secdemo.ctx_audit;

SELECT table_name
FROM all_tables
WHERE owner = 'SECDEMO';

col view_name format a30

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 a20
col approval_date format a20

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

SELECT last_name
FROM secdemo.employee
WHERE employee_id IN (
  SELECT manager_id
  FROM secdemo.employee
  WHERE employee_id IN (
    SELECT employee_id FROM secdemo.exp_report_sum_view));
View data as employee Kyte conn secdemo/secdemo@pdbdev

SELECT * FROM ctx_audit;

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

conn kyte/tom@pdbdev

SELECT * FROM secdemo.ctx_audit;

SELECT * FROM secdemo.employee;

SELECT * FROM secdemo.exp_report_sum_view;

EXPLAIN PLAN FOR
SELECT * FROM secdemo.exp_report_sum_view;

SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
| Id | Operation                      | Name        | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |             |   32 |  1600 |     22 (10)| 00:00:01 |
|  1 |  HASH GROUP BY                 |             |   32 |  1600 |     22 (10)| 00:00:01 |
|* 2 |   HASH JOIN                    |             |   32 |  1600 |     21  (5)| 00:00:01 |
|  3 |    MERGE JOIN                  |             |   12 |   516 |     12  (9)| 00:00:01 |
|  4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |   32 |   352 |      2  (0)| 00:00:01 |
|  5 |      INDEX FULL SCAN           | PK_EMPLOYEE |   32 |       |      1  (0)| 00:00:01 |
|* 6 |     SORT JOIN                  |             |   12 |   384 |     10 (10)| 00:00:01 |
|  7 |      TABLE ACCESS FULL         | EXP_REPORT  |   12 |   384 |      9  (0)| 00:00:01 |
|  8 |    TABLE ACCESS FULL           | EXP_LINE    |   32 |   224 |      9  (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"."REPORT_ID"="L"."REPORT_ID")
6 - access("E"."EMPLOYEE_ID"="R"."EMPLOYEE_ID")
filter("E"."EMPLOYEE_ID"="R"."EMPLOYEE_ID")

Note
-----
- this is an adaptive plan


conn secdemo/secdemo@pdbdev

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

conn kyte/tom@pdbdev

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

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
| Id | Operation                       | Name        | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT                |             |    4 |   200 |      15 (7)| 00:00:01 |
|  1 |  HASH GROUP BY                  |             |    4 |   200 |      15 (7)| 00:00:01 |
|  2 |   NESTED LOOPS                  |             |    6 |   300 |      14 (0)| 00:00:01 |
|  3 |    NESTED LOOPS                 |             |    6 |   300 |      14 (0)| 00:00:01 |
|  4 |     NESTED LOOPS                |             |    2 |    86 |      10 (0)| 00:00:01 |
|  5 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |    1 |    11 |       1 (0)| 00:00:01 |
|* 6 |       INDEX UNIQUE SCAN         | PK_EMPLOYEE |    1 |       |       0 (0)| 00:00:01 |
|* 7 |      TABLE ACCESS FULL          | EXP_REPORT  |    2 |    64 |       9 (0)| 00:00:01 |
|* 8 |     INDEX RANGE SCAN            | PK_EXP_LINE |    3 |       |       1 (0)| 00:00:01 |
|  9 |    TABLE ACCESS BY INDEX ROWID  | EXP_LINE    |    3 |    21 |       2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."EMPLOYEE_ID"=TO_NUMBER(SYS_CONTEXT('exp_rpt','emp_number')))
7 - filter("R"."EMPLOYEE_ID"=TO_NUMBER(SYS_CONTEXT('exp_rpt','emp_number')))
8 - access("R"."REPORT_ID"="L"."REPORT_ID")
Note
-----
- this is an adaptive plan
 
Demo Clean-up
Dismantle the demo infrastructure conn secdemo/secdemo@pdbdev

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_group2', 'policy3');

exec dbms_rls.drop_grouped_policy(USER, 'exp_report', 'demo_group1', '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 sys@pdbdev as sysdba

drop user kyte cascade;

drop user morgan cascade;

drop user secdemo cascade;

col package format a25
col namespace format a25
col schema format a20

SELECT * FROM dba_context ORDER BY 1,2,3;

drop context exp_rpt;

Related Topics
Constraints
DBMS_RLS
DBMS_SESSION
INSERT Statements
Object Privileges
Packages
SELECT Statements
Tables
Users
Views

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2016 Daniel A. Morgan All Rights Reserved