Dynamic Data Masking(DBMS_REDACT)

Dynamic Data Masking

====================

Day 3rd (Dynamic Data Masking)

==============================

Dynamic Data Masking is the technique of masking data on fly, in other words We can say

to mask the data on the time when end user retrieve the data in SELECT statement.

Purpose & Need

==============

Dynamic data masking is important in case where we do not want a specific database user to see specific actual data.

when he/she execute SELECT statement on underlying object for which he/she has given privileges then instead of actual data no actual or masked data

is generated by database engine and shown to the user.

I know its a bit confusing but believe me I will make your concept clear ahead by giving a real life scenario.

Consider a table named PERSONAL_INFO given below.

ID NAME CARD_NUM

---------- ------------------------------ --------------------

1 DOST-M-KHALIL 6124-3444-4427-8891

2 ZARYAB 3721-3777-4528-3225

3 AQIB 4111-6432-6578-3127

See the table here the CARD_NUM is sensitive info I want that no one see the actual card numbers except the schema owner in which the table is

created which is HR so for this purpose i need to mask them on run time so that the actual card numbers persist in database

so for this purpose I will use Dynamic masking technique.

Now the question arises how can we achieve dynamic data masking so don't worry I am here to tell you.

Oracle provides a built-in package DBMS_REDACT which is used to achieve dynamic data masking.

PROCEDURES IN DBMS_REDACT PACKAGE

=================================

There are various procedures inside DBMS_REDACT package which are listed below.

• DBMS_REDACT.ADD_POLICY
• DBMS_REDACT.ALTER_POLICY
• DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL
• DBMS_REDACT.CREATE_POLICY_EXPRESSION
• DBMS_REDACT.DISABLE_POLICY
• DBMS_REDACT.DROP_POLICY
• DBMS_REDACT.DROP_POLICY_EXPRESSION
• DBMS_REDACT.ENABLE_POLICY
• DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES
• DBMS_REDACT.UPDATE_POLICY_EXPRESSION

Each of the above has its own specific purpose. As your concept can be cleared with the help of

the following three procedures so I will bound my practical to these three procedures.

• ADD_POLICY
• ALTER_POLICY
• DROP_POLICY

LETS BEGIN THE PRACTICAL

========================

SCHEMA = HR

TABLE = PERSONAL_INFO

We are asked to mask CARD_NUM column for all the users except HR at run time. so for this purpose I will make use of

DBMS_REDACT package.

In DBMS_REDACT package there is procedure ADD_POLICY so I will make policy for this scenario.

BEGIN        

 DBMS_REDACT.ADD_POLICY(        

   object_schema       => 'HR',        

   object_name         => 'PERSONAL_INFO',        

   column_name         => 'CARD_NUM',        

   policy_name         => 'REDACT_CARD_NO',        

   function_type       => DBMS_REDACT.PARTIAL,        

   function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,5,12',        

   expression    => 'sys_context(''userenv'',''session_user'') != ''HR''');        

END;        

/        

Execute this code as SYS or a user who has EXECUTE ON DBMS_REDACT object privileges.

After executing this code grant SELECT on PERSONAL_INFO table to any other user, in my case i am granting to user HACKER.

Now whenever you execute select query from any other user except HR on this table you will see masked data but if you execute

select from HR user or SYS user or a user granted EXEMPT ANY POLICY system privilege, you will see the actual data.

SQL> conn hacker@pdb1/123        

Connected.        

SQL> SELECT * FROM hr.personal_info;        

ID NAME CARD_NUM

---------- ------------------------------ --------------------

1 DOST-M-KHALIL 6124-****-****-8891

2 ZARYAB 3721-****-****-3225

3 AQIB 4111-****-****-3127

SQL> conn hr@pdb1/123        

Connected.        

SQL> SELECT * FROM hr.personal_info;        

ID NAME CARD_NUM

---------- ------------------------------ --------------------

1 DOST-M-KHALIL 6124-3444-4427-8891

2 ZARYAB 3721-3777-4528-3225

3 AQIB 4111-6432-6578-3127

SQL> conn sys@pdb1 as sysdba        

Enter password:        

Connected.        

SQL> SELECT * FROM hr.personal_info;        

ID NAME CARD_NUM

---------- ------------------------------ --------------------

1 DOST-M-KHALIL 6124-3444-4427-8891

2 ZARYAB 3721-3777-4528-3225

3 AQIB 4111-6432-6578-3127

To get info about created policies query REDACTION_POLICIES static dictionary view.

To drop already created policy:

BEGIN        

    DBMS_REDACT.DROP_POLICY (        

       object_schema  => 'HR',        

       object_name    =>  'PERSONAL_INFO',        

       policy_name    =>   upper('REDACT_CARD_NO'));        

   END;        

/        

This is just the basic concept of dynamic data masking I hope after reading this article you will be able to learn deep

from oracle documentation by own.

There are different types of masking for detail information refer to Oracle official documentation.

In future if you are encountering any problem feel free to share I will try my best to help you for that.

Thanks Regards Dost Muhammad Khalil