Dynamic Data Masking for SQL Server Security
By Tom Nonmacher
Dynamic Data Masking (DDM) is a security feature that Microsoft introduced with SQL Server 2016. It is designed to limit the exposure of sensitive data to non-privileged users by masking it. DDM is a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.
Implementing DDM in SQL Server is straightforward. First, you need to define a mask for the sensitive data column in the table. For instance, suppose we have a 'Customers' table that contains sensitive data in the 'EmailAddress' column. We can mask this data as follows:
ALTER TABLE Customers
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()');
This command will replace the email address with xxxx@xxxx.com for non-privileged users. SQL Server also provides other masking functions such as default(), random(), and partial(). The default() function replaces any data type with a constant. The random() function replaces any numeric type with a random number within a specified range, and the partial() function exposes the first and last letters and adds a custom padding string in the middle.
MySQL 5.7 and DB2 11.1 don't offer the same built-in DDM functionality as SQL Server 2016 and 2017. However, you can still achieve similar results with views and other techniques. Azure SQL, on the other hand, does support DDM, and its implementation is quite similar to SQL Server's.
Before implementing DDM, please note that it is a data obfuscation technique that prevents sensitive data from being exposed in query results. It does not provide encryption or prevent data from being physically accessed by unauthorized users. Therefore, DDM should be part of a comprehensive data security strategy that includes other techniques such as encryption, row-level security, and proper user permissions.
In conclusion, Dynamic Data Masking is a powerful tool for protecting sensitive data in SQL Server and Azure SQL. It allows you to control how much sensitive data can be seen by certain users, which can help your organization comply with privacy laws and regulations. However, DDM is not a silver bullet for data security. Therefore, it should be used in combination with other security measures to create a robust data protection strategy.