Oracle Redaction

Oracle 12c having new feature Oracle Redaction

Oracle redaction means mask the data while fetching by application. It have the ability to redact sensitive data.

Types of Redaction:
Full Redaction: Redact full column data.
Partial Redaction: column data portion or part is redact as per need.
Regular Redaction: Regular expression to redact column data as a pattern. It is for character data only.
Random Redaction: It will generate random value at time of query.
No Redaction: Test the internal operation of redaction policy.

Syntax to add policy of Redaction:

DBMS_REDACT.ADD_POLICY (
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
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);

Example to add full redaction
In number datatype it change the value to 0 as default in full redaction.

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
column_name => 'commission_pct',
policy_name => 'redact_com_pct',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/

Check the table after apply the redact policy

SELECT COMMISSION_PCT FROM HR.EMPLOYEES;
COMMISSION_PCT
--------------
0
0

Check the reduction present in database

SELECT * FROM REDACTION_POLICIES;

SELECT * FROM REDACTION_COLUMNS;

SELECT * FROM REDACTION_VALUES_FOR_TYPE_FULL;

Alter the existing reduction syntax

DBMS_REDACT.ALTER_POLICY (
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
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 := NULL,
regexp_occurrence IN BINARY_INTEGER := NULL,
regexp_match_parameter IN VARCHAR2 := NULL,
policy_description IN VARCHAR2 := NULL,
column_description IN VARCHAR2 := NULL);

ACTION parameter value defines what will the change done on the existing redaction policy: Following are the value defines the ACTION value:
DBMS_REDACT.MODIFY_COLUMN : Change the column_name value.
DBMS_REDACT.ADD_COLUMN : Add a new column in existing redaction policy.
DBMS_REDACT.DROP_COLUMN : Remove column from redaction policy.
DBMS_REDACT.MODIFY_EXPRESSION: Change the expression value.
DBMS_REDACT.SET_POLICY_DESCRIPTION: Change the description
DBMS_REDACT.SET_COLUMN_DESCRIPTION: Change the description of column.

Example of adding a new column in existing policy of data redaction

BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol',
action => DBMS_REDACT.ADD_COLUMN,
column_name => 'hire_date',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => DBMS_REDACT.REDACT_DATE_EPOCH);
END;
/
Note: detail you got in partial redaction page.

Drop the policy

BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Enable the data redaction policy

BEGIN
DBMS_REDACT.ENABLE_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Disable the data redaction policy

BEGIN
DBMS_REDACT.DISABLE_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s