An excellent alternative for complying with GDPR standards without changing applications.
Dynamic data masking (DDM) limits the exposure of confidential information by hiding it from unauthorised users. It can be used to greatly simplify the design and coding of application security.
Dynamic data masking prevents unauthorised access to sensitive data by allowing customers to specify the amount of confidential information that should be disclosed, with minimal impact on the application layer. DDM can be configured on designated database fields to hide sensitive data in the result sets of queries. With DDM the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.
• A central data masking policy acts directly on sensitive fields in the database.
• Designate privileged users or roles that do have access to the sensitive data.
• DDM features full masking and partial masking functions, and a random mask for numeric data.
• Simple Transact-SQL commands define and manage masks
As an example, a call center support person may identify callers by several digits of their social security number or credit card number. Social security numbers or credit card numbers should not be fully exposed to the support person. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query. For another example, by using the appropriate data mask to protect personally identifiable data, a developer can query production environments for troubleshooting purposes without violating compliance regulations.
The purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who should not have access to the data from viewing it. Dynamic data masking does not aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data. Dynamic data masking is complementary to other SQL Server security features (auditing, encryption, row level security…) and it is highly recommended to use this feature in conjunction with them in addition in order to better protect the sensitive data in the database.
Dynamic data masking is available in SQL Server 2016 (13.x) and Azure SQL Database, and is configured by using Transact-SQL commands.
Defining a Dynamic Data Mask
A masking rule may be defined on a column in a table, in order to obfuscate the data in that column. Four types of masks are available.
RandomA random masking function for use on any numeric type to mask the original value with a random value within a specified range.</td<
|Default value||Full masking according to the data types of the designated fields.
For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters (char, nchar, varchar, nvarchar, text, ntext).
For numeric data types use a zero value (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).
For date and time data types use 01.01.1900 00:00:00.0000000 (date, datetime2, datetime, datetimeoffset, smalldatetime, time).
For binary data types use a single byte of ASCII value 0 (binary, varbinary, image).
|Masking method that exposes the first letter of an email address and the constant suffix “.com”, in the form of an email address. Column in the source table capturadasaXXX@XXXX.com|
|Custom String||Masking method that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.
Best Practices and Common Use Cases
• Creating a mask on a column does not prevent updates to that column. So although users receive masked data when querying the masked column, the same users can update the data if they have write permissions. A proper access control policy should still be used to limit update permissions.
• Using SELECT INTO or INSERT INTO to copy data from a masked column into another table results in masked data in the target table.
• Dynamic Data Masking is applied when running SQL Server Import and Export. A database containing masked columns will result in an exported data file with the masked data and the imported database will contain statically masked data.
Querying for masked columns
Use the sys.masked_columns view to query for table-columns that have a masking function applied to them. This view is inherited from the sys.columns view. It returns all columns in the sys.columns view, plus the is_masked and masking_function columns, indicating if the column is masked, and if so, what masking function is defined. This view only shows the columns on which there is a masking function applied.
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function FROM sys.masked_columns AS c JOIN sys.tables AS tbl ON c.[object_id] = tbl.[object_id] WHERE is_masked = 1;
Limitations and restrictions
You cannot define a data masking rule for the following column types:
• Encrypted columns (always encrypted)
• A mask cannot be configured on a computed column, but if the computed column depends on a column with a MASK, then the computed column will return masked data.
• A column with data masking cannot be a key for a FULLTEXT index.
For users without the UNMASK permission, the deprecated READTEXT, UPDATETEXT, and WRITETEXT statements do not function properly on a column configured for Dynamic Data Masking.
CREATE TABLE Example (CodeID int IDENTITY PRIMARY KEY, FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, LastName varchar(100) NOT NULL, Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL); INSERT Example (FirstName, LastName, Phone, Email) VALUES ('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'), ('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'), ('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net'); SELECT * FROM Example;
The result demonstrates the masks by changing the data from:
1 Roberto Tamburello 555.123.4567 RTamburello@contoso.com
1 RXXXXXXX Tamburello xxxx RXXX@XXXX.com
Adding or Editing a Mask on an Existing Column
Use the ALTER TABLE statement to add a mask to an existing column in the table, or to edit the mask on that column.
The following example adds a masking function to the LastName column:
ALTER TABLE Example ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');
Dropping a Dynamic Data Mask
The following statement drops the mask on the LastName column created in the previous example:
ALTER TABLE Membership ALTER COLUMN LastName DROP MASKED;