Dynamic Data Masking (DDM) in SQL Server

 Dynamic Data Masking (DDM) in SQL Server provides a way to obfuscate sensitive data in query results, ensuring that users without proper permissions cannot view the actual data. This feature can be used to limit exposure of sensitive data by controlling how the data appears to non-privileged users.

Types of Masks

1. Default: Full masking according to the data types (e.g., 0 for numeric types, 'XXXX' for strings).
2. Email: Masks all but the first character of an email address in the form aXX@XXXX.com.
3. Custom String: Masking format of your choice with a specified prefix and suffix, with the middle part replaced by a custom padding string.
4. Random: A random value within a specified range for numeric types.

Steps to Implement Dynamic Data Masking

Step 1: Create a Table with Sensitive Data

Create a table with columns that you want to mask.

sql
USE YourDatabase; -- Replace with your database name
GO

CREATE TABLE CustomerData (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Email NVARCHAR(50),
    CreditCardNumber NVARCHAR(16)
);
GO

-- Insert sample data
INSERT INTO CustomerData (CustomerID, Name, Email, CreditCardNumber)
VALUES 
(1, 'John Doe', 'john.doe@example.com', '1234567812345678'),
(2, 'Jane Smith', 'jane.smith@example.com', '8765432187654321');
GO

Step 2: Add Masks to the Columns

Apply dynamic data masking to the columns.

sql
ALTER TABLE CustomerData
ALTER COLUMN Name ADD MASKED WITH (FUNCTION = 'default()');
GO

ALTER TABLE CustomerData
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
GO

ALTER TABLE CustomerData
ALTER COLUMN CreditCardNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');
GO

Step 3: Create a User and Grant Permissions

Create a user and grant permissions to see the effect of the masking.

sql
-- Create a user for testing
CREATE USER TestUser WITHOUT LOGIN;
GO

-- Grant select permission on the table
GRANT SELECT ON CustomerData TO TestUser;
GO

Step 4: Query the Data as the Test User

To see the masked data, execute a query as the TestUser.

sql
-- Impersonate the TestUser
EXECUTE AS USER = 'TestUser';
GO

-- Query the table
SELECT CustomerID, Name, Email, CreditCardNumber
FROM CustomerData;
GO

-- Revert to the original user
REVERT;
GO

Expected Results

For a user without special permissions, the query results will be masked as follows:

plaintext

CustomerID | Name     | Email              | CreditCardNumber
-------------------------------------------------------------
1          | XXXX     | jXXX@XXXX.com      | XXXX-XXXX-XXXX-5678
2          | XXXX     | jXXX@XXXX.com      | XXXX-XXXX-XXXX-4321

Additional Considerations

• Unmasking Data: Users with the UNMASK permission can see the unmasked data.
• Security: DDM is not a security feature to protect against malicious intent but is useful for limiting data exposure.
• Applications: DDM is useful for production databases to prevent accidental exposure of sensitive data to non-privileged users.

Removing Dynamic Data Masking

To remove masking from a column, use the following syntax:

sql
ALTER TABLE CustomerData
ALTER COLUMN Name DROP MASKED;
GO

ALTER TABLE CustomerData
ALTER COLUMN Email DROP MASKED;
GO

ALTER TABLE CustomerData
ALTER COLUMN CreditCardNumber DROP MASKED;
GO

By following these steps, you can implement Dynamic Data Masking in SQL Server to help protect sensitive data from unauthorized viewing, providing an additional layer of security to your database.

Transparent Data Encryption (TDE)
Column-Level Encryption
Always Encrypted
Dynamic Data Masking (DDM)
Transport Layer Security (TLS)