Data masking is a feature that allows you to completely or partially mask selected data in a database. Access to unmask the data can also be granted or revoked for individual users. Data masking and anonymization have become increasingly important topics, especially with the introduction of GDPR regulations by the European Commission, which require greater attention to the protection of personal data.
This feature is available in SQL Server versions 2016 and newer and now I’ll provide a more detailed overview of this feature.
How Does Data Masking Work?
Masking in SQL Server is relatively straightforward. Unlike row-level security, which controls access permissions at the individual row level in a table, data masking focuses on entire columns. To mask a specific column, you use a masking function, and you can also allow unmasking of data for specific users. The basic process involves:
- Identifying the columns in various tables that you want to mask
- Applying masking to those columns. You have four masking options to choose from:
- default – the attribute value is displayed as “XXX”
- email – email addresses are displayed as GXXXXX@XXX.com
- partial – you specify the number and position of characters to be masked
- random – used to mask numeric values by selecting a random numeric value within a specified range
- If you need to unmask data for certain users, you grant UNMASK permissions
Privileged users (sysadmins, db_owners) always see unmasked data. Masked data can be used for all operations as before, including filtering, etc.
Masking and Unmasking a Column – MASKED WITH
To mask a specific attribute, you can use the following SQL script. Data in the specified column will then be displayed as “XXX”.
ALTER TABLE Employees
ALTER COLUMN Last_Name VARCHAR(255) MASKED WITH (FUNCTION = ‘default()’);
To unmask a column, you can use the following SQL script:
ALTER COLUMN Last_Name VARCHAR(255) DROP MASKED;
Assigning and Revoking UNMASK Permissions – GRANT/REVOKE UNMASK
To allow a user or role to see unmasked data, you can use the following command:
GRANT UNMASK TO <user>;
To revoke these permissions, you can use the following command:
REVOKE UNMASK TO <user>;
Permissions are granted or revoked at the database level. You cannot choose specific objects or columns for which you want to control permissions. Therefore, data is either masked or unmasked for a specific user for all objects in the database.
If you need to handle data visibility at the individual column level, you must use traditional access control, specifically Column level permissions – for more details, refer to this link.
