Data masking

Masking types

Static

Dynamic

What is data masking and how it works?

Data masking works by shielding confidential data, such as credit card information, Social Security numbers, names, addresses, and phone numbers, from unintended exposure to reduce the risk of data breaches

What is masking in SQL Server?

Encrypting SQL Server: Dynamic Data Masking. ... SQL Server 2016 introduced dynamic data masking (DDM) as a way to prevent unauthorized users from viewing certain types of sensitive information. The database engine masks the data when it is retrieved from the database, based on masking rules defined on the column schema.

In SQL Server, data masking is used to limit sensitive data exposure by obfuscating or hiding the actual values. This can be particularly useful for preventing unauthorized access to sensitive information, especially when users with limited privileges query the data.

SQL Server provides Dynamic Data Masking (DDM) as a feature that helps automatically mask sensitive data in the result set of queries, without changing the actual data in the database. The masks are applied when the data is selected, and users with special privileges can see the actual data.

Masking Types in SQL Server

SQL Server supports the following four types of data masking:

1. Default Masking: Full masking based on the data type.
2. Email Masking: Masks email addresses by showing only the first letter and masking the rest.
3. Custom String Masking: Masks part of the string with a custom format.
4. Random Masking: Masks numeric data with a random number within a specified range.

Example of Each Masking Type

1. Default Masking:

• This type of mask applies full masking for the data type.
• For STRING types: This mask returns a XXXX pattern.
• For NUMERIC types: This mask returns 0 values.

 CREATE TABLE Employees (

    EmployeeID INT IDENTITY(1,1),

    FullName NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'),

    SSN CHAR(11) MASKED WITH (FUNCTION = 'default()'),

    Salary MONEY MASKED WITH (FUNCTION = 'default()')

);

INSERT INTO Employees (FullName, SSN, Salary)

VALUES ('John Doe', '123-45-6789', 75000),

       ('Jane Smith', '987-65-4321', 85000);

 -- Query the masked data (assuming the user querying has limited access)

SELECT EmployeeID, FullName, SSN, Salary FROM Employees;

Output (Masked for unauthorized users):

EmployeeID	FullName	SSN	Salary
1	XXXX	XXX-XX-XXXX	0.00
2	XXXX	XXX-XX-XXXX	0.00

2. Email Masking:

• This mask is specifically for email addresses.
• Shows the first letter of the email address and masks the rest.

CREATE TABLE Users (

    UserID INT IDENTITY(1,1),

    Email NVARCHAR(255) MASKED WITH (FUNCTION = 'email()')

);

INSERT INTO Users (Email)

VALUES ('johndoe@example.com'),

       ('janesmith@example.com');

 

-- Query the masked data

SELECT UserID, Email FROM Users;

Output (Masked for unauthorized users):

UserID	Email
1	jXX@XXXX.com
2	jXX@XXXX.com

3. Custom String Masking:

• This allows you to show certain parts of the string while masking the rest.
• You can customize the prefix, suffix, and padding characters.

CREATE TABLE CreditCards (

    CardID INT IDENTITY(1,1),

    CardNumber NVARCHAR(16) MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)')

); 

INSERT INTO CreditCards (CardNumber)

VALUES ('1234567890123456'),

       ('9876543210987654');

-- Query the masked data

SELECT CardID, CardNumber FROM CreditCards;

Output (Masked for unauthorized users):

CardID	CardNumber
1	XXXX-XXXX-XXXX-3456
2	XXXX-XXXX-XXXX-7654

4. Random Masking:

• This mask applies only to numeric data types.
• It masks the value with a random number within a specified range.

CREATE TABLE Customers (

    CustomerID INT IDENTITY(1,1),

    Age INT MASKED WITH (FUNCTION = 'random(18, 65)')

);

 

INSERT INTO Customers (Age)

VALUES (25),

       (35),

       (50);

-- Query the masked data

SELECT CustomerID, Age FROM Customers;

Output (Masked for unauthorized users):

CustomerID	Age
1	47
2	32
3	51

Note: Each time you query, the values will be randomized within the range.

Key Points:

1. Permissions: Users with the UNMASK permission can see the actual data without the mask.
2. Security: Data is masked when it is queried by unauthorized users, but the actual data is not modified or encrypted.
3. Data types: Masks can only be applied to certain data types, such as CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, VARBINARY, NUMBER, and DATETIME types.
4. Limitations: You cannot mask columns with keys (primary, foreign, or unique constraints).

Managing Data Masking

Grant UNMASK Permission:

To view the actual data, you need to grant the UNMASK permission to the user.

GRANT UNMASK TO [username];

Remove Masking:

If you want to remove masking from a column:

ALTER TABLE Employees

ALTER COLUMN FullName DROP MASKED;