I. EXPLANATION

Oracle Data Redaction is an advanced security method for redacting sensitive data from end-users. Oracle Data Redaction is a part of Oracle Advanced Security and it is a separately licensed Enterprise Edition option! This option redact data from end-users, don’t from system privileged users as SYS, SYSMAN.

II. DATA REDACTION METHODS

Full Redaction: This method is using for redact all the contents of the column data in a table. The redacted value returned zero(0) for NUMBER data type and returned for blank space for character data types.

Partial Redaction: We use this method when redacting a partion of column data.

Regular Expressions: If you want to redact specific data based on a search pattern (like phone numbers, email addresses, …) in a column data, you can use this method.

Random Redaction: With this method you can generate random values for column data for each time it is displayed.

III. DEMO

This demo is including examples for Oracle Data Redaction. Firstly, I will prepare database and then I will operate redaction policies. Finally I will quering redaction tables and dropping my policy.

A. PREPERATION

A.1. Creating a Role

With SYS user:

SQL> CREATE ROLE SUPERVISOR;
A.2. Creating a Test User and Grant Core Privileges to the User

With SYS user:

SQL> CREATE USER MRSLBKY IDENTIFIED BY MrslBkyPassword;
SQL> GRANT CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE TO MRSLBKY;
A.3. Grant the Role to the User

With SYS user:

SQL> GRANT SUPERVISOR TO MRSLBKY;
A.4. Cretaing a Redaction Privileged User

With SYS user:

SQL> GRANT CREATE PROCEDURE, CREATE ROLE, CREATE SESSION, INHERIT ANY PRIVILEGES, SELECT ANY DICTIONARY TO SEC_ADMIN IDENTIFIED BY SecAdminPassword;
SQL> GRANT EXECUTE ON SYS.DBMS_REDACT TO SEC_ADMIN;
A.5. Creating a Test Table, Inserting Data to the Table and Quering Data

With MRSLBKY user:

SQL> CREATE TABLE DATA_REDACTION_TABLE
     (
     	FULL_REDACT_COL varchar2(10),
     	PARTIAL_REDACT_COL varchar2(23), 
     	REGULAR_EXP_COL varchar2(30), 
     	RANDOM_REDACT_COL number
     )
     ;
				
SQL> INSERT INTO DATA_REDACTION_TABLE VALUES ('ABCDE Bank', '11111-22222-33333-44444', 'qwert@abcdebank.com', '12345');
SQL> INSERT INTO DATA_REDACTION_TABLE VALUES ('BCDEF Bank', '22222-33333-44444-55555', 'werty@bcdefbank.com', '23456');
SQL> INSERT INTO DATA_REDACTION_TABLE VALUES ('CDEFG Bank', '33333-44444-55555-66666', 'ertyu@cdefgbank.com', '34567');
SQL> INSERT INTO DATA_REDACTION_TABLE VALUES ('DEFGH Bank', '44444-55555-66666-77777', 'rtyui@defghbank.com', '45678');
SQL> INSERT INTO DATA_REDACTION_TABLE VALUES ('EFGHI Bank', '55555-66666-77777-88888', 'tyuio@efghibank.com', '56789');

SQL> COMMIT;

SQL> SELECT * FROM DATA_REDACTION_TABLE;

     FULL_REDACT_COL	PARTIAL_REDACT_COL		REGULAR_EXP_COL			RANDOM_REDACT_COL
     ---------------	------------------		---------------			-----------------
     ABCDE Bank		11111-22222-33333-44444		qwert@abcdebank.com		12345
     BCDEF Bank		22222-33333-44444-55555		werty@bcdefbank.com		23456
     CDEFG Bank		33333-44444-55555-66666		ertyu@cdefgbank.com		34567
     DEFGH Bank		44444-55555-66666-77777		rtyui@defghbank.com		45678
     EFGHI Bank		55555-66666-77777-88888		tyuio@efghibank.com		56789
A.6. Creating a Second User for Access Testing and Grant Privileges to the User

SQL> CREATE USER TSTUSR IDENTIFIED BY TstUsrPassword;
SQL> GRANT CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE TO TSTUSR;
SQL> GRANT SELECT ON MRSLBKY.DATA_REDACTION_TABLE TO TSTUSR;

With TSTUSR user:

SQL> SELECT * FROM MRSLBKY.DATA_REDACTION_TABLE;
  
     FULL_REDACT_COL	PARTIAL_REDACT_COL		REGULAR_EXP_COL			RANDOM_REDACT_COL
     ---------------	------------------		---------------			-----------------
     ABCDE Bank		11111-22222-33333-44444		qwert@abcdebank.com		12345
     BCDEF Bank		22222-33333-44444-55555		werty@bcdefbank.com		23456
     CDEFG Bank		33333-44444-55555-66666		ertyu@cdefgbank.com		34567
     DEFGH Bank		44444-55555-66666-77777		rtyui@defghbank.com		45678
     EFGHI Bank		55555-66666-77777-88888		tyuio@efghibank.com		56789

B. DATA REDACTION POLICY OPERATIONS

B.1. ADD POLICY

(Example for Full Redaction Method)

With SEC_ADMIN user:

SQL> BEGIN
     DBMS_REDACT.add_policy(
	object_schema => 'MRSLBKY',
	object_name   => 'DATA_REDACTION_TABLE',
	column_name   => 'FULL_REDACT_COL',
	policy_name   => 'DATA_REDACTION_POLICY',
	function_type => DBMS_REDACT.full,
	expression    => 'SYS_CONTEXT(''SYS_SESSION_ROLES'', ''SUPERVISOR'') = ''FALSE'''
     );
     END;
     /

With TSTUSR user:

SQL> SELECT * FROM MRSLBKY.DATA_REDACTION_TABLE;

     FULL_REDACT_COL	PARTIAL_REDACT_COL		REGULAR_EXP_COL			RANDOM_REDACT_COL
     ---------------	------------------		---------------			-----------------
			11111-22222-33333-44444		qwert@abcdebank.com		12345
			22222-33333-44444-55555		werty@bcdefbank.com		23456
			33333-44444-55555-66666		ertyu@cdefgbank.com		34567
			44444-55555-66666-77777		rtyui@defghbank.com		45678
			55555-66666-77777-88888		tyuio@efghibank.com		56789
B.2. ALTER POLICY

(Examples for Partial, Regular Expressions and Random Methods)

For Partial:

With SEC_ADMIN user:

SQL> BEGIN
     DBMS_REDACT.alter_policy(
	object_schema 		=> 'MRSLBKY',
	object_name   		=> 'DATA_REDACTION_TABLE',
	column_name   		=> 'PARTIAL_REDACT_COL',
	policy_name   		=> 'DATA_REDACTION_POLICY',
	action        		=> DBMS_REDACT.add_column,
	function_type 		=> DBMS_REDACT.partial,
	function_parameters	=> 'VVVVVFVVVVVFVVVVVFVVVVV,VVVVV-VVVVV-VVVVV-VVVVV,#,1,15'
     );
     END;
     /		

With TSTUSR user:

SQL> SELECT * FROM MRSLBKY.DATA_REDACTION_TABLE;

     FULL_REDACT_COL	PARTIAL_REDACT_COL		REGULAR_EXP_COL			RANDOM_REDACT_COL
     ---------------	------------------		---------------			-----------------
			#####-#####-#####-44444		qwert@abcdebank.com		12345
			#####-#####-#####-55555		werty@bcdefbank.com		23456
			#####-#####-#####-66666		ertyu@cdefgbank.com		34567
			#####-#####-#####-77777		rtyui@defghbank.com		45678
			#####-#####-#####-88888		tyuio@efghibank.com		56789

With SEC_ADMIN user:

SQL> BEGIN
     DBMS_REDACT.alter_policy(
	object_schema 		=> 'MRSLBKY',
	object_name   		=> 'DATA_REDACTION_TABLE',
	column_name   		=> 'PARTIAL_REDACT_COL',
	policy_name   		=> 'DATA_REDACTION_POLICY',
	action        		=> DBMS_REDACT.modify_column,
	function_type 		=> DBMS_REDACT.partial,
	function_parameters	=> 'VVVVVFVVVVVFVVVVVFVVVVV,VVVVV-VVVVV-VVVVV-VVVVV,$,1,15'
     );
     END;
     /		

With TSTUSR user:

SQL> SELECT * FROM MRSLBKY.DATA_REDACTION_TABLE;

     FULL_REDACT_COL	PARTIAL_REDACT_COL		REGULAR_EXP_COL			RANDOM_REDACT_COL
     ---------------	------------------		---------------			-----------------
			$$$$$-$$$$$-$$$$$-44444		qwert@abcdebank.com		12345
			$$$$$-$$$$$-$$$$$-55555		werty@bcdefbank.com		23456
			$$$$$-$$$$$-$$$$$-66666		ertyu@cdefgbank.com		34567
			$$$$$-$$$$$-$$$$$-77777		rtyui@defghbank.com		45678
			$$$$$-$$$$$-$$$$$-88888		tyuio@efghibank.com		56789		

For Regular Expressions:

With SEC_ADMIN user:

SQL> BEGIN
     DBMS_REDACT.alter_policy(
	object_schema 		=> 'MRSLBKY',
	object_name   		=> 'DATA_REDACTION_TABLE',
	column_name   		=> 'REGULAR_EXP_COL',
	policy_name   		=> 'DATA_REDACTION_POLICY',
	action        		=> DBMS_REDACT.add_column,
	function_type 		=> DBMS_REDACT.regexp,
	regexp_pattern          => '(.+)@(.+\.[A-Za-z]{2,4})',
	regexp_replace_string   => 'xxxxx@com'
     );
     END;
     /

With TSTUSR user:

SQL> SELECT * FROM MRSLBKY.DATA_REDACTION_TABLE;

     FULL_REDACT_COL	PARTIAL_REDACT_COL		REGULAR_EXP_COL		RANDOM_REDACT_COL
     ---------------	------------------		---------------		-----------------
			$$$$$-$$$$$-$$$$$-44444		xxxxx@com               12345
			$$$$$-$$$$$-$$$$$-55555		xxxxx@com		23456
			$$$$$-$$$$$-$$$$$-66666		xxxxx@com		34567
			$$$$$-$$$$$-$$$$$-77777		xxxxx@com		45678
			$$$$$-$$$$$-$$$$$-88888		xxxxx@com		56789

For Random:

With SEC_ADMIN user:

SQL> BEGIN
     DBMS_REDACT.alter_policy(
	object_schema 		=> 'MRSLBKY',
	object_name   		=> 'DATA_REDACTION_TABLE',
	column_name   		=> 'RANDOM_REDACT_COL',
	policy_name   		=> 'DATA_REDACTION_POLICY',
	action        		=> DBMS_REDACT.add_column,
	function_type 		=> DBMS_REDACT.random,
	expression	        => '1=1'
     );
     END;
     /

With TSTUSR user:

SQL> SELECT * FROM MRSLBKY.DATA_REDACTION_TABLE;

     FULL_REDACT_COL	PARTIAL_REDACT_COL		REGULAR_EXP_COL		RANDOM_REDACT_COL
     ---------------	------------------		---------------		-----------------
			$$$$$-$$$$$-$$$$$-44444		xxxxx@com		4168
			$$$$$-$$$$$-$$$$$-55555		xxxxx@com		16871
			$$$$$-$$$$$-$$$$$-66666		xxxxx@com		20667
			$$$$$-$$$$$-$$$$$-77777		xxxxx@com		22185
			$$$$$-$$$$$-$$$$$-88888		xxxxx@com		8105

B.3. DROP POLICY

With SEC_ADMIN user:

SQL> BEGIN
     DBMS_REDACT.drop_policy (
	object_schema => 'MRSLBKY',
	object_name   => 'DATA_REDACTION_TABLE',
	policy_name   => 'DATA_REDACTION_POLICY'
     );
     END;
     /

IV. REDACTION VIEWS

With SYS user:

SQL> SELECT * FROM REDACTION_COLUMNS;
SQL> SELECT * FROM REDACTION_POLICIES;

That’s all.

See you soon.

Oracle

ADVANCEDDATAREDUCTIONSECURITY