Data Masking in Oracle 12c

Share via:

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

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (26 votes, average: 5.00 out of 5)
Loading...

13 thoughts on “Data Masking in Oracle 12c

  1. Nice article vinod !!!!
    Do we need any other special licenses to use this feature , if we have our database running with Oracle 12c Enterprise Edition.

  2. Excuse but Data Masking and Data Redaction both are different.
    In this demo you explained about data redaction not masking.
    Concept of masking is to mask the data and that is irreversible. Redaction is reversible.
    We mask data and then import it to other environment.

  3. i need help in masking using the masking features of 13c OEM for a people soft database. Masking rules needs to be implemented using features of 13c OEM. Can you help me, i am based in US. Willing to pay you for the services.
    My name is Mohan and my whatsapp number is +17327420640

  4. Nice article vinod
    Data masking used to sensitive data protection and unauthorized access to the original database.
    they are two types of Data masking
    Static Data and Dynamic Data

Add Comment