Oracle Fine Grained Data Security Demo
Version 21c

Preparation As DBA
Create Demo Application Owner conn sys@pdbdev as sysdba

CREATE USER experian
IDENTIFIED BY E1x2p3e4r5i6a7n$
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
PROFILE ora_stig_profile
QUOTA 0 ON system
QUOTA 0 ON sysaux
QUOTA 100M ON uwdata;

ALTER USER experian ENABLE EDITIONS;

GRANT create session TO experian;
GRANT create table TO experian;

CREATE USER secaccess
IDENTIFIED BY S1e2c3a4c5c6e7s8s$
PROFILE ora_stig_profile;
-- note secaccess gets no default or temporary tablespace

GRANT create session TO secaccess;
GRANT create any context TO secaccess;
GRANT create procedure TO secaccess;
GRANT create type TO secaccess;
GRANT create view TO secaccess;
Create Application's Proxy Users conn sys@pdbdev as sysdba

CREATE USER webcust
IDENTIFIED BY webcust
TEMPORARY TABLESPACE temp
PROFILE DEFAULT;

CREATE USER bankcust
IDENTIFIED BY bankcust
TEMPORARY TABLESPACE temp
PROFILE DEFAULT;
-- note the application users webcust and bankcust get no tablespace privs
-- in production I would never grant anyone the DEFAULT profile but this is a demo

-- this is the only system privilege webcust gets
GRANT create session TO webcust;
GRANT create session TO bankcust;

-- the following is the proxy user auditing an connection
AUDIT CONNECT BY webcust ON BEHALF OF secaccess;
ALTER USER secaccess GRANT CONNECT THROUGH webcust;

AUDIT CONNECT BY bankcust ON BEHALF OF secaccess;
ALTER USER secaccess GRANT CONNECT THROUGH bankcust;
Create audit table and associated after logon trigger to set application information into v$session conn sys@pdbdev as sysdba

-- create login audit table
CREATE TABLE experian.app_audit (
login_date  TIMESTAMP WITH LOCAL TIME ZONE,
user_name   VARCHAR2(30),
proxy_name  VARCHAR2(30),
schema_name VARCHAR2(30));

GRANT insert ON experian.app_audit TO webcust, bankcust;

-- create after logon trigger
CREATE OR REPLACE TRIGGER audit_app_cnx
AFTER LOGON
ON DATABASE
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 cur_user user_users.username%TYPE := sys_context('USERENV', 'CURRENT_USER');
BEGIN
  dbms_application_info.set_client_info(cur_user);

  INSERT INTO experian.app_audit
  (login_date, user_name, proxy_name, schema_name)
  VALUES
  (SYSTIMESTAMP, cur_user, sys_context('USERENV', 'PROXY_USER'), sys_context('USERENV', 'CURRENT_SCHEMA'));
  COMMIT;

  -- set contexts here
END audit_app_cnx;
/
 
Preparation As Application Owner
Create Demo Tables and Constraints conn experian/E1x2p3e4r5i6a7n$@pdbdev

CREATE TABLE credit_info_base (
ssn          VARCHAR2(11),
cc_number    VARCHAR2(19),
last_name    VARCHAR2(15),
first_name   VARCHAR2(15),
dob          DATE,
gender       VARCHAR2(1),
cc_exp_date  VARCHAR2(4),
cc_sec_code  VARCHAR2(4))
PCTFREE 0
TABLESPACE uwdata;

ALTER TABLE credit_info_base
ADD CONSTRAINT pk_credit_info_base
PRIMARY KEY (ssn, cc_number);
Grant Minimum Required Object Privileges conn experian/E1x2p3e4r5i6a7n$@pdbdev

GRANT select ON experian.credit_info_base TO secaccess;
Insert Records Into CREDIT_INFO_BASE conn experian/E1x2p3e4r5i6a7n$@pdbdev

INSERT INTO credit_info_base VALUES ('545-98-1234', '3567-0123-4567-8901', 'Ellison', 'Larry', TO_DATE('01-JAN-1950'), 'M', '0121', '842');
INSERT INTO credit_info_base VALUES ('618-45-2345', '3678-1234-4567-8902', 'Catz', 'Safra', TO_DATE('01-FEB-1950'), 'F', '0221', '456');
INSERT INTO credit_info_base VALUES ('795-61-3456', '3789-2345-4567-8903', 'Hurd', 'Mark', TO_DATE('01-MAR-1950'), 'M', '0321', '8042');
INSERT INTO credit_info_base VALUES ('214-79-4567', '3890-3456-4567-8904', 'Kurian', 'Thomas', TO_DATE('01-APR-1950'), 'M', '0421', '890');
INSERT INTO credit_info_base VALUES ('545-98-1234', '3901-4567-4567-8905', 'Lewis', 'Jonathan', TO_DATE('01-MAY-1950'), 'M', '0322', '215');
INSERT INTO credit_info_base VALUES ('545-98-1234', '3012-5678-4567-8906', 'Hall', 'Tim', TO_DATE('01-JUN-1950'), 'M', '0521', '678');
INSERT INTO credit_info_base VALUES ('773-31-7890', '3123-6789-4567-8907', 'Forbrich', 'Hans', TO_DATE('01-JUL-1950'), 'M', '0621', '133');
INSERT INTO credit_info_base VALUES ('888-73-8901', '3234-7890-4567-8908', 'Small', 'Caleb', TO_DATE('01-AUG-1950'), 'M', '0721', '4052');
INSERT INTO credit_info_base VALUES ('510-85-9012', '3345-8901-4567-8909', 'Havemeyer', 'Tara', TO_DATE('01-SEP-1957'), 'F', '0821', '938');
INSERT INTO credit_info_base VALUES ('915-94-0123', '3456-9012-4567-8910', 'Lofstrom', 'Helen', TO_DATE('01-OCT-1958'), 'F', '0921', '826');

INSERT INTO credit_info_base VALUES ('545-98-1234', '4567-0123-4567-8901', 'Ellison', 'Larry', TO_DATE('01-NOV-1950'), 'M', '1021', '591');
INSERT INTO credit_info_base VALUES ('618-45-2345', '4678-1234-4567-8902', 'Catz', 'Safra', TO_DATE('01-DEC-1950'), 'F', '1121', '642');
INSERT INTO credit_info_base VALUES ('795-61-3456', '4789-2345-4567-8903', 'Hurd', 'Mark', TO_DATE('01-JAN-1955'), 'M', '1221', '763');
INSERT INTO credit_info_base VALUES ('214-79-4567', '4890-3456-4567-8904', 'Kurian', 'Thomas', TO_DATE('01-FEB-1955'), 'M', '0122', '274');
INSERT INTO credit_info_base VALUES ('442-21-5678', '4901-4567-4567-8905', 'Lewis', 'Jonathan', TO_DATE('01-MAR-1955'), 'M', '0222', '425');
INSERT INTO credit_info_base VALUES ('545-98-1234', '4012-5678-4567-8906', 'Hall', 'Tim', TO_DATE('01-APR-1955'), 'M', '0322', '346');
INSERT INTO credit_info_base VALUES ('545-98-1234', '4123-6789-4567-8907', 'Forbrich', 'Hans', TO_DATE('01-MAY-1955'), 'M', '0422', '737');
INSERT INTO credit_info_base VALUES ('545-98-1234', '4234-7890-4567-8908', 'Small', 'Caleb', TO_DATE('01-JUN-1955'), 'M', '0522', '488');
INSERT INTO credit_info_base VALUES ('510-85-9012', '4345-8901-4567-8909', 'Havemeyer', 'Tara', TO_DATE('01-JUL-1955'), 'F', '0622', '495');
INSERT INTO credit_info_base VALUES ('545-98-1234', '4456-9012-4567-8910', 'Lofstrom', 'Helen', TO_DATE('01-AUG-1955'), 'F', '0722', '594');

INSERT INTO credit_info_base VALUES ('545-98-1234', '5567-0123-4567-8901', 'Ellison', 'Larry', TO_DATE('01-SEP-1955'), 'M', '0822', '545');
INSERT INTO credit_info_base VALUES ('618-45-2345', '5678-1234-4567-8902', 'Catz', 'Safra', TO_DATE('01-OCT-1955'), 'F', '0922', '981');
INSERT INTO credit_info_base VALUES ('795-61-3457', '5789-2345-4567-8903', 'Morgan', 'Daniel', TO_DATE('01-NOV-1955'), 'M', '1022', '234');
INSERT INTO credit_info_base VALUES ('214-79-4567', '5890-3456-4567-8904', 'Kurian', 'Thomas', TO_DATE('01-DEC-1955'), 'M', '1122', '678');
INSERT INTO credit_info_base VALUES ('545-98-1234', '5901-4567-4567-8905', 'Lewis', 'Jonathan', TO_DATE('01-JAN-1960'), 'M', '1222', '901');
INSERT INTO credit_info_base VALUES ('545-98-1234', '5012-5678-4567-8906', 'Hall', 'Tim', TO_DATE('02-FEB-1960'), 'M', '0123', '618');
INSERT INTO credit_info_base VALUES ('545-98-1234', '5123-6789-4567-8907', 'Forbrich', 'Hans', TO_DATE('03-MAR-1960'), 'M', '0223', '452');
INSERT INTO credit_info_base VALUES ('545-98-1234', '5234-7890-4567-8908', 'Small', 'Caleb', TO_DATE('04-APR-1960'), 'M', '0323', '345');
INSERT INTO credit_info_base VALUES ('510-85-9012', '5345-8901-4567-8909', 'Havemeyer', 'Tara', TO_DATE('05-MAY-1960'), 'F', '0423', '567');
INSERT INTO credit_info_base VALUES ('915-94-0123', '5456-9012-4567-8910', 'Lofstrom', 'Helen', TO_DATE('06-JUN-1960'), 'F', '0523', '822');
COMMIT;

SELECT * FROM credit_info_base;

exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);
 
Preparation as Security Layer
Create contexts and package for context setting conn secaccess/S1e2c3a4c5c6e7s8s$@pdbdev

CREATE OR REPLACE PACKAGE ciprep_ctx AUTHID DEFINER IS
 PROCEDURE set_ctx(ssn_in IN VARCHAR2);
END ciprep_ctx;
/

CREATE OR REPLACE PACKAGE BODY ciprep_ctx IS
 PROCEDURE set_ctx(ssn_in IN VARCHAR2) IS
  BEGIN
    dbms_session.set_context('ci_env', 'ssn_ctx', ssn_in);
 END set_ctx;
END ciprep_ctx;
/

SELECT object_name, object_type
FROM user_objects;

CREATE OR REPLACE CONTEXT ci_env USING secaccess.ciprep_ctx;

SELECT object_name, object_type
FROM user_objects;
Create The Policy Functions Package conn secaccess/S1e2c3a4c5c6e7s8s$@pdbdev

CREATE OR REPLACE FORCE EDITIONABLE VIEW "SECACCESS"."CREDIT_INFO" (
"SSN","CC_NUMBER","LAST_NAME","FIRST_NAME","DOB","GENDER","CC_EXP_DATE","CC_SEC_CODE") AS
SELECT "SSN","CC_NUMBER","LAST_NAME","FIRST_NAME","DOB","GENDER","CC_EXP_DATE","CC_SEC_CODE"
FROM experian.credit_info_base;

SELECT object_name, object_type
FROM user_objects;

CREATE OR REPLACE TYPE credit_info_type AUTHID DEFINER AS OBJECT(
ssn         VARCHAR2(11),
cc_number   VARCHAR2(19),
last_name   VARCHAR2(15),
first_name  VARCHAR2(15),
dob         DATE,
gender      VARCHAR2(1),
cc_exp_date VARCHAR2(4),
cc_sec_code VARCHAR2(4));
/

CREATE OR REPLACE TYPE credit_info_TypeSet AS TABLE OF credit_info_type;
/

CREATE OR REPLACE PACKAGE refcur_pkg AUTHID DEFINER IS
 TYPE refcur_t IS REF CURSOR RETURN credit_info%ROWTYPE;
END refcur_pkg;
/

CREATE OR REPLACE FUNCTION rci(p refcur_pkg.refcur_t)
RETURN credit_info_TypeSet
PIPELINED AUTHID DEFINER IS
 in_rec    p%ROWTYPE;
 out_rec   credit_info_type := credit_info_type(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
 cntr      PLS_INTEGER := 0;
 cur_limit PLS_INTEGER;
 cur_match VARCHAR2(19);
BEGIN
  cur_match := (sys_context('ci_env', 'ssn_ctx'));
  IF sys_context('USERENV', 'PROXY_USER') = 'WEBCUST' THEN
    cur_limit := 3;
  ELSIF sys_context('USERENV', 'PROXY_USER') = 'BANKCUST' THEN
    cur_limit := 12;
  ELSIF sys_context('USERENV', 'CURRENT_USER') = 'SECACCESS' THEN
    cur_limit := 999999999;
  ELSE
    cur_limit := 0;
  END IF;
  LOOP
    FETCH p INTO in_rec;
    EXIT WHEN p%NOTFOUND;

    IF in_rec.ssn = cur_match THEN
      cntr := cntr + 1;
      out_rec.ssn := in_rec.ssn;
      out_rec.cc_number := in_rec.cc_number;
      out_rec.last_name := in_rec.last_name;
      out_rec.first_name := in_rec.first_name;
      out_rec.dob := in_rec.dob;
      out_rec.gender := in_rec.gender;
      out_rec.cc_exp_date := in_rec.cc_exp_date;
      out_rec.cc_sec_code := in_rec.cc_sec_code;
      PIPE ROW(out_rec);
    END IF;
    IF cntr >= cur_limit THEN
      EXIT;
    END IF;
  END LOOP;
  CLOSE p;
  RETURN;
END rci;
/

CREATE OR REPLACE VIEW rciv AS
SELECT * FROM TABLE(rci(CURSOR(SELECT * FROM credit_info)));

SELECT object_name, object_type, status
FROM user_objects
ORDER BY 2,1;
 
Run Demo
Test As WebCust and BankCust conn webcust[SECACCESS]/webcust@pdbdev

col grantee format a15
col grantor format a15
col owner format a15
col privilege format a20
col table_name format a20
col type format a10

SELECT * FROM user_role_privs;
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;

exec ciprep_ctx.set_ctx('545-98-1234');
SELECT * FROM rciv;

exec ciprep_ctx.set_ctx('618-45-2345');
SELECT * FROM rciv;

exec ciprep_ctx.set_ctx('795-61-3457');
SELECT * FROM rciv;

SELECT * FROM rciv;

conn bankcust[SECACCESS]/bankcust@pdbdev

col grantee format a15
col grantor format a15
col owner format a15
col privilege format a20
col table_name format a20
col type format a10

SELECT * FROM user_role_privs;
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;

exec ciprep_ctx.set_ctx('545-98-1234');
SELECT * FROM rciv;

exec ciprep_ctx.set_ctx('618-45-2345');
SELECT * FROM rciv;

exec ciprep_ctx.set_ctx('795-61-3457');
SELECT * FROM rciv;
Test As BankCust conn webcust[SECACCESS]/webcust@pdbdev

exec ciprep_ctx.set_ctx('545-98-1234');
set autotrace on
SELECT * FROM rciv;

Execution Plan
----------------------------
Plan hash value: 3911557982
----------------------------------------------------------------------------------------
| Id | Operation                           | Name             | Rows|Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                  | 8168| 438K|   29    (0)|
|  1 |  VIEW                               | RCIV             | 8168| 438K|   29    (0)|
|  2 |   VIEW                              |                  | 8168| 462K|   29    (0)|
|  3 |    COLLECTION ITERATOR PICKLER FETCH| RCI              | 8168|     |   29    (0)|
|  4 |     VIEW                            | CREDIT_INFO      |   30| 1650|    9    (0)|
|  5 |      TABLE ACCESS FULL              | CREDIT_INFO_BASE |   30| 1950|    9    (0)|
----------------------------------------------------------------------------------------

Statistics
--------------------------------------------
  10 recursive calls
   0 db block gets
  21 consistent gets
   0 physical reads
   0 redo size
1260 bytes sent via SQL*Net to client
 607 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   3 rows processed

conn bankcust[SECACCESS]/bankcust@pdbdev

exec ciprep_ctx.set_ctx('545-98-1234');
set autotrace on
SELECT * FROM rciv;

Execution Plan
----------------------------
Plan hash value: 3911557982
----------------------------------------------------------------------------------------
| Id | Operation                           | Name             | Rows|Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                  | 8168| 438K|   29    (0)|
|  1 |  VIEW                               | RCIV             | 8168| 438K|   29    (0)|
|  2 |   VIEW                              |                  | 8168| 462K|   29    (0)|
|  3 |    COLLECTION ITERATOR PICKLER FETCH| RCI              | 8168|     |   29    (0)|
|  4 |     VIEW                            | CREDIT_INFO      |   30| 1650|    9    (0)|
|  5 |      TABLE ACCESS FULL              | CREDIT_INFO_BASE |   30| 1950|    9    (0)|
----------------------------------------------------------------------------------------

Statistics
--------------------------------------------
  31 recursive calls
   0 db block gets
  42 consistent gets
   0 physical reads
   0 redo size
1758 bytes sent via SQL*Net to client
 607 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
  12 rows processed
 
Demo Clean-up
Dismantle the demo infrastructure conn sys@pdbdev as sysdba

DROP USER webcust CASCADE;

DROP USER bankcust CASCADE;

DROP USER secaccess CASCADE;

DROP USER experian CASCADE;

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