When defining a security policy for an application, one of the most common challenges is limiting unauthorized access to sensitive information.
For example, you may want your Marketing staff to have full access to all customer and prospect information. But, your Finance department to only be able to access information pertaining to collections, or your managers to only be able to search for their accounts.
If you’ve ever worked with SQL Server at Microsoft, you’ve probably had to define what information your different departments have access to or the ability to edit it.
Today, these techniques are known as Data Masking. An especially useful and accessible technique for all types of companies thanks to Dynamic Data Masking.
Dynamic Data Masking (hence its acronym DDM) is a technique to protect sensitive information from unwanted eyes. It allows you to specify the extent of data accessible for a user without the necessary permissions.
The DDM has the advantage that can be applied at any time. Even with an application that is already running, without the need to change any existing settings or action orders.
The four most common types of data that you want to keep secret are:
The data that can be used to identify certain profiles. Include full names or personal document numbers (DNI, driver’s license and Social Security, among others).
Data collected by health service providers, private or public, to identify the necessary treatments. It includes insurance information, demographics, test results, and medical records.
The information we give to online stores when making a purchase from the web. It falls within the Payment Card Industry Data Security Standard (PCIDSS) to ensure the security of cardholder data.
the information related to inventions, business plans, designs and construction plans. These are highly valuable to a business and must be protected against theft and unauthorized access.
There are actually three common types of data masking within data libraries:
One of the solutions that we would recommend in the event that the database engine used by the application is SQL Server (very common in all types of sectors) would be the use of Dynamic Data Masking available in SQL Server in its SQL versions Server 2016 (13.x) and later such as Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.
To enable this feature we only have to define a mask based on the data type of the column.
This mask is created in the column definition itself in T-SQL by applying a function that returns a masked value instead of the actual value of the column:
Function | Datatype | Description | T-SQL Column definition |
Default() | All | Applies a default value to all columns, depending on the data type: String to xxx Numeric to 0 Date and Time: 01.01.1900 00:00:00 | Telephone varchar(20) MASKED WITH (FUNCTION = ‘default()’) |
Email() | String | Return the first letter of the value by adding the following pattern:{P}xxxx@XXX.com | Email varchar(100) MASKED WITH (FUNCTION = ’email()’) |
Partial | String | Return the first N characters and the last N characters applying a padding: Rxxxxxxxo | Surnames varchar(100) MASKED WITH (FUNCTION = ‘partial(1, “xxxxxx”, 1)’) |
Random | Numeric | Returns a random value within an interval | Gross Salary decimal (18,2) MASKED WITH (FUNCTION = ‘random(1,999999)’) |
Datetime (1) | Date and Time | Mask the part of the day that is required: Year at datetime (“Y”) Month at datetime (“M”) Date at datetime (“D”) Datetime time (“h”) Minute to datetime (“m”) Second to datetime (“s”) (1) Only available in Sql Server 2022 | DateofBirth ADD MASKED WITH (FUNCTION = ‘datetime(“M”)’) |
DDM has the option to grant different levels of permissions to view the unmasked values with different granularities: Database, Schema, Table and even column:
Level | T-SQL |
Database | GRANT UNMASK TO {User}; |
Schema | GRANT UNMASK ON SCHEMA::{Schema} TO {User} |
Table | GRANT UNMASK ON {Schema.Table} TO {User}; |
Column | GRANT UNMASK ON {Schema.Table}(Column) TO {User} |
In the Azure portal, within the options menu for managing a SQL Server database, we have the option ‘Dynamic data masking’. It allows us to carry out in a very simple way the administration of the masks for any existing column, without having to know the T-SQL syntax that we have seen previously:
When clicking on the action (2) ‘Add mask’, Azure will present us with a screen where we can create the mask by previously selecting the Schema, Table and Column. Action (3) will display the same screen preselecting the previous values, starting from the suggested column.
On the screen we will finally be presented with the list of available formats depending on the data type of the column:
Format Type | Data Type | Display |
Default value | All | ![]() |
Credit card | String | ![]() |
String | ![]() | |
Random range | Numeric | ![]() |
Custom chain | String | ![]() |
Let’s observe a aDDM’s example with all the examples that we have described in the previous table
First, we create the Employees table; masking the different columns we have seen:
A user with SELECT and UNMASK permission on that table would obtain the following values:
On the other hand, a user with SELECT permission, but without UNMASK permission, would obtain the same rows, but with masked values in each of the columns defined with dynamic mask, applying the established masking function to each column:
Here we leave you the same example described in text with the most important parts highlighted
CREATE TABLE Employees (
EmployeeID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Name varchar(50) MASKED WITH (FUNCTION = 'partial(1, "xx", 1)') NULL,
Surname varchar(50) MASKED WITH (FUNCTION = 'partial(1, "xxxxxx", 1)') NULL,
Telephone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email varchar(50) MASKED WITH (FUNCTION = 'email()') NOT NULL,
INSERT INTO Employees (Name, Surname, Telephone, Email, GrossSalary)
VALUES
('Antonio', 'González López', '612999888', 'antonio.gonzalez@contoso.es', 2000),
('Juan', 'Valdés Rovira', '612444555', 'juan.valdes@contoso.com', 2500),
('Luis', 'Torres Sánchez', '612333222', 'luis.torres@contoso.net', 3000)
CREATE USER MaskingTestUser WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::dbo TO MaskingTestUser;
EXECUTE AS USER = 'MaskingTestUser';
SELECT * FROM dbo.Employees
CREATE TABLE Employees
(
UserID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Name varchar(50) NULL,
Surname varchar(50) NULL,
Telephone varchar(12) NULL,
Email varchar(50) NOT NULL,
DateBirth datetime NULL,
IBAN varchar(50) NULL,
GrossSalary decimal(18,2) NULL
);
INSERT INTO Employees (Name, Surname, Telephone, Email, DateBirth, IBAN, GrossSalary)
VALUES
('Antonio', 'González López', '612999888', 'antonio.gonzalez@contoso.es', '1990-07-11', 'ES0220958524201618721727', 2000),
('Juan', 'Valdés Rovira', '612444555', 'juan.valdes@contoso.com', '1980-01-29', 'ES6320804148384145618563', 2500),
('Luis', 'Torres Sánchez', '612333222', 'luis.torres@contoso.net', '1985-06-15', 'ES9201826168304876446786', 3000)
To finish, we can comment on some advantages of the DDM that we have seen throughout the article:
It is also important to clarify that dynamic masking used in isolation does not fully guarantee the protection of confidential information. It is recommended to use tools to properly manage permissions in the database, always applying the principle of granting the minimum required permissions, enabling other security mechanisms such as Audit and Encryption.
If you want to know more about how to take advantage of your DDM techniques within Microsoft suites, contact us.