Configuring Dynamic Data Masking
SQL Server provides a security feature called Dynamic Data Masking (DDM), which prevents unauthorized users from viewing sensitive data during queries. DDM protects data by displaying some or all masked values instead of the actual data values in query results.
This section describes how to configure DDM on the RDS console or using native SQL Server functions.
Constraints
- To use this function, submit a service ticket to request required permissions.
- Only SQL Server 2016 and later versions support DDM.
- Only users who have the database-level CONTROL SERVER or CONTROL permission can view the original data. Such users include administrator users and roles, such as sysadmin and db_owner.
- You cannot define masking rules for the following column types:
- Encrypted columns (always encrypted)
- FILESTREAM
- COLUMN_SET or sparse columns that are part of a column set
- Computed columns. However, if a computed column relies on a column with a mask, the computed column returns masked data.
- Columns with data masking cannot be used as keys for FULLTEXT indexes.
- Columns in PolyBase external tables
- If you do not have the UNMASK permission, you cannot execute the deprecated READTEXT, UPDATETEXT, or WRITETEXT statements on columns configured for DDM.
- Adding a dynamic data mask is implemented as a schema change on the underlying table, and therefore cannot be performed on a column with dependency (for example, column referenced by a computed column).
If you add a dynamic data mask to a column with dependency, the error "ALTER TABLE ALTER COLUMN _columnname_ failed because one or more objects access this column" will be reported.
To resolve this problem, first remove the dependency, then add the dynamic data mask, and finally re-create the dependency. For example, if the dependency is due to an index relying on that column, you can delete the index, add the mask, and then re-create the dependent index.
- Each time an expression is projected and the expression references a column for which a data masking function is defined, the expression is also masked. Regardless of which masking function (default, email, random, or custom string) is used to mask the referenced column, any generated expression will always be masked using the default function.
Security Notes
Although dynamic data masking can effectively prevent accidental disclosure of sensitive data during access to production databases, non-privileged users with temporary query permissions may still obtain actual data using technical means.
For example, in the query statement SELECT Salary FROM Employees WHERE Salary = 10000, even if dynamic data masking rules are applied to the Salary column, the query result, such as xxx or other masked values, still allows inference that the actual salary is 10,000.
Procedure
- Click
in the upper left corner and select a region.
- Click
in the upper left corner of the page and choose Databases > Relational Database Service.
- On the Instances page, click the instance name to go to the Overview page.
- In the navigation pane, choose Databases and Accounts.
- On the Databases tab page, choose More > Dynamic Masking.
Figure 1 Databases
- On the displayed page, click Add Rule, enter a schema, table name, and column name, select a masking pattern, and click Save.
Figure 2 Dynamic masking
- After configuring the columns for dynamic masking, click OK.
- Prepare data.
- Create a database to store tables and data.
-- Create a sample database. CREATE DATABASE DemoDDM; GO -- Use the database. USE DemoDDM; GO
- Create a table and specify masking rules for columns.
-- Create a table that contains sensitive data and apply the masking rules. CREATE TABLE Employees ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR(50), SSN VARCHAR(11) MASKED WITH (FUNCTION = 'partial(1, "XXX-XX-", 4)'), CreditCard VARCHAR(19) MASKED WITH (FUNCTION = 'partial(4, "XXXX-XXXX-XXXX-", 4)') ); GO -- Insert some sample data. INSERT INTO Employees (Name, SSN, CreditCard) VALUES ('Alice', '123-45-6789', '1234-5678-9012-3456'), ('Bob', '234-56-7890', '9876-5432-1098-7654'); GO
- Verify the masking results.
-- Assume that you have the db_datareader role's permissions on the DemoDDM database but do not have the db_owner role's permissions on the database. SELECT * FROM Employees;
The command output should be similar to the following:
| Id | Name | SSN | CreditCard | |----|-------|----------------|-----------------------------| | 1 | Alice | 1XXX-XX-678 | 1234XXXX-XXXX-XXXX- | | 2 | Bob | 2XXX-XX-789 | 9876XXXX-XXXX-XXXX- |
- Create a database to store tables and data.
- Use dynamic data masking.
- Prepare table data for dynamic data masking.
If there is already a table, go to 2.b and run the ALTER TABLE command to apply masking rules to the existing columns.
-- Create a table. CREATE TABLE Customers ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR(50), Email VARCHAR(100), PhoneNumber VARCHAR(15) ); GO -- Insert some sample data. INSERT INTO Customers (Name, Email, PhoneNumber) VALUES ('Charlie', 'charlie@example.com', '123-456-7890'), ('Diana', 'diana@example.com', '987-654-3210'); GO
- Apply masking rules to columns in the table.
-- Apply a masking rule to the Email column. ALTER TABLE Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()'); GO -- Apply a masking rule to the PhoneNumber column. ALTER TABLE Customers ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'partial(5, "XXX-", 0)'); GO
- Execute a query to verify the masking results.
-- Assume that you do not have the required permission. SELECT * FROM Customers;
The command output should be similar to the following:
| Id | Name | Email | PhoneNumber | |----|--------|------------------------------|-----------------| | 1 | Charlie| cXXX@XXXX.com | 123-4XXX- | | 2 | Diana | dXXX@XXXX.com | 987-6XXX- |
- Prepare table data for dynamic data masking.
- For details about SQL Server dynamic data masking functions and permissions, see DDM Functions and Permissions.
The following examples demonstrate how to use dynamic data masking functions:
- default()
-- Use the default() function for a column. CREATE TABLE SensitiveData ( Id INT IDENTITY PRIMARY KEY, Value VARCHAR(50) MASKED WITH (FUNCTION = 'default()') ); GO -- Insert some sample data. INSERT INTO SensitiveData (Value) VALUES ('Confidential Information 1'), ('Confidential Information 2'); GO -- Query the table to check the masking results. SELECT * FROM SensitiveData;
The command output should be similar to the following:
| Id | Value | |----|---------------------------| | 1 | xxxx | | 2 | xxxx |
- random()
-- Use the random() function for a column. CREATE TABLE Accounts ( Id INT IDENTITY PRIMARY KEY, Balance DECIMAL(10, 2) MASKED WITH (FUNCTION = 'random(1000, 5000)') ); GO -- Insert some sample data. INSERT INTO Accounts (Balance) VALUES (1234.56), (4567.89); GO -- Query the table to check the masking results. SELECT * FROM Accounts;
The command output should be similar to the following:
| Id | Balance | |----|---------| | 1 | 3456.78 | | 2 | 1234.56 |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot