Oracle Fine Grained Data Security
Versions 18c - 20c

General
Fine Grained Data Security is the result of bringing together multiple security technologies to accomplish a substantial improvement in data security.

Why did I name the first user created "Experian"? I believe it is referred to as "Fair Use" and "Performance Art".
No one will ever confuse DBSecWorx with Experian.
We know how to protect data and databases.
 
Preparation as DBA
Create 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 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 of 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 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
Drop 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