As a running up to the new release of SQL 2016 I would like to share some research I did about the new features that will be available. Today I want to talk about Dynamic Data Masking. It is a nifty, small but quite interesting new feature.
Books Online describes it as this:
Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.
So what does it not do? It’s not an encryption method. It really does what it says.
It’s easier to give an example. After saving a credit card into your paypal account, you will never see your full credit card number again on their website.
It will always be shown like XXXX XXXX XXXX 1234, although they know the full number. That is data masking. This method is now also available in SQL 2016. In this short article I would like to describe how you can configure this.
If you are working with sensitive data, like credit card numbers or social security numbers. It’s never a good idea to show them in your application. Since you never know who is watching your screen. A second benefit is that the data that is send over the network is worthless. If a hacker catches the data, he sees a lot of XXXXXXX and a small part of the real data. So the benefits all have to do with Security.
Microsoft has foreseen 4 different masking types:
Following example shows the use of all of them. Our data, a table that contains a list of all the employees in a certain company. It includes the account number where their wages are paid on, and there social security number for insurance reason.
CREATE TABLE Employee(
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(100),
LastName NVARCHAR(100),
Email NVARCHAR(100),
BankAccountNumber BIGINT,
SocialSecurityNumber NVARCHAR(20)
)
INSERT INTO Employee(FirstName, LastName, Email, BankAccountNumber, SocialSecurityNumber)
VALUES
(‘John’, ‘Doe’,’john.doe@domain.com’, 3219876512, ‘123-456-789’),
(‘Jane’, ‘Doe’,’jane.doe@domain.com’, 9876543210, ‘321-654-987’),
(‘Lewis’, ‘Doe’,’lewis.doe@domain.com’, 3754796548, ‘789-123-456’)
SELECT * FROM Employee
This is what we were used to. Now let’s implement the data masking:
CREATE TABLE Employee(
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(100) MASKED WITH (FUNCTION = ‘partial(1, “……”, 1)’),
LastName NVARCHAR(100),
Email NVARCHAR(100) MASKED WITH (FUNCTION = ’email()’),
BankAccountNumber BIGINT MASKED WITH (FUNCTION = ‘random(1000000000,9999999999)’),
SocialSecurityNumber NVARCHAR(20) MASKED WITH (FUNCTION = ‘default()’)
)
INSERT INTO Employee(FirstName, LastName, Email, BankAccountNumber, SocialSecurityNumber)
VALUES
(‘John’, ‘Doe’,’john.doe@domain.com’, 3219876512, ‘123-456-789’),
(‘Jane’, ‘Doe’,’jane.doe@domain.com’, 9876543210, ‘321-654-987’),
(‘Lewis’, ‘Doe’,’lewis.doe@domain.com’, 3754796548, ‘789-123-456’)
SELECT * FROM Employee
As you noticed the result of our query still returns all the data. Since you created the table, you are the owner and you can see everything. We need a user that only has SELECT permissions on that table.
CREATE USER PublicUser WITHOUT LOGIN;
GRANT SELECT ON Employee TO PublicUser;
EXECUTE AS USER = ‘PublicUser’;
SELECT * FROM Employee;
REVERT;
And now we see that the data is masked.
FirstName is replaced by the first letter of the name, the last letter of the name and in between some dots. Email is replaced by the first letter, the suffix and all the rest became X’s. BankAccountNumber has been replaced with a random number in the range we gave and the Social SecurityNumber is fully replaced by XXXX.
If you want to give a user the privilege to unmask data, then you need to grant him UNMASK permissions.
GRANT UNMASK TO PublicUser;
EXECUTE AS USER = ‘PublicUser’;
SELECT * FROM Employee;
REVERT;
Revoking can be done by this:
REVOKE UNMASK TO PublicUser;
Microsoft has foreseen a new system table (sys.masked_columns) to get data about the masked columns.
SELECT t.name, mc.name, mc.masking_function
FROM sys.tables t
JOIN sys.masked_columns mc
ON t.object_id = mc.object_id
WHERE t.name = ‘Employee’
Don’t forget that this does not change anything to how to update or insert data in those columns. You just enter the correct data; SQL will mask it. It’s also good to know that if you do a SELECT INTO or INSERT INTO, the data will be copied masked if you don’t have the UNMASK permissions.
I hope you found this short blogpost interesting. Next time I will write about another new feature namely ‘Row-Level-Security’.
Cookie | Duration | Description |
---|---|---|
ARRAffinity | session | ARRAffinity cookie is set by Azure app service, and allows the service to choose the right instance established by a user to deliver subsequent requests made by that user. |
ARRAffinitySameSite | session | This cookie is set by Windows Azure cloud, and is used for load balancing to make sure the visitor page requests are routed to the same server in any browsing session. |
cookielawinfo-checkbox-advertisement | 1 year | Set by the GDPR Cookie Consent plugin, this cookie records the user consent for the cookies in the "Advertisement" category. |
cookielawinfo-checkbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checkbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
CookieLawInfoConsent | 1 year | CookieYes sets this cookie to record the default button state of the corresponding category and the status of CCPA. It works only in coordination with the primary cookie. |
elementor | never | The website's WordPress theme uses this cookie. It allows the website owner to implement or change the website's content in real-time. |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Cookie | Duration | Description |
---|---|---|
__cf_bm | 30 minutes | Cloudflare set the cookie to support Cloudflare Bot Management. |
pll_language | 1 year | Polylang sets this cookie to remember the language the user selects when returning to the website and get the language information when unavailable in another way. |
Cookie | Duration | Description |
---|---|---|
_ga | 1 year 1 month 4 days | Google Analytics sets this cookie to calculate visitor, session and campaign data and track site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognise unique visitors. |
_ga_* | 1 year 1 month 4 days | Google Analytics sets this cookie to store and count page views. |
_gat_gtag_UA_* | 1 minute | Google Analytics sets this cookie to store a unique user ID. |
_gid | 1 day | Google Analytics sets this cookie to store information on how visitors use a website while also creating an analytics report of the website's performance. Some of the collected data includes the number of visitors, their source, and the pages they visit anonymously. |
ai_session | 30 minutes | This is a unique anonymous session identifier cookie set by Microsoft Application Insights software to gather statistical usage and telemetry data for apps built on the Azure cloud platform. |
CONSENT | 2 years | YouTube sets this cookie via embedded YouTube videos and registers anonymous statistical data. |
vuid | 1 year 1 month 4 days | Vimeo installs this cookie to collect tracking information by setting a unique ID to embed videos on the website. |
Cookie | Duration | Description |
---|---|---|
ai_user | 1 year | Microsoft Azure sets this cookie as a unique user identifier cookie, enabling counting of the number of users accessing the application over time. |
VISITOR_INFO1_LIVE | 5 months 27 days | YouTube sets this cookie to measure bandwidth, determining whether the user gets the new or old player interface. |
YSC | session | Youtube sets this cookie to track the views of embedded videos on Youtube pages. |
yt-remote-connected-devices | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt-remote-device-id | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt.innertube::nextId | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
yt.innertube::requests | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
Cookie | Duration | Description |
---|---|---|
WFESessionId | session | No description available. |