Securing Sensitive Data with Dynamic Data Masking.
Data protection is critical in this age of growing cyber security threats. Dynamic Data Masking shows up as a potent remedy that enables businesses to restrict who has access to sensitive data without sacrificing functionality. By guiding you through the complexities of Dynamic Data Masking, you will be able to strengthen the security of your database.
Table of Contents
Introduction
Sensitive data can be instantly obscured using SQL Server’s Dynamic Data Masking (DDM) security feature. This guarantees that information that is hidden or fictitious is visible to unauthorized users, while the original data is still accessible to authorized users. By taking this proactive step, data privacy can be improved without affecting the underlying data.
A Glimpse into History
Dynamic Data Masking added a database-level layer of security in response to the growing concern over data breaches when it was first introduced in SQL Server 2016. It has improved with later iterations, now providing greater customization choices and improved compatibility with various database architectures.
Advantages of Dynamic Data Masking in SQL:
The advantages of SQL Server Dynamic Data Masking are given below:
Enhanced Data Privacy
By shielding private information from unauthorized users, Dynamic Data Masking (DDM) greatly improves data privacy. This feature aids businesses in protecting sensitive information and adhering to privacy laws.
Real-time Masking
When sensitive data is queried, DDM applies dynamic masking to it in real time. This reduces the possibility of unauthorized exposure by guaranteeing that only authorized users view the actual data.
Easy Implementation
Neither the application code nor the database schema needs to be significantly altered to implement DDM. A seamless integration process is made possible by this implementation’s simplicity.
No Effect on Underlying Data
DDM makes no changes to the data that is kept in the database. Because the original data is still present, data integrity is maintained and backup and restore processes can be completed without difficulty.
Granular Control
The masking functions and the columns that are masked are applied at the administrative level. This enables businesses to customize their data security plans according to the level of sensitivity of each column.
Application Compatibility
DDM ensures that authorized users can easily interact with the data because it is transparent to applications. The functionality of currently running applications is maintained because the underlying database structure is unaltered.
Role-based Customization
Organizations can define distinct masking configurations for different user roles by utilizing Dynamic Data Masking’s support for role-based customization. This adaptability guarantees that users with varying levels of access view appropriately masked data.
Regulation Compliance
With DDM in place, organizations can more easily comply with regulations, particularly those that have stringent data protection requirements. It is an effective instrument for attaining and proving compliance.
Disadvantages of Dynamic Data Masking in SQL
The disadvantages of SQL Server Dynamic Data Masking are given below:
Privileged User Visibility
Database administrators and other users with elevated privileges can get around Dynamic Data Masking and view the original data. Even though it’s deliberate, more steps are needed to limit access to privileged users.
Potential Performance Impact
A Look at Dynamic Data Performance of queries may be slightly impacted by masking, particularly when complex masking functions are used on big datasets. Potential effects on performance must be evaluated and mitigated by organizations.
Complex Implementation
User roles and data relationships must be carefully considered when configuring dynamic data masking. To make sure the implementation satisfies security and usability requirements, organizations must devote time to planning and testing.
User Education Requirement
To prevent confusion, end users must be informed about dynamic data masking. It is crucial to comprehend the reasoning behind masking and its goal to avoid unintentional data exposure.
Restricted Assistance with Specific Column Types
XML and CLR user-defined types are two examples of column types that may have restrictions even though DDM supports a large variety of data types. Examining the documentation for particular considerations is essential.
Not a Replacement for Encryption
DDM cannot take the place of encryption. Sensitive data should still be encrypted to guarantee protection both in transit and at rest, even though it protects data visibility. It is frequently advised to use encryption and DDM in tandem.
Possibility of inconsistent data
Dynamic Data Masking can cause inconsistent data presentation if it is not used carefully. To prevent inadvertent data exposure, organizations need to validate and test masking configurations thoroughly.
Permissions for Masking Data
CONTROL Permission for the Schema
On the schema containing the masked table, the user must have CONTROL permission. Changing the table structure and using Dynamic Data Masking require this permission.
REFERENCES Authorization for the Masking Function
When using any masking functions, the user must have permission from REFERENCES. With this permission, the user can configure Dynamic Data Masking by referencing and using the designated masking functions.
ALTER Authorization
The client needs to have ALTER authorization on the table that contains the section that should be covered to apply dynamic information veiling. With this authorization, the client can add or change covering rules as well as adjust the table’s design.
Permissions for Data Unmasking:
UNMASK Permission on Column
UNMASK permission on the particular column is required in order to unmask data. The user can view the unmasked data with this permission. All that is visible to users without this permission is the masked data.
VIEW DEFINITION – Authorization for the Masking Function
When using any masking functions, users must have the VIEW DEFINITION permission in order to attempt to unmask data. With this permission, users can view the masking function’s definition without seeing the actual masked data.
SELECT Table Permission
Users need to have the SELECT permission on the table because unmasking entails querying the data. Nevertheless, the UNMASK permission on the particular column is still necessary in order to view unmasked data; simply possessing the SELECT permission is insufficient.
Syntax
The MASKED WITH clause must be used inside the CREATE TABLE or ALTER TABLE statements to implement dynamic data masking. Observe this simple example:
CREATE TABLE TestDB.dbo.mCreditCard
(
CardID INT IDENTITY(1,1) PRIMARY KEY,
CreditCardNo NVARCHAR(16) MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)') NULL,
CardExpiryDate DateTime
);Parameters
FUNCTION: Indicates the masking function (partial, email, random, etc.) that will be used.
Expression: Specifies the masking logic about the function of choice.
Different Types of Dynamic Data Masking
Sensitive data columns in SQL Server can have a variety of masks applied to them thanks to Dynamic Data Masking (DDM). Based on user permissions, these masks aid in limiting the disclosure of sensitive information. The primary categories of dynamic data masking in SQL Server are as follows:
Default Mask
If a sensitive column has no defined mask, the default mask is used. ‘X’ or ‘0’ are common fixed characters that are used in place of characters. Should the source data be “SensitiveData,” for instance, the default mask may present it as “XXXXXXXXXXX.”
Example: Suppose you have a table with credit card numbers in it. You wish to apply the default mask to the “Credit Card Number” column of the table.
-- Create a table for mVISACustomers
CREATE TABLE mVISACustomers (
VISACustomerID INT PRIMARY KEY,
VISACustomerName NVARCHAR(100),
VISA_CCNumber NVARCHAR(20) MASKED WITH (FUNCTION = 'default()')
);
-- Insert sample data in mVISACustomers table
INSERT INTO mVISACustomers VALUES
(1, 'John Doe', '5234-4545-6789-4567'),
(2, 'Jane Smith', '9487-4365-4321-3654'),
(3, 'John Cena', '2343-4792-2690-3479');
CREATE USER NewUserWithMaskedData WITHOUT Login;
GRANT SELECT ON mVISACustomers TO NewUserWithMaskedData;
EXECUTE AS User= 'NewUserWithMaskedData';
SELECT * FROM mVISACustomers
REVERT;
SELECT * FROM mVISACustomers
DROP USER NewUserWithMaskedData;
DROP TABLE mVISACustomers;
In this example, the MASKED WITH (FUNCTION = ‘default()’) syntax is used to apply the default mask to the “CreditCardNumber” column. The default mask typically replaces characters with a fixed character, such as ‘X’.
Email Mask
Columns with email addresses should use the email mask. It hides the remaining characters with asterisks and only shows the initial character of the email and the domain. An email address such as “kumar.gupta@gmail.com” could be disguised as “k**********@gmail.com,” for instance.
Example: The email mask will be used for an email address column in this example.
-- Create a table for mVendors
CREATE TABLE mVendors (
VendorID INT PRIMARY KEY,
VendorName NVARCHAR(50),
VendorEmail NVARCHAR(150) MASKED WITH (FUNCTION = 'email()')
);
-- Insert sample data in mVendors table
INSERT INTO mVendors VALUES
(1, 'M/S TriVelley Ple', 'Info@TriVelley.com'),
(2, 'M/S Microsoft Corporation', 'Info@Microsoft.com'),
(3, 'M/S Infosys Limited', 'Info@Infosys.com'),
(4, 'M/S DishTV India Limited', 'Info@dishtv.in');
-- Create a user without login & grant SELECT access on mVendors table
CREATE USER NewUserWithMaskedData WITHOUT Login;
GRANT SELECT ON mVendors TO NewUserWithMaskedData;
EXECUTE AS User= 'NewUserWithMaskedData';
SELECT * FROM mVendors
REVERT;
--Select without masking
SELECT * FROM mVendors
DROP USER NewUserWithMaskedData;
DROP TABLE mVendors;
Here, we’re applying an email mask to the “Email” column using the email() function. The remainder of the mask will be hidden, leaving only the domain and the first letter visible.
Random Mask
The original data’s length and format are preserved when characters are substituted with random ones using the random mask. This offers a safer method of disguising private data. As an illustration, the initial data “987-54-40381” could be disguised as “and-21-acb34”
Example: In this example, we’ll use a random mask to mask Aadhar Number.
-- Create a table for Citizenship
CREATE TABLE mCitizenship (
CitizenID INT PRIMARY KEY,
CitizenFullName NVARCHAR(150),
AadharNumber bigint MASKED WITH (FUNCTION = 'random(1, 1)')
);
-- Insert sample data in Citizenship table
INSERT INTO mCitizenship VALUES
(1, 'John Doe', '523445456783'),
(2, 'Jane Smith', '948743654324'),
(3, 'John Cena', '234347922692');
CREATE USER NewUserWithMaskedData WITHOUT Login;
GRANT SELECT ON mCitizenship TO NewUserWithMaskedData;
EXECUTE AS User= 'NewUserWithMaskedData';
SELECT * FROM mCitizenship
REVERT;
--Select without masking
SELECT * FROM mCitizenshipHere, we’re applying a random mask to the “AadharNumber” column using the random(1, 1) function. The 1 indicates that the digit-level randomization is to take place.
Custom String Mask
You can specify a custom string to replace the original data with the help of the custom string mask. When displaying a consistent format or pattern without disclosing the actual sensitive information, can be helpful. One possible replacement for a credit card number would be a custom string, such as “–*3689.”
Example: In this example, we’ll use a custom string mask to mask GST Numbers with a predefined string.
-- Create a sample table
CREATE TABLE mClients (
ClientID INT PRIMARY KEY,
ClientFullName NVARCHAR(150),
ClientGSTNumber NVARCHAR(15) MASKED WITH (FUNCTION = 'default()')
);
-- Insert some sample data
INSERT INTO mClients VALUES
(1, 'Alice Johnson', '12AJDG749JDU0J9'),
(2, 'Bob Smith', '22A4YR63J77FMJ3');
CREATE USER NewUserWithMaskedData WITHOUT Login;
GRANT SELECT ON mClients TO NewUserWithMaskedData;
EXECUTE AS User= 'NewUserWithMaskedData';
SELECT * FROM mClients
REVERT;
In this case, the “GSTNumber” column’s default mask is being used. When masking, the default() function will apply the default behavior, which is to replace characters with a fixed character (such as ‘X’).
Partially Covered
Using a partial mask, sensitive data can be partially displayed while the remaining portion is hidden. The partial function is used to define it, along with the starting position, the number of characters to display, and the masking string to use. The first two and last four characters of a Social Security number, such as “349-15-1025,” might be displayed as “34*25″ in a partial mask, for instance.
Example: SQL Server’s Partial Dynamic Data Masking (DDM) feature lets you expose just a portion of the sensitive data while hiding the remainder. Let’s look at an example where we wish to hide some of the Social Security Numbers (SSNumbers) in a table called “mCustomer”:
-- Create a table for mCustomer
CREATE TABLE mCustomer (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(150),
SSNumber NVARCHAR(11) MASKED WITH (FUNCTION = 'partial(4,"XXX-XX-",0)')
);
-- Insert sample data in mCustomer table
INSERT INTO mCustomer VALUES
(1, 'John Doe', '123-45-6789'),
(2, 'Jane Smith', '987-65-4321');
CREATE USER NewUserWithMaskedData WITHOUT Login;
GRANT SELECT ON mCustomer TO NewUserWithMaskedData;
EXECUTE AS User= 'NewUserWithMaskedData';
SELECT * FROM mCustomer
REVERT;
SELECT * FROM mCustomer
DROP USER NewUserWithMaskedData;
DROP TABLE mCustomer;
In this example, we define a partial mask for the “SSNumber” column using the partial function. As the function is defined as partial(5, “XXX-XX-“, 0), the first five characters will be revealed, then “XXX-XX-“, and the remaining characters will be hidden.
Mask of Function
You can apply unique masking logic specified by a user-defined function with the function mask. This gives designers more freedom to create intricate masking patterns that meet particular business needs.
SQL Server Pivot: Top 5 Concepts
How to Use Dynamic Data Masking in SQL Server
A security feature in SQL Server called Dynamic Data Masking (DDM) helps shield sensitive data by preventing unauthorized users from accessing it. Depending on the user’s permissions, it dynamically masks the data in the query’s result set. Using Dynamic Data Masking in SQL Server can be done as follows:
How to Turn on Dynamic Data Masking
Recognize delicate columns:
Choose the database columns that you wish to hide from view because they may contain sensitive information.
Connect the server using SSMS:
Connect to your SQL Server instance by opening SSMS.
Choose the Database:
Choose the database where you wish to use dynamic data masking in the Object Explorer.
Get Dynamic Data Masking enabled:
Click the database with a right-click, select “Tasks,” and then select “Dynamic Data Masking.”
To enable DDM for the database, select “Enable”.
Describe the Masking Rules:
Once DDM is enabled, you must specify masking rules for individual columns.
On the table with the sensitive data, perform a right-click, select “Tasks,” and then select “Dynamic Data Masking.”
To find the columns containing sensitive data, select “Discover Sensitive Columns”.
Determine the masking rules according to what you need.
Examples
Example 1: Create table with masked few columns
--Check whether the database is available or not
use master
go
IF EXISTS(SELECT 1 FROM sys.databases WHERE Name='Student')
BEGIN
DROP DATABASE Student;
END
--Create a database for DDM testing
CREATE DATABASE Student;
--Check whether the required table is available or not.
go
Use Student;
go
IF EXISTS(SELECT 1 FROM sys.objects WHERE Name='mStudents')
BEGIN
DROP TABLE mStudents;
END
--Create a database for DDM testing
CREATE TABLE Student.dbo.mStudents
(
StudentID INT IDENTITY(1,1) PRIMARY KEY,
StudentName varchar(100) MASKED WITH (Function = 'default()'),
MotherName varchar(100) MASKED WITH (Function = 'partial(1,"XXX",1)'),
AadharNo NVARCHAR(16) MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)') NULL,
Email varchar(100) MASKED WITH (function = 'email()'),
AdmissionDate date MASKED WITH (Function = 'default()'),
);
--Insert few records as per table structure.
DECLARE @Date DateTime
SET @Date=GETDATE()
INSERT INTO Student.dbo.mStudents VALUES('Sunil Kumar','J N Devi','2345-5467-1570','sumil126@gmail.com',@Date);
INSERT INTO Student.dbo.mStudents VALUES('Kumar Amit','Shakuntala Devi','4356-4590-6880','KumAmit56@gmail.com',@Date);
INSERT INTO Student.dbo.mStudents VALUES('Tannu Sharma','Annu Sharma','6733-2690-3690','tsharma.mca@yahoo.com',@Date);
INSERT INTO Student.dbo.mStudents VALUES('Kavya Singh','Sonal Singh','2345-1479-1270','s.singh@hotmail.com',@Date);
INSERT INTO Student.dbo.mStudents VALUES('Tarun Gupta','Anita Gupta','4704-9626-0279','gupta.a1991@gmail.com',@Date);
--Create a user to retrieve the data from the table:
CREATE USER NewUserWithMaskedData WITHOUT Login;
GRANT SELECT ON mStudents TO NewUserWithMaskedData;
--Try to fetch the data using above user:
EXECUTE AS User= 'NewUserWithMaskedData';
SELECT * FROM mStudents
REVERT
FAQs
Q: Can privileged users get around Dynamic Data Masking?
Ans: The original data is visible to users with elevated privileges, like database administrators. The purpose of DDM is to prevent unwanted access.
Q: Does data stored in a database get affected by dynamic data masking?
Ans: No, DDM only has an impact on the information that users see. The underlying information doesn’t change.
Q: Is it possible to apply Dynamic Data Masking to current databases without compromising data integrity?
Ans: The ALTER TABLE statement can be used to apply Dynamic Data Masking to databases that already exist. Without changing the underlying data structure, it adds masking.
Q: Does Dynamic Data Masking have any restrictions on the kinds of columns that it can mask?
Ans: Most data types can be hidden, but there might be restrictions on some column types, like XML and CLR user-defined types. Examining the documentation for particular considerations is essential.
Q: Is it possible to mask data in result sets from intricate queries or views using Dynamic Data Masking?
Ans: Complex queries and views can both benefit from the use of dynamic data masking. Based on the underlying tables’ masking configuration, the masking logic is applied to the result sets.
Q: What is the relationship between backup and restore operations and dynamic data masking?
Ans: The actual data kept in the database is unaffected by DDM. The original data is kept safe when backing up or restoring. To avoid unwanted access, backup files should be carefully secured.
Q: Is it feasible to integrate encryption or other security features with dynamic data masking?
Ans: You can combine Dynamic Data Masking with other security measures like Transparent Data Encryption (TDE) for a multi-layered approach to data protection. Each security feature addresses a different aspect of data security.
Q: Does Dynamic Data Masking have any impact on application performance?
Ans: When applying complex masking functions to large datasets, DDM may have a minor performance impact. To determine the precise impact on your environment, performance testing is advised.
Q: Is it feasible to alter the masking format according to the permissions or roles of the user?
Ans: Yes, role-based customization is possible with Dynamic Data Masking. Distinct masking configurations can be assigned to different users or roles, enabling organizations to customize data visibility according to particular user privileges.
Q: Is it possible to use row-level security in conjunction with dynamic data masking?
Ans: It is possible to create a complete security framework by combining row-level security and dynamic data masking. Access to rows is managed by row-level security, whereas sensitive column data is concealed by DDM.
Q: How does Dynamic Data Masking handle sorting and filtering on masked columns?
Ans: The original data, not the masked values, is used for sorting and filtering. By maintaining the integrity of query results, DDM makes sure that users see the results in the right order and can filter them based on the real data.
Q: Does Dynamic Data Masking impact triggers and stored procedures?
Ans: The answers to queries, including those from stored procedures and triggers, are subject to dynamic data masking. The result sets include the masked data.
Q: Does using Dynamic Data Masking affect indexing in any way?
Ans: Indexing is unaffected by dynamic data masking in and of itself. Organizations should be conscious, though, that the masking logic may have an impact on index selectivity and hence query performance.
Q: Is it possible to use Dynamic Data Masking with an Azure SQL Database?
Ans: The Azure SQL Database does support dynamic data masking. The implementation and usage are similar to on-premises SQL Server, providing a consistent approach to data masking in the cloud.
Q: Is it possible to extend Dynamic Data Masking to external applications that utilize the SQL Server database?
Ans: Dynamic Data Masking can aid third-party applications that communicate with the SQL Server database. Applying the masking logic at the database level guarantees uniform data protection.
Q: How do data types other than strings and numbers get handled by dynamic data masking?
Ans: Date and binary data types are supported by dynamic data masking. The application of masking functions is contingent upon the distinct attributes of individual data types.
Q: Is it possible to utilize Transparent Data Encryption (TDE) in conjunction with Dynamic Data Masking?
Ans: It is possible for transparent data encryption and dynamic data masking to work in tandem. While DDM concentrates on regulating data visibility in real time, TDE secures data while it is at rest.
Q: Is it possible to replace conventional access controls and permissions with Dynamic Data Masking?
Ans: No, access controls are still necessary in addition to dynamic data masking. It is an extra safety measure against unauthorized data exposure that supplements the current permissions system.
Q: Is Dynamic Data Masking a substitute for traditional access controls and permissions?
Ans: No, Dynamic Data Masking is not a substitute for access controls. It is an additional layer of security that complements existing permissions, providing an extra safeguard against unauthorized data exposure.
Q: How does Dynamic Data Masking handle case sensitivity in masking functions?
Ans: Masking functions are case-insensitive. The specified masking function, such as ‘partial()’, will work regardless of whether it is written in uppercase or lowercase.
Conclusion
Dynamic Data Masking is a game-changer in the realm of database security, offering a robust solution to protect sensitive information. By understanding its syntax, advantages, and practical implementation, you’re equipped to enhance your data protection strategies. Elevate your database security with Dynamic Data Masking and fortify your defenses against potential threats!
Review the below articles also
DBCC Freeproccache in SQL Server: A powerful command
Understand Deadlocks in SQL Server
Unleash Database Insights with Extended Events in SQL Server
SQL Server Pivot: Top 5 Concepts
Unleash Database Insights with Extended Events in SQL Server: A Deep Dive
A Powerful Merge Statement in SQL Server
Deadlocks in SQL Server : Understanding and Resolving Database Concurrency Issues