OraMatt: YABAOracle

Oracle 12c introduced A TON of new features and options.

One of the COOLEST (IMHO) is the Data Redaction packages.

For those not in the know here’s what the Data Redaction Packages do:
(http://docs.oracle.com/cd/E16655_01/network.121/e17729/redaction.htm#CJHCAAHG)
Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by low-privileged users or applications. You can redact column data by using one of the following methods:

Full redaction:
You redact all of the contents of the column data. The redacted value returned to the querying user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a blank space.

Partial redaction:
You redact a portion of the column data. For example, you can redact most of a Social Security number with asterisks (*), except for the last 4 digits.

Regular expressions:
You can use regular expressions to look for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only.

Random redaction:
The redacted data presented to the querying user appears as randomly generated values each time it is displayed, depending on the data type of the column.

No redaction:
This option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment.

Oracle Database applies the redaction at runtime, at the moment users attempt to access the data (that is, at query-execution time). This solution works well in a dynamic production system in which data is constantly changing. During the time that the data is being redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved.

Data redaction can help you to comply with industry regulations such as Payment Card Industry Data Security Standard (PCI DSS) and the Sarbanes-Oxley Act.

So I whipped up a quick/simple demo to show off some of the concepts…enjoy!

-- Create demo table
create table CUSTOMERS (
ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10),
NAME varchar2(50),
SS_NUMBER varchar2(50),
INDATE timestamp)
/

insert into customers (name,ss_number,indate) values ('matt','123-11-1234',systimestamp);
insert into customers (name,ss_number,indate) values ('bob','111-22-3333',systimestamp);
commit;

-- Create some roles for user access
-- Customer servcie manager...should see everything..including PII
create role customer_service_manager;
grant select,update,delete on customers to customer_service_manager;

-- Customer service rep...should NOT see the PII data
create role customer_service_rep;
grant select,update,delete on customers to customer_service_rep;

-- Grant the manager role to user Matt
grant customer_service_manager to matt;

-- Grant the rep role to Bob
grant customer_service_rep,connect to bob identified by bob;

-- Test the select as the dba
select * from customers;

-- Simple Full Redaction Policy
-- Reps CAN NOT see PII...so we redact it
BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema   => NULL,
   object_name     => 'CUSTOMERS',
   column_name     => 'SS_NUMBER',
   policy_name     => 'redact_full_ss_number',
   function_type   => DBMS_REDACT.FULL,
   expression      => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CUSTOMER_SERVICE_REP'') = ''TRUE''');
END;
/

-- List the Redaction Policies as dba
select policy_name,object_name,object_owner from REDACTION_POLICIES;

-- Test the select as a user
conn bob/bob@localhost/dw
SELECT sys_context('SYS_SESSION_ROLES', 'CUSTOMER_SERVICE_REP')
FROM dual;
select * from matt.customers;

-- Drop the policy as the dba
conn matt/matt@localhost/dw
BEGIN
 DBMS_REDACT.DROP_POLICY (
   object_schema        => 'MATT',
   object_name          => 'CUSTOMERS',
   policy_name			=> 'redact_full_ss_number');
END;
/

-- Create a view to see if that 'side steps' the Redaction Policy
create view view_customers as select * from customers;
grant select on view_customers to customer_service_manager,customer_service_rep;

-- Test the select on the base table as the dba
select * from customers;

-- Test the select on the view as the dba
select * from view_customers;

-- Test the select on the base table as the rep
conn bob/bob@localhost/dw
select * from matt.customers;

-- Test the select on the view as the rep
conn bob/bob@localhost/dw
select * from matt.view_customers;

-- Simple Random Redaction Policy
-- Reps CAN NOT see PII...so we randomize it so they know data is there
conn matt/matt@localhost/dw
BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema   => NULL,
   object_name     => 'CUSTOMERS',
   column_name     => 'SS_NUMBER',
   policy_name     => 'redact_random_ss_number',
   function_type   => DBMS_REDACT.RANDOM,
   expression      => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''CUSTOMER_SERVICE_REP'') = ''TRUE''');
END;
/

-- Test the select on the base table as the rep
conn bob/bob@localhost/dw
select * from matt.customers;

-- Test the select on the view as the rep
conn bob/bob@localhost/dw
select * from matt.view_customers;

-- Drop the policy as the dba
conn matt/matt@localhost/dw
BEGIN
 DBMS_REDACT.DROP_POLICY (
   object_schema        => 'MATT',
   object_name          => 'CUSTOMERS',
   policy_name			=> 'redact_random_ss_number');
END;
/

-- List the Redaction Policies as dba
select policy_name,object_name,object_owner from REDACTION_POLICIES;

-- Drop users,policies, & objects
conn matt/matt@localhost/dw

-- Drop the view
drop view view_customers;

-- Make sure the Redaction Policies are gone
BEGIN
 DBMS_REDACT.DROP_POLICY (
   object_schema        => 'MATT',
   object_name          => 'CUSTOMERS',
   policy_name			=> 'redact_random_ss_number');
END;
/

BEGIN
 DBMS_REDACT.DROP_POLICY (
   object_schema        => 'MATT',
   object_name          => 'CUSTOMERS',
   policy_name			=> 'redact_full_ss_number');
END;
/

-- Revoke & drop the roles
revoke customer_service_manager from matt;
revoke customer_service_rep from bob;

drop role customer_service_rep;
drop role customer_service_manager;

drop user bob cascade;

-- Drop the base table
drop table customers purge;