Full Redaction in Oracle Redaction

Full Redaction in Oracle Redaction

Following are the default values for the full data redaction:

Character data type is single space
Number data type is zero
Data time data type is ’01-Jan-01′

Check the Full Redaction default values

SET LINESIZE 250
COLUMN char_value FORMAT A10
COLUMN varchar_value FORMAT A10
COLUMN nchar_value FORMAT A10
COLUMN nvarchar_value FORMAT A10
COLUMN timestamp_value FORMAT A27
COLUMN timestamp_with_time_zone_value FORMAT A32
COLUMN blob_value FORMAT A20
COLUMN clob_value FORMAT A10
COLUMN nclob_value FORMAT A10
SELECT * FROM redaction_values_for_type_full;

Syntax for adding full reduction

DBMS_REDACT.ADD_POLICY (
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
column_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2,
function_type IN BINARY_INTEGER := NULL,
expression IN VARCHAR2,
enable IN BOOLEAN := TRUE);

Example for adding full reduction policy
HR is the schema and employees is the table present in HR Schema. You want to redact the commission pct column with full redaction. It will take the Default value define on the data type of the column. In my example commission pct is the NUMBER data type.
Note: Define the parameter expression
1. Expression “1=1″ means the redaction will always take place.
2. Expressions using the SYS_CONTEXT function. Example expression parameter is used for other user except manager roles:
expression => ‘SYS_CONTEXT(”SYS_SESSION_ROLES”,”MGR”) = ”FALSE”’
expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) != ”HR”’

--With Expression 1=1 means applied on all users
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;
/

--With expression specify at user level only HR user can see data other user see the redacted data according to policy.
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 => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR'''
);
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;

SET LINESIZE 120
COLUMN column_name FORMAT A30
COLUMN function_parameters FORMAT A50
SELECT column_name, function_type,function_parameters
FROM redaction_columns;

SELECT * FROM redaction_values_for_type_full;

Change the default value with DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES Procedure

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,
date_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 to change full redaction number default value

1. Check the default or previous value<

SELECT NUMBER_VALUE FROM REDACTION_VALUES_FOR_TYPE_FULL;
NUMBER_VALUE
------------
0

2. Modify the number datatype value:

EXEC DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (number_val => 7);

3. Restart the database

shutdown immediate;
startup;

Advertisements

One thought on “Full Redaction in Oracle Redaction

  1. Pingback: Oracle Redaction | Smart way of Technology

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

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