-- Data Redaction Examples Nov 2018
SET ECHO ON
SET SERVEROUTPUT ON
ALTER TABLE HR.EMPLOYEES ADD (CREDIT_CARD VARCHAR2(20));
@@Credit_Card_Numbers_for_HR_Employees.sql
-- Query dictionary table: REDACTION_POLICIES (table should be empty as no policies were defined yet)
-- Full redaction on HR.EMPLOYEES(COMMISSION_PCT)
BEGIN
	DBMS_REDACT.ADD_POLICY	(
							object_schema => 'HR',
							object_name => 'EMPLOYEES',
							column_name => 'COMMISSION_PCT',
							policy_name => 'REDACT_COM_PCT_FULL',
							function_type => DBMS_REDACT.FULL,
							expression => '1=1'
							);
END;
/

-- Drop policy
BEGIN
	DBMS_REDACT.DROP_POLICY	(
							policy_name => 'REDACT_COM_PCT_FULL',
							object_schema => 'HR',
							object_name => 'EMPLOYEES'
							);
END;
/

-- Query REDACTION_VALUES_FOR_TYPE_FULL dictionary table
SELECT	number_value,varchar_value,date_value,timestamp_value
FROM 	redaction_values_for_type_full;

-- Change full redaction character value to N (instead of single space ' ')
BEGIN
	-- DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES(varchar_val=>' ');
	DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES(varchar_val=>'N');
	-- Need to restart the database
END;
/

-- Partial redaction on HR.EMPLOYEES(CREDIT_CARD)
BEGIN
	DBMS_REDACT.ADD_POLICY	(
							object_schema => 'HR',
							object_name => 'EMPLOYEES',
							column_name => 'CREDIT_CARD',
							policy_name => 'REDACT_CREDIT_CARD_PARTIAL',
							function_type => DBMS_REDACT.PARTIAL,
							function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',
							expression => '1=1');
END;
/

-- Query data dictionary:
COL OBJECT_OWNER FOR A30
COL OBJECT_NAME FOR A30
COL POLICY_NAME FOR A30
COL EXPRESSION FOR A30
-- Query redaction policies
SELECT	object_owner,object_name,policy_name,expression,enable 
FROM	redaction_policies;
-- Query redaction columns
SELECT	object_owner,object_name,column_name,function_type,function_parameters 
FROM	redaction_columns;

-- Drop policy
EXEC DBMS_REDACT.DROP_POLICY('HR','EMPLOYEES','REDACT_CREDIT_CARD_PARTIAL');

-- Partial redaction on HR.EMPLOYEES(CREDIT_CARD) using Partial Fixed Character Redaction Shortcuts
BEGIN
	DBMS_REDACT.ADD_POLICY	(
							object_schema => 'HR',
							object_name => 'EMPLOYEES',
							column_name => 'CREDIT_CARD',
							policy_name => 'REDACT_CREDIT_CARD_PARTIAL',
							function_type => DBMS_REDACT.PARTIAL,
							function_parameters => DBMS_REDACT.REDACT_CCN16_F12,
							expression => '1=1');
END;
/

-- Query redaction policies
SELECT	object_owner,object_name,policy_name,expression,enable 
FROM	redaction_policies;
-- Query redaction columns
SELECT	object_owner,object_name,column_name,function_type,function_parameters 
FROM	redaction_columns;

-- Alter policy, add partial redaction on HR.EMPLOYEES(HIRE_DATE)
BEGIN
	DBMS_REDACT.ALTER_POLICY(
							object_schema => 'HR',
							object_name => 'EMPLOYEES',
							policy_name => 'REDACT_CREDIT_CARD_PARTIAL',
							action => DBMS_REDACT.ADD_COLUMN,
							column_name => 'HIRE_DATE',
							function_type => DBMS_REDACT.PARTIAL,
							function_parameters => 'm02Dy2016'
							);
END;
/

-- Query redaction columns
SELECT	object_owner,object_name,column_name,function_type,function_parameters 
FROM	redaction_columns;

-- Drop policy
BEGIN
	DBMS_REDACT.DROP_POLICY	(
							policy_name => 'REDACT_CREDIT_CARD_PARTIAL',
							object_schema => 'HR',
							object_name => 'EMPLOYEES'
							);
END;
/

-- Redaction using random on HR.EMPLOYEES(SALARY)
BEGIN
	DBMS_REDACT.ADD_POLICY	(
							object_schema => 'HR',
							object_name => 'EMPLOYEES',
							column_name => 'SALARY',
							policy_name => 'REDACT_SALARY_RANDOM',
							function_type => DBMS_REDACT.RANDOM,
							expression => '1=1'
							);
END;
/

-- Alter policy, add random redaction on HR.EMPLOYEES(HIRE_DATE)
BEGIN
	DBMS_REDACT.ALTER_POLICY(
							object_schema => 'HR',
							object_name => 'EMPLOYEES',
							policy_name => 'REDACT_SALARY_RANDOM',
							action => DBMS_REDACT.ADD_COLUMN,
							column_name => 'HIRE_DATE',
							function_type => DBMS_REDACT.RANDOM,
							expression => '1=1'
							);
END;
/

-- Alter policy to exclude SQL*Plus from redaction policy
BEGIN
	DBMS_REDACT.ALTER_POLICY(
							object_schema => 'HR',
							object_name => 'EMPLOYEES',
							policy_name => 'REDACT_SALARY_RANDOM',
							action => DBMS_REDACT.MODIFY_EXPRESSION,
							expression => 'SYS_CONTEXT(''USERENV'',''MODULE'') != ''SQL*Plus''');
END;
/

-- Drop policy
BEGIN
	DBMS_REDACT.DROP_POLICY	(
							policy_name => 'REDACT_SALARY_RANDOM',
							object_schema => 'HR',
							object_name => 'EMPLOYEES'
							);
END;
/

-- Redaction using REGEXP on HR.EMPLOYEES(EMAIL)
BEGIN
	DBMS_REDACT.ADD_POLICY	(
							object_schema => 'HR',
							object_name => 'EMPLOYEES',
							column_name => 'EMAIL',
							policy_name => 'REDACT_EMAIL_REGEXP',
							function_type => DBMS_REDACT.REGEXP,
							function_parameters => NULL,
							expression => '1=1',
							regexp_pattern => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,
							regexp_replace_string => DBMS_REDACT.RE_REDACT_EMAIL_DOMAIN,
							regexp_position => DBMS_REDACT.RE_BEGINNING,
							regexp_occurrence => DBMS_REDACT.RE_FIRST);
END;
/

-- Drop policy
BEGIN
	DBMS_REDACT.DROP_POLICY	(
							policy_name => 'REDACT_EMAIL_REGEXP',
							object_schema => 'HR',
							object_name => 'EMPLOYEES'
							);
END;
/

-- Redaction on employees salary
BEGIN
	DBMS_REDACT.ADD_POLICY(
		object_schema => 'HR',
		object_name => 'EMPLOYEES',
		column_name => 'SALARY',
		column_description => 'Employees Salary',
		policy_name => 'REDACT_EMP_SAL',
		policy_description => 'Partially redacts the employee salary column',
		function_type => DBMS_REDACT.PARTIAL,
		function_parameters => '9,1,5',
		expression => '1=1');
END;
/

-- Query employees salary
SELECT	salary
FROM	employees
WHERE 	rownum <= 20;

SET SERVEROUTPUT ON

-- Hacking data redaction
DECLARE
	TYPE rec_type IS RECORD
	(emp_name	VARCHAR2(100),
	 emp_sal	NUMBER);
	TYPE tbl_type IS TABLE OF rec_type INDEX BY PLS_INTEGER;
	tbl	tbl_type;
BEGIN
	FOR i IN 1..99999
	LOOP
		BEGIN
			SELECT first_name||' '||last_name,i INTO tbl(i) FROM HR.EMPLOYEES WHERE SALARY = i AND ROWNUM <= 1;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN CONTINUE;
		END;
	END LOOP;
	FOR i IN tbl.FIRST..tbl.LAST LOOP
		IF tbl.EXISTS(i) THEN
			DBMS_OUTPUT.PUT_LINE(tbl(i).emp_name||' earns: '||tbl(i).emp_sal);
		END IF;
	END LOOP;
END;
/

-- Drop policy
BEGIN
	DBMS_REDACT.DROP_POLICY	(
							policy_name => 'REDACT_EMP_SAL',
							object_schema => 'HR',
							object_name => 'EMPLOYEES'
							);
END;
/

-- End of examples
