Oracle Redaction 12c new feature

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.