Masking your Db2 Databases to Protect Sensitive Data

I thought about titling this entry “To Mask or Not to Mask” to generate more visitors to the blog, but then I quickly thought better of it😊  While I did pickup some medical knowledge during my 5+ years of working on Watson Health solutions, I definitely do not have the credentials to discuss medical masking.  However, with over 25 years of experience working on Db2 for i, I am qualified to talk about protecting sensitive data in your databases with the Db2 masking support.

Before I highlight the Db2 masking support, I want to remind everyone that a solid object-based security model is the first step in securing all of the valuable business data that you store on IBM i. Masking is meant to be used as a granular security control after your Db2 for i objects have been secured with IBM i’s robust object-level security.

The column mask support was first delivered in the IBM i 7.2 release as part of the new security functionality known as Row and Column Access Control (RCAC). RCAC support is made available through the optional, no charge feature called IBM Advanced Data Security for i, also known as option 47 of the IBM i operating system.

A user authorized to a Db2 table object can view all of the column values stored in that table. A column mask enables you to provide another layer of protection around columns storing sensitive values like credit card numbers and tax identification numbers. This extra layer allows you to limit access to a column with sensitive data values to just a subset of the users that have object-level access.

For the moment, assume that you’re a retailer that stores credit card numbers in the orders table. The following mask definition only allows the card number to be visible to users that belong to the customer support group profile.  All other users (security administrator, programmers, etc.) are only allowed to view the last 4 digits of the credit card numbers stored in the cardnum column. 

CREATE MASK mask_cardnum ON orders FOR COLUMN cardnum
RETURN
   CASE WHEN VERIFY_GROUP_FOR_USER(SESSION_USER , 'SUPPORT') = 1
    THEN cardnum
    ELSE OVERLAY(cardnum PLACING '*************' FROM 1 FOR 12)
   END ENABLE 

Even though a column mask is defined with SQL, the masking is enforced on all SQL and non-SQL interfaces. Consult the Row and Column Access Control Support in IBM Db2 for i Redpaper for additional details. 

While a column mask is enforced on all interfaces that directly access the column, there is a security exposure that arises when a column is compared to a host variable or literal value on a query request.  A security exposure exists because the Db2 for i performance tools (e.g. Plan Cache) capture the value of the host variable or literal from the query execution since the actual value that is passed in can affect the query optimizer’s choice of data access methods.  

To highlight this exposure, let’s assume that this SQL statement is embedded within one of your programs.

   SELECT ordID INTO :hv1 FROM orders WHERE cardnum = :hv2

A developer that has access to the Db2 Performance tools on your system, uses that tooling to launch Visual Explain to analyze the performance of this Select statement. Notice below in the captured Visual Explain output that the both the SQL statement text and the host variable values are shown. 

Although the highlighted host variable value is not guaranteed to be stored in the orders table, there’s a high likelihood that it is one of the stored sensitive card numbers since the application program is searching for that value. 

To close this security exposure, IBM provides the SET_COLUMN_ATTRIBUTE service in the SYSPROC library. This service allows you to register your database columns that store sensitive data.  Once a column has been registered, Db2 for i will start masking the host variable or literal values compared to it during its collection of performance data. 

In our example, the following store procedure call is made to secure the cardnum column:  

CALL sysproc.set_column_attribute

                       ('DBLIB', 'ORDERS', 'CARDNUM', 'SECURE YES')

Now that this column has been registered, the following image shows Visual Explain displaying a masked value of *SECURE instead of a credit card number. 

The secure column attribute setting applies to data collected by the Plan Cache, Plan Cache Snapshot and the Database Performance Monitor tooling.  A value of *SECURE will be displayed for all users except the QSECOFR user.  

The SET_COLUMN_ATTRIBUTE should be used to prevent sensitive data leaks independent of any plans that you have for employing column mask definitions in your database.

Hopefully, you now see how Db2 masking functionality can add an extra layer of security to your database object-level security implementation.  Feel free to reach out if your company is need of assistance of securing its most important asset – data.