Following on from previous posts where we looked at Dynamic Data Masking, how to use it, and the challenges we may encounter, there’s one last area I wanted to touch on. With the arrival of SQL Server 2022 the masking functionality has been extended so we have an additional masking function and more granular permissions available to us.
In this example we’ll be using SQL Server 2022 CTP 2 as its the latest we have available. Let’s start out by creating a couple of tables, adding some data and then creating some users to show these new features:
/* Create tables */
CREATE TABLE dbo.Customers (
CustomerID INT IDENTITY(1, 1),
FirstName VARCHAR(50) MASKED WITH (FUNCTION = 'default()'),
Age INT MASKED WITH (FUNCTION = 'random(20, 80)'),
LastLogin DATETIME
);
CREATE TABLE dbo.Employees (
EmployeeID INT IDENTITY(1, 1),
FirstName VARCHAR(50) MASKED WITH (FUNCTION = 'default()'),
Age INT MASKED WITH (FUNCTION = 'random(20, 80)')
);
/* Add some data */
INSERT INTO dbo.Customers (
FirstName, Age, LastLogin
)
VALUES ('Dave', 42, '2022-02-15 15:27:10'),
('Janet', 25, '1999-12-18 11:04:52');
INSERT INTO dbo.Employees (
FirstName, Age
)
VALUES ('Andy', 35),
('Jackie', 60);
/* Create the users */
CREATE USER Overlord WITHOUT LOGIN;
CREATE USER HRTeam WITHOUT LOGIN;
CREATE USER MITeam WITHOUT LOGIN;
/* Provide read access */
ALTER ROLE db_datareader ADD MEMBER Overlord;
ALTER ROLE db_datareader ADD MEMBER HRTeam;
ALTER ROLE db_datareader ADD MEMBER MITeam;
A wild masking function appears
With the latest version we have a new masking function to help obfuscate the various date and time data types including DATETIME, DATETIME2, DATE and TIME. With this function we can choose which elements of the date or time we want to mask – so for example we could hide the Year element from a date of birth.
Let’s see how we can get that applied to the LastLogin date for our customers:
ALTER TABLE dbo.Customers
ALTER COLUMN LastLogin
ADD MASKED WITH (
FUNCTION = 'datetime("YMDhms")');
In this example we’re hiding all of the elements of the date and time which are governed by the letters we use within the function:
Ywill default the year to 2000Mwill default the month to JanuaryDwill default the day to 1sthwill default the hour to nilmwill default the minute to nilswill default the seconds to nil
You can use as many or few of these which you required, we’ve just thrown the kitchen sink at it in this example.
With that in place we can see the time stamps in this table now all completely hidden, lovely:
Unmasking a whole schema
When it comes to the more granular permissions this means that we can now grant the unmask privilege not only at the database level but also schema, table and even column level.
As we’re only using dbo in this instance let’s go and give the Overlord access to that:
GRANT UMASK ON SCHEMA::dbo TO Overlord;
With that available we can go and see how the data would look if we were the overlord (if only!):
EXECUTE AS USER='Overlord';
SELECT * FROM Customers;
SELECT * FROM Employees;
REVERT
As expected our Overlord can see all of the details in both of our tables. It’t not really any different to granting the permission at the database level so let’s go a little more granular.
Unmasking a single table
For our HR team we want to allow them to see all of our employee details so this time we’ll allow the user to unmask that one table:
GRANT UNMASK ON dbo.Employees TO HRTeam;
Once again we’ll now check those tables as this user to see what the results look like:
EXECUTE AS USER = 'HRTeam';
SELECT * FROM Customers;
SELECT * FROM Employees;
REVERT
We’ll now be able to see the employee details without having any visibility of the customer information. Note that the customer age is randomised so they aren’t the real ages we’re seeing.
Unmasking a particular column
Finally we’re able to unmask just an individual column to a user so let’s allow the MI team to do some analytics based on the ages of our customers and employees. We’ll unmask just those columns in the two tables as follows:
GRANT UNMASK ON dbo.Customers (Age) TO MITeam;
GRANT UNMASK ON dbo.Employees (Age) TO MITeam;
With that in place lets take a look at how the data presents with only the one column unmasked for us:
EXECUTE AS USER = 'MITeam';
SELECT * FROM Customers;
SELECT * FROM Employees;
REVERT
Wrap up
As we’ve seen above the incremental improvements in SQL Server 2022 can help with utilisation of the feature in more specific use cases when more flexibility is required.
The new masking function can allow partial visibility of date and time fields and the additional granularity provided with the permissions can enable newer more bespoke usage.
I hope we’ve seen over the last few posts that the Dynamic Data Masking feature is a worthwhile component of a strong security model and the new features in SQL 2022 enhance the feature set and ability for adoption.
As with the majority of our features in SQL Server you can find further details around these features within the official documentation.
Andy Brownsword 