DB2 Column Masking for Sensitive Data
By Tom Nonmacher
In today's data-driven world, sensitive data protection is of utmost importance. One of the effective methods to secure sensitive data in databases is data masking, specifically column masking. For this post, we will focus on column masking in IBM's DB2 11.1, but we'll also touch on the similar features in SQL Server 2016, SQL Server 2017, MySQL 5.7, and Azure SQL.
Column masking in DB2 allows you to define a return value that users will see when querying a column, instead of the actual sensitive data. The return value could be a constant, a function applied to the column, or a completely unrelated value. This way, unauthorized users can still query and work with the data without actually seeing the sensitive information.
To implement column masking in DB2 11.1, you can use the CREATE MASK statement. Here is an example of its usage:
CREATE MASK SSN_MASK ON USERS
FOR COLUMN SSN RETURN
CASE
WHEN VERIFY_GROUP_FOR_USER(SESSION_USER,'SSN_VIEWER') = 1 THEN SSN
ELSE 'XXX-XX-XXXX'
END
ENABLE;
In the example above, we create a mask for the SSN column in the USERS table. When a user who belongs to the 'SSN_VIEWER' group queries this column, they will see the actual SSN. Otherwise, they will see a masked value 'XXX-XX-XXXX'.
SQL Server 2016 and 2017 offer a similar feature called Dynamic Data Masking (DDM). DDM automatically hides sensitive data in the result set of a query by replacing it with "masked" data. Here's how you might define a mask for an email column in SQL Server:
ALTER TABLE Users
ADD MASKED WITH (FUNCTION = 'email()') FOR Email;
MySQL 5.7 doesn't natively support column masking. However, you can achieve similar functionality by creating views with select statements that return masked data. Azure SQL, like SQL Server, also supports Dynamic Data Masking.
In conclusion, column masking is a very useful feature that helps protect sensitive data in your databases. It's important to note that column masking is just one of many measures to secure your data and should be used in conjunction with other security measures, such as proper access control, encryption, and regular security audits.