What exactly is Dynamic Data Masking?
Dynamic Data Masking (DDM) is a SQL Server security feature that helps secure sensitive data in real time by obscuring it from users who do not have the necessary access rights. It enables users to selectively mask sensitive data without changing the underlying data. This aids in data privacy and confidentiality protection in contexts where sensitive data is accessed by different people with varying levels of clearance.
This post will go over the concept of Dynamic Data Masking in SQL Server, as well as its benefits and examples.
How may Dynamic Data Masking be used?
- DDM can be used with one of four masking functions: default, email, random, or custom. These functions enable you to modify how the data is masked based on the data type and sensitivity level.
- Default Masking: Use the default masking function to mask sensitive data with a predetermined mask. For example, you can utilize the default masking mechanism to hide all but the final four digits of a credit card number.
- Email Masking: When you wish to mask an email address but keep it recognizable as an email address, you utilize the email masking function. For example, you can utilize the email masking tool to hide an email address's domain name.
- When you want to mask data with a random value, you use the random masking function. You can, for example, use the random masking function to replace a year in Date of Birth with a random value.
- Custom Masking: Use the custom masking function to build a bespoke mask for sensitive data. You can, for example, utilize the custom masking function to mask a phone number in a specific format.
How to create Masking functions?
Let's see, with an example, create a table with default, email, random, and custom masking functions and insert some records by running the following command.
--Create table with masking functions
CREATE TABLE dbo.DDM_TestTable(
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'PARTIAL(1, "xxx", 1)') NULL,
LastName VARCHAR(100) NOT NULL,
Phone VARCHAR(10) MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
Email VARCHAR(100) MASKED WITH (FUNCTION = 'EMAIL()') NOT NULL,
BirthYear SMALLINT MASKED WITH (FUNCTION = 'RANDOM(1000, 9999)') NULL
);
--Inser sample records
INSERT INTO dbo.DDM_TestTable (FirstName, LastName, Phone, Email, BirthYear)
VALUES ('Peter', 'Black', '9876543210', '[email protected]', 1982),
('Scott', 'Cassidy', '9128374650', '[email protected]', 1991),
('John', 'Lee', '9021873456', '[email protected]', 1989),
('Laura', 'M', '8907654321', '@hostforlife.eu">[email protected]', 1985);
Create a User and Grant the SELECT permission on the schema where the table resides by execution the following command.
CREATE USER DDMUser WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::dbo TO DDMUser;
Execute the query as the DDMUser to view masked data from the table.
EXECUTE AS USER = 'DDMUser'
SELECT * FROM dbo.DDM_TestTable
Benefits of Dynamic Data Masking
Organizations that need to protect sensitive data might benefit from Dynamic Data Masking in a variety of ways. Some of these advantages.
- Enhanced Security: Dynamic Data Masking shields sensitive data from unauthorized users by obscuring it. This aids in the prevention of data breaches and illegal access to critical information.
- Improved Compliance: Many firms must adhere to data privacy standards such as GDPR and HIPAA. Dynamic Data Masking assists enterprises in complying with these rules by preventing unauthorized access to sensitive data.
- Reduced danger: Dynamic Data concealing decreases the danger of data theft and other security breaches by concealing sensitive data. This helps to defend the organization's reputation and sustain customer trust.
- Increased Flexibility: Dynamic Data Masking can be used to mask data selectively based on user roles and permissions. This allows for more control over access to sensitive data.
What new DDM feature was introduced in SQL Server 2022(16.x)?
Datetime has a new masking function.
With the introduction of granular permission, we may grant or revoke UNMASK permission to a user or database role at the database, schema, table, or column level.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
