During Covid-19, I blogged about data masking. I showed how masks are used to filter out stuff that may harm you or others, how they prevent “leakage” of harmful particles, including sensitive data. Db2 12.1.2 includes many security enhancements, including improvements to data masking.
Row and column access control
Masking is part of Row and column access control (RCAC). It allows to filter on which rows are visible to specific users, and how column values are presented. Often, for that reason, RCAC is referred to as fine-grained access control (FGAC). It is one layer in the overall strategy for database security, has its strengths and weaknesses (see later).
Rules for row access are defined using the CREATE PERMISSION. It allows to define who has access to certain rows (data records) based on a search conditions. That conditions can be used to check the user ID, role, or other attributes. Individual column values can be masked based on a defition via CREATE MASK. A CASE expression is used to determine what value to display or return.
Three built-in functions help to check roles:
- VERIFY_ROLE_FOR_USER checks whether the (current) session user belongs to any of the listed roles.
- Similarly, VERIFY_GROUP_FOR_USER checks for group membership against a list of possible groups.
- Last, VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER checks whether the current user has obtained a specified role through a trusted context.
You can find more information on row and column access control in the Information Center, including a A practical guide to implementing row and column access control.
Unmasking masked data - or not
In October 2024, Emil Kotrc published the blog Unmasking the masked data on the IDUG website. He details (for Db2 for z/OS) one weakness of column masks and how it can be attacked. Given the right privileges and knowledge, someone can work around masks. Using the EMP table from the z/OS sample database, he wrote a common table expression (CTE) to unmask the masked data:
with hack(number) as (
select 1 from sysibm.sysdummy1
union all
select number+1 from hack where number <= 30000)
select lastname, bonus, number as unmasked_bonus from emp, hack where bonus = number order by bonus, lastname;
At that time of his article, column masks were changing/filtering values prior to returning the result. Db2 12.12 (that is Db2 for Linux, UNIX, and Windows / LUW) adds a new AT READ option for when data should be changed/filtered. Moreover, it also has a new RESTRICT USAGE to prevent certain operations on tables with masking enabled. The two new options counter attacks based on known values and by inferencing. But AT READ comes with a bigger performance impact (see Greg Stager’s presentation in DB2Night Show 273). The RESTRICT USAGE is the switch to cut off such processing, it just throws an error (see below).
Playing the masking game
In the following, I am using the EMPLOYEE table from Db2’s SAMPLE database for a scenario similar to what Emil discussed. For the table and its column BONUS, I am going to create variations of the column mask, then evaluate the same SQL statement.
AT RESULT evaluation (old behavior)
The following statements create the column mask with AT RESULT and activate column access control on the table.
create or replace mask bonus_mask on employee
for column bonus
return
case
when (bonus > 500.00) then null
else bonus
end
enable at result;
alter table employee activate column access control;
Next, I run the CTE similar to what Emil described.
with dictionary_hack(number) as (
select 1 from sysibm.sysdummy1
union all
select number+1 from dictionary_hack where number <= 2000)
select lastname, bonus, number as unmasked_bonus from employee, dictionary_hack where bonus = number order by bonus, lastname;
LASTNAME BONUS UNMASKED_BONUS
--------------- ----------- --------------
JOHNSON 300.00 300
PARKER 300.00 300
SETRIGHT 300.00 300
SPRINGER 300.00 300
JEFFERSON 400.00 400
JONES 400.00 400
MEHTA 400.00 400
PIANKA 400.00 400
SMITH 400.00 400
SMITH 400.00 400
WALKER 400.00 400
ADAMSON 500.00 500
ALONZO 500.00 500
GOUNOT 500.00 500
LEE 500.00 500
PEREZ 500.00 500
QUINTANA 500.00 500
SCHNEIDER 500.00 500
SCHWARTZ 500.00 500
SCOUTTEN 500.00 500
SPENSER 500.00 500
STERN 500.00 500
WONG 500.00 500
YAMAMOTO 500.00 500
YOSHIMURA 500.00 500
BROWN - 600
HENDERSON - 600
JOHN - 600
LUTZ - 600
MARINO - 600
MONTEVERDE - 600
NATZ - 600
NICHOLLS - 600
O'CONNELL - 600
ORLANDO - 600
PULASKI - 700
GEYER - 800
KWAN - 800
THOMPSON - 800
LUCCHESSI - 900
HAAS - 1000
HEMMINGER - 1000
42 record(s) selected.
The output shows that the BONUS values can be unmasked. It shows the limitations of that approach and that masking only complements other security measures.
I also ran a second similar query which uses COUNT and AVG on the original and hacked BONUS columns.
with dictionary_hack(number) as (
select 1 from sysibm.sysdummy1
union all
select number+1 from dictionary_hack where number <= 2000)
select count(bonus) as ct_bonus, avg(bonus) as avgbonus, count(number) as ct_unmasked, avg(number) as unmasked_bonus from employee, dictionary_hack where bonus = number
at result
CT_BONUS AVGBONUS CT_UNMASKED UNMASKED_BONUS
----------- --------------------------------- ----------- --------------
25 440.000000000000000000000000 42 547
1 record(s) selected.
AT READ (new option)
Next in my tests, I recreated the column mask with the new AT READ option. Then, I reran both queries again.
create or replace mask bonus_mask on employee
for column bonus
return
case
when (bonus > 500.00) then null
else bonus
end
enable at read;
Db2 evaluates the filter while reading rows from the bufferpool, in an early processing stage. The query returns only the intended rows, other data is not leaked out, the filter works. The cost is the early filter itself which defeats indexing and other performance tuning.
LASTNAME BONUS UNMASKED_BONUS
--------------- ----------- --------------
JOHNSON 300.00 300
PARKER 300.00 300
SETRIGHT 300.00 300
SPRINGER 300.00 300
JEFFERSON 400.00 400
JONES 400.00 400
MEHTA 400.00 400
PIANKA 400.00 400
SMITH 400.00 400
SMITH 400.00 400
WALKER 400.00 400
ADAMSON 500.00 500
ALONZO 500.00 500
GOUNOT 500.00 500
LEE 500.00 500
PEREZ 500.00 500
QUINTANA 500.00 500
SCHNEIDER 500.00 500
SCHWARTZ 500.00 500
SCOUTTEN 500.00 500
SPENSER 500.00 500
STERN 500.00 500
WONG 500.00 500
YAMAMOTO 500.00 500
YOSHIMURA 500.00 500
25 record(s) selected.
The query with COUNT and AVG, expectedly, has the same values for both the masked column and the attempted hack.
CT_BONUS AVGBONUS CT_UNMASKED UNMASKED_BONUS
----------- --------------------------------- ----------- --------------
25 440.000000000000000000000000 25 440
1 record(s) selected.
RESTRICT USAGE (new option)
The new RESTRICT USAGE pretty much narrows possible SQL functionality to be applied to the table when access control is enabled. Below, I allowed joins.
create or replace mask bonus_mask on employee
for column bonus
return
case
when (bonus > 500.00) then null
else bonus
end
enable at read restrict usage allow joins;
Running both queries from above, they resulted in the following error message with error code SQL20478N. The queries are not allowed because of the restrictive column mask.
SQL20478N The statement failed because the column mask "DB2INST1.BONUS_MASK"
defined for column "DB2INST1.EMPLOYEE.BONUS" exists and the column mask cannot
be applied or the column mask conflicts with the failed statement. Reason code
"42" SQLSTATE=428HD
Conclusions
Database security is evolving, attacks are too. The additional options for data masking allow for more control on how masks are applied. Because of the performance impact, is a fine path on which DBAs and Security Admins walk, even for fine-grained access control. Choose wisely. “42” is the reason code returned, but not always the best answer… 😎
If you have feedback, suggestions, or questions about this post, please reach out to me on Mastodon (@data_henrik@mastodon.social) or LinkedIn.
