Oracle Data Redaction

Oracle Data Redaction

Oracle Database 12c introduced a new feature related to Advanced Security option which enables the protection of data shown to the user in real-time, without requiring changes to the application. The feature is called Data-redaction.
During Data redaction the stored data remain unchanged, while the data to be displayed is transformed on-the-fly before leaving the database.
Data redaction is different from Oracle Data Masking which has been available since version 11g.
How to use the feature:
We can create redaction policies which specify conditions that must be met before the data gets redacted and returned to the user. During the definition of such policies, the DBA can specify which columns and the type of protection that must be applied.

There is an Oracle package called DBMS_REDACT, used to create protection rules. The package contains six procedures(APIs) to manage the rules and to provide the default value for full redaction policy.
DBMS_REDACT.ADD_POLICY       To create a new policy to a table.
DBMS_REDACT.ALTER_POLICY     To make some changes to an existing policy.
DBMS_REDACT.DISABLE_POLICY   To disables an existing policy.
DBMS_REDACT.ENABLE_POLICY    To enables an existing policy.
DBMS_REDACT.DROP_POLICY      To drop an existing policy.
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES   To provide the default value for full redaction policy.
We can protect data at the column level using below redaction type(function_type in APIs):
No redaction – Allows testing the inner workings of redaction policies, with no effect on the results of currently running queries. This is widely used during the testing phase of redaction policies that will eventually find their way to production environments.
Full redaction – All content of the column is protected and the type of value returned depends on the data type of the column. For numeric columns, the value zero will be returned. For columns of type character, space will be returned. This setting can be changed at the database level.
Partial redaction – Only part of the information is changed. For example, the first digits of the credit card number are replaced by asterisks.
Random redaction – Returned values are random; each time a query is executed, the displayed data will be different.
Regular expressions – You can use regular expressions to search for patterns of data that must be protected.

Syntax:
DBMS_REDACT.ADD_POLICY:
DBMS_REDACT.ADD_POLICY(
object_schema          IN VARCHAR2       := NULL,
object_name            IN VARCHAR2,
policy_name            IN VARCHAR2,
policy_description     IN VARCHAR2       := NULL,
column_name            IN VARCHAR2       := NULL,
column_description     IN VARCHAR2       := NULL,
function_type          IN BINARY_INTEGER := dbms_redact.full,
function_parameters    IN VARCHAR2       := NULL,
expression             IN VARCHAR2,
enable                 IN BOOLEAN        := TRUE,
regexp_pattern         IN VARCHAR2       := NULL,
regexp_replace_string  IN VARCHAR2       := NULL,
regexp_position        IN BINARY_INTEGER := 1,
regexp_occurrence      IN BINARY_INTEGER := 0,
regexp_match_parameter IN VARCHAR2       := NULL);
DBMS_REDACT.ALTER_POLICY:
DBMS_REDACT.ALTER_POLICY(
object_schema          IN VARCHAR2       := NULL,
object_name            IN VARCHAR2,
policy_name            IN VARCHAR2,
action                 IN BINARY_INTEGER := dbms_redact.ADD_COLUMN,
column_name            IN VARCHAR2       := NULL,
function_type          IN BINARY_INTEGER := dbms_redact.FULL,
function_parameters    IN VARCHAR2       := NULL,
expression             IN VARCHAR2       := NULL,
regexp_pattern         IN VARCHAR2       := NULL,
regexp_replace_string  IN VARCHAR2       := NULL,
regexp_position        IN BINARY_INTEGER := 1,
regexp_occurrence      IN BINARY_INTEGER := 0,
regexp_match_parameter IN VARCHAR2       := NULL,
policy_description     IN VARCHAR2       := NULL,
column_description     IN VARCHAR2       := NULL);
DBMS_REDACT.DISABLE_POLICY:
DBMS_REDACT.DISABLE_POLICY(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2);
DBMS_REDACT.ENABLE_POLICY:
DBMS_REDACT.ENABLE_POLICY(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2);
DBMS_REDACT.DROP_POLICY:
DBMS_REDACT.DROP_POLICY(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2);
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES:
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES(
number_val    IN NUMBER        := NULL,
binfloat_val  IN BINARY_FLOAT  := NULL,
bindouble_val IN BINARY_DOUBLE := NULL,
char_val      IN CHAR          := NULL,
varchar_val   IN VARCHAR2      := NULL,
nchar_val     IN NCHAR         := NULL,
nvarchar_val  IN NVARCHAR2     := NULL,
datecol_val   IN DATE          := NULL,
ts_val        IN TIMESTAMP     := NULL,
tswtz_val     IN TIMESTAMP WITH TIME ZONE := NULL,
blob_val      IN BLOB          := NULL,
clob_val      IN CLOB          := NULL,
nclob_val     IN NCLOB         := NULL);
Example on DMBS_REDACT Package:
Data source:
CREATE USER base_user IDENTIFIED BY base;
GRANT UNLIMITED TABLESPACE TO base_user;
GRANT DBA TO base_user;

conn base_user/base
CREATE TABLE customer_details_tab (
customer_id NUMBER       NOT NULL,
card_no NUMBER       NOT NULL,
card_string VARCHAR2(19) NOT NULL,
expiry_date DATE         NOT NULL,
email VARCHAR2(90) NOT NULL,
us_phone    VARCHAR2(12) NOT NULL,
us_ssn      VARCHAR2(26) NOT NULL);
INSERT INTO customer_details_tab VALUES (7001, 1234765476541234, ‘1234-7654-7654-1234′, ’01-AUG-2020’, ‘sample1.contact@dbcme.com’, ‘123-456-7890’, ‘123-45-6789’);
INSERT INTO customer_details_tab VALUES (7002, 2345987698762345, ‘2345-9876-9876-2345′, ’02-AUG-2025’, ‘sample2.mail@gmail.com’, ‘234-567-8901’, ‘234-56-7890’);
INSERT INTO customer_details_tab VALUES (7003, 3456876587653456, ‘3456-8765-8765-3456′, ’03-AUG-2030’, ‘sample3.longemailid@hotmail.com’, ‘345-678-9012’, ‘345-67-8901’);
COMMIT;

CREATE USER redact_user IDENTIFIED BY redact;
GRANT UNLIMITED TABLESPACE TO redact_user;
GRANT CREATE SESSION TO redact_user;
GRANT SELECT ON customer_details_tab TO redact_user;
ADD_POLICY ( function_type   => DBMS_REDACT.none )
conn base_user/base
BEGIN
DBMS_REDACT.add_policy(
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_001’,
policy_description => ‘redactPolicy_001 for customer_details_tab table’,
column_name => ‘customer_id’,
column_description => ‘id NUMBER value in customer_details_tab table’,
function_type => DBMS_REDACT.NONE,
function_parameters => NULL,
expression => ‘1=1’,
enable => TRUE);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT customer_id FROM base_user.customer_details_tab ORDER BY 1;

CUSTOMER_ID
———–
       7001
       7002
       7003

conn base_user/base
BEGIN
  DBMS_REDACT.drop_policy(object_schema => ‘base_user’, object_name => ‘customer_details_tab’, policy_name => ‘redactPolicy_001’);
END;
/

PL/SQL procedure successfully completed.
function_type   => DBMS_REDACT.FULL
conn base_user/base
BEGIN
DBMS_REDACT.add_policy(
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_002’,
policy_description => ‘redactPolicy_002 for customer_details_tab table’,
column_name => ‘customer_id’,
column_description => ‘customer_id value in customer_details_tab table’,
function_type => DBMS_REDACT.FULL,
function_parameters => NULL,
expression => ‘1=1’,
enable => TRUE);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT customer_id FROM base_user.customer_details_tab ORDER BY 1;
CUSTOMER_ID
———–
  0
  0
  0

conn base_user/base
BEGIN
  DBMS_REDACT.drop_policy(object_schema => ‘base_user’, object_name => ‘customer_details_tab’, policy_name => ‘redactPolicy_002’);
END;
/

PL/SQL procedure successfully completed.

function_type   => DBMS_REDACT.PARTIAL
conn base_user/base
BEGIN
DBMS_REDACT.add_policy(
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_003’,
policy_description => ‘redactPolicy_003 for customer_details_tab table’,
column_name => ‘card_no’,
column_description => ‘card_no value in customer_details_tab table’,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => ‘9,1,3’,
expression => ‘1=1’,
enable => TRUE);
END;
/

PL/SQL procedure successfully completed.

card_no column format for data alignment:
col card_no format 9999999999999999

conn redact_user/redact
SQL> SELECT card_no FROM base_user.customer_details_tab ORDER BY 1;

  CARD_NO
—————–
 9994765476541234
 9995987698762345
 9996876587653456

conn base_user/base
BEGIN
  DBMS_REDACT.drop_policy(object_schema => ‘base_user’, object_name => ‘customer_details_tab’, policy_name => ‘redactPolicy_003’);
END;
/

PL/SQL procedure successfully completed.
function_type   => DBMS_REDACT.RANDOM
conn base_user/base
BEGIN
DBMS_REDACT.add_policy(
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_004’,
policy_description => ‘redactPolicy_004 for customer_details_tab table’,
column_name => ‘card_string’,
column_description => ‘card_string value in customer_details_tab table’,
function_type => DBMS_REDACT.RANDOM,
function_parameters => NULL,
expression => ‘1=1’,
enable => TRUE);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT card_string FROM base_user.customer_details_tab ORDER BY 1;

CARD_STRING
——————-
qy[;D8&!y”cCL@.)”*k
bLC>CuVjTKFK}d^rS
)h=l0I^i1pEt8Qfq9xM


conn base_user/base
BEGIN
  DBMS_REDACT.drop_policy(object_schema => ‘base_user’, object_name => ‘customer_details_tab’, policy_name => ‘redactPolicy_004’);
END;
/

PL/SQL procedure successfully completed.
function_type   => DBMS_REDACT.REGEXP
conn base_user/base
BEGIN
DBMS_REDACT.add_policy(
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_005’,
policy_description => ‘redactPolicy_005 for customer_details_tab table’,
column_name => ’email’,
column_description => ‘expiry_date NUMBER value in customer_details_tab table’,
function_type => DBMS_REDACT.REGEXP,
function_parameters => NULL,
expression => ‘1=1’,
enable => TRUE,
regexp_pattern => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,
regexp_replace_string => DBMS_REDACT.RE_REDACT_EMAIL_NAME,
regexp_position => 1,
regexp_occurrence => 0,
regexp_match_parameter => NULL);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT email FROM base_user.customer_details_tab ORDER BY 1;

EMAIL
————————
xxxx@dbcme.com
xxxx@gmail.com
xxxx@hotmail.com

— Cleanup: 
conn base_user/base
BEGIN 
  DBMS_REDACT.drop_policy(object_schema => ‘base_user’, object_name => ‘customer_details_tab’, policy_name => ‘redactPolicy_005’);
END;
/

PL/SQL procedure successfully completed.

ADD_POLICY
conn base_user/base
BEGIN 
DBMS_REDACT.add_policy(
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_001’,
policy_description => ‘redactPolicy_001 for customer_details_tab table’,
column_name => ‘customer_id’,
column_description => ‘customer_id NUMBER value in customer_details_tab table’,
function_type => DBMS_REDACT.partial,
function_parameters => ‘9,1,3’,
expression => ‘1=1’,
enable => TRUE);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT customer_id FROM base_user.customer_details_tab ORDER BY 1;

CUSTOMER_ID
———–
       9991
       9992
       9993
Partial redaction for TEXT type.
ALTER_POLICY, ADD_COLUMN

conn base_user/base
BEGIN 
DBMS_REDACT.alter_policy (
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_001’,
action => DBMS_REDACT.add_column,
column_name => ‘card_string‘,
function_type => DBMS_REDACT.partial,
function_parameters => DBMS_REDACT.REDACT_CCN16_F12);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT card_string FROM base_user.customer_details_tab ORDER BY 1;

CARD_STRING
——————-
****-****-****-1234
****-****-****-2345
****-****-****-3456
MODIFY_COLUMN
conn base_user/base
BEGIN 
DBMS_REDACT.alter_policy (
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_001’,
action => DBMS_REDACT.modify_column,
column_name => ‘card_string’,
function_type => DBMS_REDACT.partial,
function_parameters => ‘VVVVFVVVVFVVVVFVVVV,VVVVVVVVVV-VV-VV-VV,*,1,10’);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT card_string FROM base_user.customer_details_tab ORDER BY 1;

CARD_STRING
——————-
**********-54-12-34
**********-76-23-45
**********-65-34-56

Partial redaction for DATE type.
function_parameters => DBMS_REDACT.redact_date_millennium
conn base_user/base
BEGIN 
DBMS_REDACT.alter_policy (
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_001’,
action => DBMS_REDACT.add_column,
column_name => ‘expiry_date‘,
function_type => DBMS_REDACT.partial,
function_parameters => DBMS_REDACT.redact_date_millennium);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT TO_CHAR(expiry_date,’DD-MON-YYYY HH:MI:SS AM’) FROM base_user.customer_details_tab ORDER BY 1;

TO_CHAR(EXPIRY_DATE,’DD-MON-YYYY
——————————–
01-JAN-2000 12:00:00 AM
01-JAN-2000 12:00:00 AM
01-JAN-2000 12:00:00 AM
function_parameters => DBMS_REDACT.redact_date_epoch
conn base_user/base
BEGIN 
DBMS_REDACT.alter_policy (
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_001’,
action => DBMS_REDACT.modify_column,
column_name => ‘expiry_date‘,
function_type => DBMS_REDACT.partial,
function_parameters => DBMS_REDACT.redact_date_epoch);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT TO_CHAR(expiry_date,’DD-MON-YYYY HH:MI:SS AM’) FROM base_user.customer_details_tab ORDER BY 1;

TO_CHAR(EXPIRY_DATE,’DD-MON-YYYY
——————————–
01-JAN-1970 12:00:00 AM
01-JAN-1970 12:00:00 AM
01-JAN-1970 12:00:00 AM

function_parameters => ‘m01Dy2016HMS’ custom format
conn base_user/base
BEGIN 
DBMS_REDACT.alter_policy (
object_schema => ‘base_user’,
object_name => ‘customer_details_tab’,
policy_name => ‘redactPolicy_001’,
action => DBMS_REDACT.modify_column,
column_name => ‘expiry_date‘,
function_type => DBMS_REDACT.partial,
function_parameters => ‘m02Dy1987Hm17S‘);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT TO_CHAR(expiry_date,’DD-MON-YYYY HH:MI:SS AM’) FROM base_user.customer_details_tab ORDER BY 1;

TO_CHAR(EXPIRY_DATE,’DD-MON-YYYY
——————————–
01-FEB-1987 12:00:00 AM
02-FEB-1987 12:00:00 AM
03-FEB-1987 12:00:00 AM

DBMS_REDACT.disable_policy
conn base_user/base
BEGIN 
DBMS_REDACT.disable_policy(object_schema => ‘base_user’, object_name => ‘customer_details_tab’, policy_name => ‘redactPolicy_001’);
END;
/

PL/SQL procedure successfully completed.
conn redact_user/redact
SQL> SELECT customer_id, card_string, TO_CHAR(expiry_date,’DD-MON-YYYY HH:MI:SS AM’) FROM base_user.customer_details_tab ORDER BY 1;

CUSTOMER_ID CARD_STRING      TO_CHAR(EXPIRY_DATE,’DD-MON-YYYY
———– ——————- ——————————–
       7001 1234-7654-7654-1234 01-AUG-2020 12:00:00 AM
       7002 2345-9876-9876-2345 02-AUG-2025 12:00:00 AM
       7003 3456-8765-8765-3456 03-AUG-2030 12:00:00 AM

DBMS_REDACT.enable_policy
conn base_user/base
BEGIN 
DBMS_REDACT.enable_policy(object_schema => ‘base_user’, object_name => ‘customer_details_tab’, policy_name => ‘redactPolicy_001’);
END;
/

PL/SQL procedure successfully completed.

conn redact_user/redact
SQL> SELECT customer_id, card_string, TO_CHAR(expiry_date,’DD-MON-YYYY HH:MI:SS AM’) FROM base_user.customer_details_tab ORDER BY 1;

CUSTOMER_ID CARD_STRING      TO_CHAR(EXPIRY_DATE,’DD-MON-YYYY
———– ——————- ——————————–
       9991 **********-54-12-34 01-FEB-1987 12:00:00 AM
       9992 **********-76-23-45 02-FEB-1987 12:00:00 AM
       9993 **********-65-34-56 03-FEB-1987 12:00:00 AM

DBMS_REDACT.drop_policy
conn base_user/base
BEGIN 
DBMS_REDACT.drop_policy(object_schema => ‘base_user’, object_name => ‘customer_details_tab’, policy_name => ‘redactPolicy_001’);
END;
/

PL/SQL procedure successfully completed.
Object Cleanup
conn scott/tiger
DROP TABLE base_user.customer_details_tab;
DROP USER redact_user CASCADE;
DROP USER base_user CASCADE;

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn