Data Masking in Oracle 12c

Dear Readers,

In this article, we will see the Data Masking in Oracle 12c.

Every organization maintains the sensitive data regarding their operations. like  personal identifiable data, personal sensitive data or commercially sensitive data. organizations are required to protect their sensitive data falling to the wrong hands.

Data can be protect by doing the common stuff such as data encryption, strong passwords, and minimal privileges. but sometimes we need to compromise to do this things as per requirement and performance of databases.

To avoid the all difficulties in Oracle introduced the Data masking concept(DATA REDACTION) in Version 12C  to provide more security to the sensitive data.

The main advantage of data redaction police is dynamically provides realistic-looking data to the customers  instated of real data.

In order to perform data masking user need execute permission on sys.dbms_redact

SYS>> grant execute on sys.dbms_redact to data;

Data masking can be done dynamically or statically to protect sensitive data.

You can redact column data by using one of the following methods:

• Full redaction
• Partial redaction
• Regular expressions
• Random redaction
• No redaction

 

Full redaction

Full redaction will redact all of the contents of the column data. The redacted value returned to the querying application user depends on the data type of the column.

Columns of the NUMBER data type are redacted with a zero (0).

Columns of the character data types are redacted with a single space.

 

FULL DATA MASKING

DATA>>select * from dmtest;

 

 

DATA>>connect / as sysdba

Connected.

 

SYS>>grant execute on sys.dbms_redact to data;

 

SYS>>connect data/mask;

Connected.

 

Run the below procedure to Full mask the data.

DATA>>BEGIN

2  DBMS_REDACT.ADD_POLICY(

3  object_schema => ‘DATA’,

4  object_name => ‘DMTEST’,

5  column_name => ‘U_ATM_PIN’,

6  policy_name => ‘REDACT_PIN’,

7  function_type => DBMS_REDACT.FULL,

8  expression => ‘1=1’);

9  END;

10  /

 

PL/SQL procedure successfully completed.

 

 

DATA>>select * from dmtest;

 

 

FULL DATA UNMASKING

Run the below procedure to Full unmask the data.

DATA>>BEGIN

2 DBMS_REDACT.alter_policy(

3  object_schema=>’DATA’,

4 object_name=>’DMTEST’,

5  policy_name=>’REDACT_PIN’,

6 action=>DBMS_REDACT.modify_expression,

7  column_name=>’U_ATM_PIN’,

8 expression=>’SYS_CONTEXT(”USERENV”,”SESSION_USER”) != ”DATA”’);

9 END;

10  /

 

DATA>>select * from dmtest;

 

 

Partial redaction

Partial redaction will redact a portion of the column data.

In this case we redacting Account number  with asterisks (*) except for the last 4 digits.

 

 

Run the below procedure to Partially mask the data.

DATA>>BEGIN

2   DBMS_REDACT.ADD_POLICY(

3    object_schema => ‘DATA’,

4    object_name => ‘DMTEST’,

5    column_name => ‘U_ACC_NO’,

6    policy_name => ‘REDACT_ACCNO’,

7    function_type => DBMS_REDACT.PARTIAL,

8    function_parameters =>’VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12′,

9    expression => ‘1=1’);

10  END;

11  /

 

 

DATA>>select * from dmtest;

 

 

Dropping the Redaction Police

 

We can drop the Redaction police by executing the following procedure.

 

DATA>>BEGIN

2  DBMS_REDACT.DROP_POLICY (

3  object_schema  => ‘DATA’,

4  object_name => ‘DMTEST’,

5  policy_name => ‘REDACT_ACCNO’);

6 END;

DATA>>/

 

PL/SQL procedure successfully completed.

 

 

 

DATA>>select * from dmtest;

 

 

Regular expressions

Regular expression can use regular expressions to look for patterns of data to redact.

you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only.

 

Random redaction

The redacted data presented to the querying application user appears as randomly generated values each time it is displayed, depending on the data type of the column.

 

DATA>>select * from USERINFO;

 

 

 

Run the below procedure for random mask the data.

DATA>>BEGIN

2  DBMS_REDACT.ADD_POLICY(

3  object_schema => ‘DATA’,

4  object_name => ‘USERINFO’,

5  column_name => ‘PHNO’,

6  policy_name => ‘REDACT_PHNO’,

7  function_type => DBMS_REDACT.RANDOM,

8  expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) = ”APPUSER”’);

9  END;

10  /

 

PL/SQL procedure successfully completed.

 

 

DATA>>select * from userinfo;

 

 

DATA>>select PHNO  from userinfo;

 

 

Grant Select privilege to appuser for select the data of userinfo table.

 

Connect the appuser and try to select the  DATA user USERINFO table.

 

 

You can find every time it showing random values for PHNO column.

 

Connect the DATA user and try to select the data.

 

 

Run the below procedure to show actual values to the APPUSER.

DATA>>BEGIN

2  DBMS_REDACT.ALTER_POLICY(

3  object_schema => ‘DATA’,

4  object_name => ‘USERINFO’,

5  policy_name => ‘REDACT_PHNO’,

6  action => DBMS_REDACT.MODIFY_EXPRESSION,

7  expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) != ”APPUSER”’);

8  END;

9  /

 

PL/SQL procedure successfully completed.

 

 

Connect the APPUSER user and try to select the data.

 

 

 

You can find every time it showing random values for PHNO column.

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform