>> Azure SQL Dynamic Data Masking empowers
you to limit sensitive data exposure by masking it
to non-privileged users in
your Azure SQL Databases and Azure Synapse Analytics.
Tune in to learn more on this episode of Data Exposed.
[MUSIC]
Hi and welcome to this episode of
Data Exposed. I'm Anna Hoffman.
Today, I'm joined by David Trigano,
a Program Manager at Microsoft,
and David Brookler, a Principal Software Engineer at Microsoft.
Let's start with you, David T. Can you tell us
a little bit about what you and your team does?
>> Thanks, Anna. Yes, my name is David Trigano.
I'm a Program Manager on Microsoft walking on
Azure Security Center team focusing on
securing SQL Server for customers that are using
our service on Azure and on-premises.
>> Awesome, very cool. It seems like you're focuses security,
but you're also focusing on SQL these days.
Thanks for being here. David B,
do you do something similar?
Can you tell us a little bit more?
>> Yeah. I'm a Principal Software Developer and I work
on database security including
features such as Dynamic Data Masking,
which we're going to be demoing.
>> Awesome. Maybe let's go ahead and start there.
What are you guys going to share with us today?
>> David and I are going to give you a closer look at
a new capability our team has been working on,
which allow you to manage data access with
more granularity and better control access to insensitive data.
Dynamic Data Masking is a core component for
Azure SQL Database and Azure Synapse Analytics that
allows you to limit
sensitive data exposure by masking it to non-privileged users.
As a policy-based security feature,
Dynamic Data Masking also allows customers to
prevent unauthorized access to sensitive data by
enabling them to designate how much of the sensitive data to
reveal without compromising their performance and compliance.
It's very simple and easy to enable.
Let me show you how it works.
Here I am on the Azure portal looking on
my SQL database called ddm-demo.
This is an Azure SQL Database and I'm actually looking at
Dynamic Data Masking feature that allow me to
mask columns basically with functions.
I have multiple functions here as you
can see: e-mail, social security number.
I also have a default value to mask
my columns and I can also have custom string.
I also have recommended fields that I can mask automatically.
Here I have first name and last name,
and I can basically mask them, save.
As you can see within few seconds,
Dynamic Data Masking automatically mask my columns.
I can also, here,
SQL users, let them see the columns that I previously masked.
Let's add test user.
As you can see, now test user has
access and can see these columns.
I can also decide to manually add a masking rule.
Let's take, for example, this table compensation.
I have here a column called bonus and I can decide
to give the default value
of a random number range as a masking field format.
I just have to add this mask and save it.
Now that we saw how we can very
easily enable Dynamic Data Masking via the Azure portal,
David will show you how you can continue your journey with
Dynamic Data Masking in SSMS via T-SQL commands.
Now that I show you how you can configure
Dynamic Data Masking via the Azure portal,
David Brookler will show you how you can do the
same with SSMS by using T-SQL commands.
>> Hey, thanks, David.
Now, if we look at my screen over here,
we'll see how the masking
actually is applied to the query results.
Now, I'd like to show you the query results for
the test user which David had setup
is being a user that had the UNMASK permission granted to it.
So if we execute that,
we'll see that we're able to see all of
the different values in the tables.
However, if we revoke that permission from
the user and then re-execute those queries again,
now we see that we get masking of the e-mail,
the social security number,
the salary and bonuses are masked down to zero,
and we get x out for the routing number and the account number.
Now, I'd like to demo a new Dynamic Data Masking capability,
the granular use of the UNMASK permission.
This capability is not exposed to the portal,
so you need to use T-SQL commands
directly through a tool such as SSMS.
To demonstrate granular UNMASK,
let's create four different users,
each able to see different parts of the database.
We're going to start out with an HR employee,
who we'd like to be able to see the things
within the human resources schema.
Let's create that.
Next, we're going to have an HR manager.
They need to be able to see their human resources schema web,
but they also should be able to see the compensation table.
What we've done here is we've created a human resource manager.
We granted the UNMASK on
the schema and also on the particular table.
Let's execute that.
Now, we want to create an HR intern.
The intern should have access also to the human resource schema,
but we don't want them to be able to
see the social security number of the employee,
so we'll deny the UNMASK on that particular column.
Sorry. For last, we'll create
a payroll manager who's not able
to see the human resources schema,
but instead will be able to see the finance schema.
Let's do that.
Then we'll see now what happens for the different employees.
We'll start with the HR employee.
So if we execute these queries,
now we see that they're able to see the human resource table.
But the salary and bonus, and the routing,
the banking information,
this information is no longer available to them.
If we instead execute as HR manager, same queries.
But now, HR manager is able to still see the human resources.
Now, they're also able to see the compensation,
but they still are not able to see the banking information.
Our HR intern executing is that.
The banking information is still not available,
the salary compensation is not available.
They can see the human resources table,
but the social security number is now masked out.
Last but not least,
if we get to the payroll manager and execute for them,
we see that they're not able to see
the information from the human resources schema,
but they are able to see
the salary information and the banking information,
just what we wanted. Great, thank you.
>> Thanks, David. As you saw, guys,
we recently added similar permission to
Dynamic Data Masking at the schema level,
the table level, and the column level,
which makes this feature so much powerful.
>> Awesome. Thanks both David T and David B. I
had seen this idea of denying and allowing at the database level,
but this new capabilities around schema, and table,
and column level is really going to allow you to
specify the exact granularity of
permissions that a user should
have and really nothing more than that.
It's a really cool stuff. Thanks for sharing it today.
If you enjoyed this video and are interested in learning
more about Dynamic Data Masking for Azure SQL Database,
we will include a link in our description,
but it's aka.ms/azuresqlddm.
We hope you'll go check it out.
If you liked this video,
please like this video, leave us a comment.
Let us know what you'd like to see more of and
stay tuned for the next episode of Data Exposed.
[MUSIC]