A. 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.
B. DATA REDACTION METHODS
1. 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.
2. Partial Redaction: We use this method when redacting a partion of column data.
3. 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.
4. Random Redaction: With this method you can generate random values for column data for each time it is displayed.
C. 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.
C.1. PREPERATION
C.1.1. Creating a Role
With SYS user:
SQL> CREATE ROLE SUPERVISOR;C.1.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;C.1.3. Grant the Role to the User
With SYS user:
SQL> GRANT SUPERVISOR TO MRSLBKY;C.1.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;C.1.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
C.1.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
C.2. DATA REDACTION POLICY OPERATIONS
C.2.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
C.2.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 56789For 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
C.2.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;
/
D. REDACTION VIEWS
With SYS user:
SQL> SELECT * FROM REDACTION_COLUMNS;
SQL> SELECT * FROM REDACTION_POLICIES;
That’s all.
See you soon.