02 - DB2 for i (EN)02a - SQL (EN)02b - IBM i Tuning and admin (EN)04f - Security

Masking sensitive data with DB2 and SQL

Last Updated on 2 February 2020 by Roberto De Pedrini

The increasingly sophisticated data privacy regulations, together with the policies that individual companies adopt on this matter, mean that in some cases it is necessary to prevent the display of sensitive data to unauthorized users. Business applications may have been developed considering this requirement, but DB2 does indeed incorporate SQL statements that allow you to satisfy this requirement very easily automatically and at the system level, thanks to two main features:

  • RCAC – row and column access control
  • MASK type objects – single column level masks

We see here how it is possible to create a filter that allows displaying a sensitive field only to authorized users.

In order to activate this function, the requirements are as follows:

  • IBM i 7.2 or higher
  • product 5770SS1 option 47 Advanced Data Security for i
  • the user who enables the masking must be registered as a DB administrator
  • The table on which you want to apply the mask must have activated column-level access control

For checking option 47, you can simply issue the usual GO LICPGM command, choice 10.

To authorize a user as a DB manager, “standard” privileges such as *SECADM or *SECOFR are not enough, the user must be added to a special register which can be accessed with the WRKFCNUSG (Work with Function Usage) command. By running the command without any parameters, the following screen appears:

The function ID we need is QIBM_DB_SECADM. With option 2 we can add the authorized user.

To check this kind of authorization we can query the catalog view QSYS2.FUNCTION_USAGE

Finally, we enable column-level access control (RCAC) for our file:

ALTER TABLE mduca1.custm00f        
ACTIVATE COLUMN ACCESS CONTROL  

Once the settings are completed, we can go: in a customer master file we want only authorized users to be able to see the company name. The SQL statement to enable column masking is CREATE MASK. For our file, we can use this statement:

CREATE OR REPLACE MASK cust_name_mask ON mduca1.custm00f        
FOR COLUMN custname RETURN                          
CASE WHEN (SESSION_USER <> 'QSECOFR')               
     THEN '----------'   
     ELSE custname                                   
END                                                  
ENABLE  

The rule here is quite basic, if the user is not QSECOFR, 10 dashes will be displayed instead of the company name. This rule can obviously be more sophisticated; for example, you can call a specific personalized function to determine who can see the data and who cannot. Example:

CREATE OR REPLACE MASK cust_name_mask ON mduca1.custm00f        
FOR COLUMN custname RETURN                          
CASE WHEN (checkAuthCustname (SESSION_USER) <> '1')               
     THEN '----------'   
     ELSE custname                                   
END                                                  
ENABLE 

If the checkAuthCustname function returns ‘1’, the current user will see the real value of that column.

The ENABLE clause causes masking to be activated for the column, it can therefore be turned on and off as needed.
Let’s check if everything works: logging in with an unauthorized user and querying the file, you do not have access to the field with the company name:

Instead, by logging in with the authorized user (QSECOFR), here is the result:

It is interesting to notice that SQL masks work against any system command that displays the masked data, so even an unauthorized developer running a simple DSPPFM would still not have access to sensitive data.

To erase a mask, nothing easier …

DROP MASK mduca1.cust_name_mask

DB2 masks can also be deactivated temporarily, without necessarily deleting them. Here is the instruction:

ALTER MASK cust_name_mask DISABLE

To completely disable column access control for a file, the statement is this:

ALTER TABLE mduca1.custm00f        
DEACTIVATE COLUMN ACCESS CONTROL  

So far so good … but what if an unauthorized user runs a normal program that updates that file? Below is the source of a very simple program that reads a record of the “masked” CUSTM00F file and updates the city field. The program performs an UPDATE statement, which in fact affects all fields of the file.

       ctl-opt option (* srcstmt: * nodebugio);                     
       dcl-f Custm01l disk (* ext) keyed usage (* input: * update);   
       dcl-s cust char (8);                                  
       // ------------------------------------------------ ----    
        INLR * = * on;                                               
                                                   
        cust = '00003256';                                      
        chain (cust) Custm01l;                                   
        if% found (Custm01l);                                     
           city = 'LAS VEGAS';                                   
           update Custm;                                         
        endif;                                                   
                                                                 
        return;                                                  

Running the program after logging in with an unauthorized user, here’s what happens:

Alarm! The program has also updated the custName field with the masked value!

This apparently serious problem can be easily solved by activating a check constraint against the file, in order to intercept the masked value and prevent it from being actually written to the DB.

ALTER TABLE mduca1.custm00f
ADD CONSTRAINT cust_name_mask_ck
CHECK (custname <> '----------')
ON UPDATE VIOLATION PRESERVE custname;

With this instruction, we tell DB2 not to accept a value of 10 dashes in the custname field of CUSTM00F file; when DB2 detects a violation to this rule it keeps the value of the field (ON UPDATE VIOLATION PRESERVE custname). After restoring the previous value of custname field and activating the constraint, let’s try to run the same program again and let’s see what happens this time…

Now everything is OK !

To list all the masks present in the system, you can exploit the QSYS2.SYSCONTROLS view:

CREATE MASK and all related functions can be safely used both for SQL tables and for the classic physical files defined with DDS. By taking advantage of the SQL masks, and activating the appropriate constraints, you can develop your own applications without worrying about implementing masking, which will be automatically managed by DB2.

DOCUMENTATION:
CREATE MASK
statement:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzcrtmask.htm
View FUNCTION USAGE:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqviewfuncusage.htm
View SYSCONTROLS:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzcatsyscontrols.htm
WRKFCNUSG
command:
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/cl/wrkfcnusg.htm

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

Senior IBM i Analyst/Developer and technical writer. Former collaborator of the Italian edition of "System i News" magazine and author of several publications about tools and development practices for the IBM i platform.

Leave a Reply

Your email address will not be published. Required fields are marked *