Data Masking in DB2

User Data security is one of the critical aspects of database administration. Its DBAs responsibility to ensure data must only be accessed by authorized users and DBAs can control that by restricting access at various level. In some cases you want to completely or partially conceal data depending on who is accessing it, e.g. In Payroll application, manager can view only last 4 digit of employees SSN while a team member cannot see SSN of other employees but can see his/her SSN. If we try to achieve this at application level it will increase application complexity as well as might impact application performance but still your database is vulnerable. If a user gets access to database they can see any employee SSN. Best way to achieve it by Data Masking at database level. 

Data breaches might lead to financial loss, reputational damage, operational downtime, legal action, loss of sensitive data. With changing data privacy law and regulations its even critical now to ensure data is adequately protected. 

In DB2 LUW database Data Masking can be achieved with the help of Column Mask which was introduced in DB2 10.1 as part of Row and Column Access Control (RCAC) . In this blog we will go through steps to enable data masking in DB2 LUW and few scenarios of data masking.

How to Create Column Mask in DB2 LUW?

Column Mask can be created using CREATE MASK command. To create mask user must have SECADM authority, no other privilege is require like select on table or execute on function.

CREATE MASK <mask name> ON <table name> FOR COLUMN <column name> RETURN <case expression> ENABLE

Above command will create column mask for column SSN on table EMPLOYEE and enable it but still it will not take effect until and unless COLUMN ACCESS CONTROL gets activated on table. 

How to Activate Column Access Control?

Use below command to activate COLUMN ACCESS CONTROL on table.

ALTER TABLE <table name> ACTIVATE COLUMN ACCESS CONTROL

Once Column Access Control gets activated all existing column mask which is in enable state will take effect. Column mask created after activating Column Access Control will take effect immediately.

How to Check Column Access Control Status?

To check if COLUMN ACCESS CONTROL is enabled or not on table query CONTROL column of SYSCAT.TABLES

SELECT SUBSTR(TABNAME,1,10) as TABNAME, CONTROL FROM SYSCAT.TABLES WHERE TABNAME='EMPLOYEE'

If CONTROL column contains value as C it means COLUMN ACCESS CONTROL is enable, in case it is not enable you will get a blank.

Column Mask can only be created on table, we cannot create column mask on nickname, temporary table, alias, view, synonym, base table of shadow table, typed table and catalog table.

We can create only 1 Colum Mask per column but we can create multiple column mask on 1 table. If you try to create Column mask on column on which already there is Column Mask created then you will get error with sqlcode SQL20475N SQLSTATE=428HC

How to find Existing Column Mask?

To find existing Column Mask on a table, query SYSCAT.CONTROLS.

SELECT SUBSTR(CONTROLNAME,1,20) as CONTROLNAME , SUBSTR(TABSCHEMA,1,10) as TABSCHEMA , SUBSTR(TABNAME,1,10) as TABNAME, ENABLE from SYSCAT.CONTROLS

Scenario 1:

In a payroll system, we want user belonging to role PAYROLL have full access to SSN of employee but user with role MANAGER must only see last 4 digit of employee SSN and all other users should get a NULL value while accessing SSN column.

This can be achieved by creating a Column Mask on SSN column. To demonstrate this we have a table EMPLOYEE with 2 records and when we query EMPLOYEE table we can see SSN.

Now we will create a column mask for above scenario.

CREATE OR REPLACE MASK EMPLOYEE_SSN_MASK ON DB2I2.EMPLOYEE FOR COLUMN SSN RETURN CASE WHEN (VERIFY_ROLE_FOR_USER(SESSION_USER,'PAYROLL')=1) THEN SSN WHEN (VERIFY_ROLE_FOR_USER(SESSION_USER,'MANAGER')=1) THEN 'XXX-XX-' || SUBSTR(SSN,8,4)   ELSE NULL   END   ENABLE

Since user DB2I2 is not part of either role PAYROLL or MANAGER, when we will query table EMPLOYEE we should get NULL value. As we can see in below screenshot we got NULL value for SSN column.

 

Now lets query EMPLOYEE table using user SUMIT which is part of role PAYROLL. In below screenshot we got actual value for SSN column.

Now lets query EMPLOYEE table using user AMIT which is part of role MANAGER. In below screenshot we got only last 4 digit for SSN column.

With the help of column mask we were able to achieve our goal to mask SSN value and ensured even instance user is unable to see actual data. Mask can be implemented not only on ROLE but also on USER GROUP. To implement column mask using USER GROUP we just need to replace function VERIFY_ROLE_FOR_USER(?,?) with function VERIFY_GROUP_FOR_USER(?,?).

Scenario 2:

For a peer feedback system, we want to mask PEER_NAME and make it null if employee opted not to disclose peer_name while submitting peer rating.

This can be achieved by creating a column mask on column PEER_NAME. To demonstrate this we will use table PEER_FEEDBACK with below records.

Below is screenshot for Average rating of Peer

Lets create a column mask for above scenario.

db2 "CREATE OR REPLACE MASK PEER_FEEDBACK_PEER_NAME_MASK ON DB2I2.PEER_FEEDBACK FOR COLUMN PEER_NAME RETURN CASE WHEN (DISCLOSE_PEER='Y') THEN PEER_NAME ELSE '' END ENABLE"

This mask is not ROLE or GROUP based so its applicable for all users. We can see in below screenshot after creating of column mask, PEER_NAME field is blank for record where DISCLOSE_PEER='N'

Lets calculate average rating of Peer and see if column mast impact average calculation.

This implies that COLUMN MASK does not interfere in query processing and output, its just mask the resulting rows.

Word of Caution

If you add a column to a table on which a column mask is defined, it will invalid Column Mask and access to table will be restricted until Column mask is either disabled, dropped or recreated. You will get error SQL5188N when trying to access table.

You cannot alter a column or rename a column on which Column Mask is defined. You will get error SQL20479N.

If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.