>> In this video, we're going to take a look
at a real-world type of scenario,
which will help you apply and
combine all the things that you've learned in this module.
So what we're looking at here is the Azure portal,
specifically, my Azure SQL Database I've been using.
If I go down to advanced data security,
we can see I've already accepted some of
the recommended columns to classify.
However, I go into here and
I start to take a look at the possible classifications.
It looks like we have first name and last name
classified as sensitive data or confidential for GDPR,
but we don't have anything listed for middle name.
Let's imagine a scenario where we decide that middle name
is also confidential related to GDPR.
So I can go ahead and create a new classification,
and I'm just going to find that customer and middle name.
I'm going to give it the same label as the other names,
so we're going to call it confidential GDPR.
I can simply just add that classification,
make sure to save and very quickly,
I can see that it will come up in my classified columns here.
Now, the next thing I might do is say, well,
we realized this new column that contains sensitive information.
We might want to go ahead and mask that column.
You can see I've already added mask for first name and last name,
so now I can simply just go ahead and select "Add
Mask" and select that middle name column again.
I'm just going to use the default mask.
I'm going to go ahead and add and then save that.
Now, let's enter the SSMS
and suppose that some people start querying this information.
For example, right now, I'm connected using Cloud Admin,
so I can go ahead and just run this,
and I should have no problem because I am the master of all here.
So I have access to all of this information.
But now let's say we create a new user.
Conveniently, his name is Bob and he loves the Titans,
so there's his password.
But what we're going to do is we're going to go
ahead and create him.
Then we're going to execute a command as Bob.
So you saw in an earlier video,
we're actually logged in as Bob loves Tennessee, but in this case,
I can just leverage a command you're probably all familiar with,
that is execute as user,
and I'll execute as him until I use the revert statement.
So I'm going to go ahead and run the same thing.
Even though he has read and write access,
he hasn't been granted
unmasked privileges to this data of first name,
middle name and last name,
so you can get a glimpse of what his view is.
Now, let's say that for whatever reason,
Bob becomes some data officer and he needs to have
access to actually see the information that's under here.
Now, what I can do is as the admin,
I can grant unmask to Bob.
Then if I run basically the same thing,
executing as him selecting that data,
I'm going to be able to now see the first name,
the middle name, and the last name altogether.
Let's say for whatever reason, our final scenario,
that we want to revoke this privilege to Bob.
Maybe Bob leaves the company
or Bob does something bad and we no longer want him to access.
Whatever the reason is,
we can take away that access using revoke unmask.
Now we can run the same set of commands again and sure enough,
he is back to being masked on the sensitive data columns.
This might all be happening externally and you might be wondering,
who is trying to access this data?
Maybe you've granted or revoked access to
Bob and you want to see if he's still trying to access this data.
So one thing we can do is leverage the data that
we have been collecting with SQL Audit.
So what I'm going to do here is leverage
the storage account that we set up for this,
so what I can do is I can go to File,
Open, Merge Audit Files.
I'm going to go to Merge Audit Files and select "Add."
I'm going to add these from
Azure Blob storage because that's where they're stored.
So first, I'm going to have to connect to my Azure account.
I'm going to go ahead and select the storage account and then
I'm going to select the SQL DB Audit Logs.
I can select "Okay," and then I can even specify the server,
which we're going to use this one,
and the database is that one,
and then the time that I want to pull in those files.
So I'm just going to pull in everything.
I deployed this database earlier today
and it's going to tell me the number of
files that are included in that.
I'm going to go ahead and take them all.
You can see these are.XEL files and I'm going to hit "Okay."
Now, it's going to download and open
all these files and I increase the font size,
so it seems like a mess.
But what I can do is now see
various information about the client
IP the people are connecting from,
the statement that people are using,
and other information about the queries.
Now, I'm just going to sort these based on
timestamp and go the other way.
What we're seeing here in
the statements is some of these comments that
tell me we were revoking privilege or giving access to privilege.
Now, if I go ahead and dive into one of these statements,
so let's do this one.
This was execute as Bob.
This is when Bob did not have access
and he's trying to access
those sensitive columns that we've masked.
Now, if I go into that,
there's actually data sensitivity information field.
So I can double-click in here maybe we can zoom in on this,
see that the sensitivity label was confidential GDPR,
and the information type was name,
and the rank was medium.
So this is just one way how I might audit
who's accessing our sensitive information.
Now, another method I could take,
since we also enabled Log Analytics
for auditing for our Azure SQL Database Server,
is I can switch over to the portal and go into
view Audit Logs and select the "Dashboard."
Here I get a dashboard that's going
to show me the access to sensitive data,
so I'm going to be able to find out things
like how many queries are accessing this sensitive data,
what types of sensitivities are being accessed,
and what principals and IPs.
What you can see, there are
four queries that are there and any of these items,
I can click into to learn more information about.
So if I just scroll to the right and I look
at principal access to classified data,
what I'm seeing is there's four from Cloud Admin.
Now, the reason Bob isn't showing up
here is we didn't actually log in as Bob,
we did an execute as Bob.
But it's a good example to show how I can dive in, and here,
now I'm seeing the logs behind that dashboard,
and I can actually dive in and see the queries that were run here.
So we can see that, yes,
Bob was accessing this sensitive data,
but because we executed as user,
it's showing up as myself accessing that data.
Now, another dashboard that's
available is the Azure SQL Security Insights.
So I just thought I'd spend
a minute showing you what that looks like.
If I select that,
I'm going to see a lot of different things.
I'm going to see the audit distribution with some information,
distribution by database, so which database is being accessed,
as well as the IP address.
So you can see that private IP address
that we've been using since we set up Private Endpoint,
as well as the public IP address,
and as well as
the internal Azure that is accessing some of the telemetry.
I can also see the SQL principals,
who's accessing and actually logging in.
So we do see Bob loves Tennessee from
an earlier exercise has showed up here.
This is another way that you can analyze and
monitor the things that are happening in your database.
In this demo, you saw how to combine
several different tactics like
advanced data security, including data classification.
We took a look at auditing from a storage account,
and we took a look at auditing with Log Analytics,
and some of the dashboards that are included there.
We hope you'll go through these exercises and see for yourself
some of the capabilities in Azure SQL. See you next time.