Dynamic Data Masking in Database
DYNAMIC DATA MASKING
Data security is a critical concern for all organizations dealing with sensitive information. Protecting this data is not just a matter of good business practice; it’s often a legal requirement. One of the ways to ensure data security is through a technique known as data masking. In this blog post, we’ll explore the concept of dynamic data masking, a powerful feature available in Microsoft SQL Server 2016 and later, as well as Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.
What is Dynamic Data Masking?
Dynamic Data Masking (DDM) is a security feature in databases that helps protect sensitive data by obscuring it at the query level. This feature ensures that unauthorized users can only see a masked version of the data while authorized users can access the original data. It is commonly used to prevent data leaks and enhance security in environments where sensitive information is handled.
How Does It Work?
DDM allows customers to specify how much sensitive data to reveal with minimal impact on their application layer. A central data masking policy acts directly on sensitive fields in the database, and privileged users or roles with access to the sensitive data can be specified.
DDM offers full and partial masking functions, including a random mask for numeric data. These masks are defined and managed using simple Transact-SQL commands.
When a query is executed against a column that has dynamic data masking applied, the database automatically returns masked data for users who do not have permission to view the original data. The masking is applied dynamically, meaning it happens at query time without altering the actual data in the database.
Key Features of Dynamic Data Masking
· Non-Intrusive: The original data remains unchanged; only the query results are masked.
· Role-Based Access: Only users with the appropriate permissions can bypass masking and see the full data.
· Transparent to Applications: Masking is enforced at the database level, requiring no changes to the application logic.
· Customizable: You can define how the data is masked based on the type of column and use case.
Masking Methods:
Most database systems (e.g., SQL Server, PostgreSQL, etc.) support the following types of masking:
Default Masking:
Replaces data with a default value based on the column's data type.
Example:
1. For strings: Replaced with "XXXX" or "****".
2. For numbers: Replaced with 0.
Custom String Masking:
Allows you to expose only part of the data while masking the rest.
Example: Masking an email address to a****@example.com.
Random Masking:
Generates a random value for the masked column, useful for numeric data.
Example: Replacing salary values with random numbers.
Full Masking:
Completely hides the data, often with a fixed placeholder like "XXXX" or "*****".
How to Implement DDM
1. Enable DDM When Creating a Table
You can define masking rules when creating a table.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FullName NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'),
Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
SSN CHAR(11) MASKED WITH (FUNCTION = 'partial(0, "XXX-XX-", 4)'),
Salary INT MASKED WITH (FUNCTION = 'random(30000, 80000)')
);
2. Add DDM to an Existing Table
You can apply masking to an existing column in a table using the ALTER TABLE statement.
ALTER TABLE Employees
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
3. Grant/Restrict Access to Unmasked Data
By default, masked data is visible to all users.
Only users with the UNMASK permission can see unmasked data.
Granting UNMASK Permission:
GRANT UNMASK TO [username];
Revoking UNMASK Permission:
REVOKE UNMASK FROM [username];
Examples
Query by Non-Privileged User:
Suppose the table has DDM configured for sensitive columns:
SELECT * FROM Employees;
Output for Non-Privileged User:
EmployeeID | FullName | SSN | Salary | |
1 | xxxx | XXX-XX-1234 | 56789 | |
2 | xxxx | XXX-XX-5678 | 42345 |
Query by Privileged User:
Privileged users (with UNMASK permission) will see unmasked data:
GRANT UNMASK TO AdminUser;
Output:
EmployeeID | FullName | SSN | Salary | |
1 | John Doe | 123-45-6789 | 56789 | |
2 | Alice Brown | 987-65-4321 | 42345 |
In PostgreSQL
PostgreSQL does not have native support for DDM, but you can implement it using view-based masking or extensions.
Example Using Views:
CREATE VIEW masked_employees AS
SELECT
ID,
Name,
LEFT(Email, 1) || '****' || '@example.com' AS Email,
'XXX-XX-' || RIGHT(SSN, 4) AS SSN
FROM Employees
WHERE current_user != 'admin';
Grant permissions to access the view, not the original table:
GRANT SELECT ON masked_employees TO public;
Example Use Cases:
Personally Identifiable Information (PII): Mask sensitive data such as Social Security Numbers, credit card numbers, or personal emails for non-privileged users.
Compliance: Help meet regulatory requirements like GDPR, HIPAA, or PCI DSS by limiting access to sensitive data.
Development and Testing: Developers or testers work with masked data to avoid exposing actual sensitive information.
Benefits:
Increased Security: Reduces the risk of data exposure.
Ease of Implementation: Can be configured without requiring changes to application code.
Cost-Effective: Minimizes the need for creating separate environments with anonymized datasets.
Conclusion
While DDM is a powerful tool, it’s not without its limitations. For instance, a masking rule can’t be defined for encrypted columns, FILESTREAM, COLUMN_SET or a sparse column that is part of a column set. Also, cross-database queries involving masked columns may not provide correct results.
In conclusion, Dynamic Data Masking is a valuable tool for enhancing data security. However, it’s not a standalone solution and should be used with other SQL Server security features to achieve comprehensive data protection.
