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.
