So for the demo I'm using those very
simple contoso clinic application
and this is a simple application
that helps nurses to log in and see
the patients at their assigned to as
well as patient visits an right now
if you see I am logged in as Rachel
who is the nurse and Rachel is able
to see all of the patient data.
So there's definitely some
security mechanisms we can take
to restrict Rachel's access,
and let's see how we can restrict the
access as well as well as restricted
access to some of the sensitive data
such as SSL on this application.
So let me go back to the database and
this is the database server I'm using.
When I click on the database
on the Azure portal,
I can Scroll down to the different
security capabilities and let's
first look at dynamic data masking
to see how we can mask some of the
sensitive data so Rachel isn't able
to see her patient sensitive data.
So the first thing that you see on this
application that is sensitive is the SSN.
So in order to mask the
assistant field completely,
all you would need to do is come to the
dynamic data masking tab and without
even having to do anything you will
see that it shows you recommended
fields to mask based on the sensitivity
of the different columns in your database.
So we see the SSN column here an all
you need to do to mask it is to add a.
Rule to mask this particular column and
the masking function is default value.
You can choose to customize it,
but let's keep it as the default
mask for now.
Let's save it.
And once it's done,
let's go back and refresh our
application and you can see that the
SSN field is now completely matched.
Let's try another way of partially
masking one of these fields.
So let's say you have an email,
or in this case,
let's take the last name you
want to partially mask the last
name by only showing the very
first letter of the last name,
but masking the rest of the
characters in the last name.
The for this let's try a different method.
Let's go to SQL Server Management
studio and we have a simple command
here for altering the last name column
an adding a mask which will basically.
Expose just the first letter,
but mask the rest of the letters
in the last name.
So let's run this command on the database.
And now let's go back and refresh the field.
And you will see that the last name
feels just show you the first letter
and the rest of it is marked out.
You could also implement dynamic
data masking through other
mechanisms like partial.
So this is a simple demo showing how
easily you can use dynamic data masking.
Without having to do any application side
changes to restrict access to sensitive data.
Let's now solve the next problem that
Rachel is able to see all of the patients,
but we just want her to want her access to be
restricted to the patients she's assigned to.
So in this case,
let's see, oh,
the role level security to restrict
the number of rows that Rachel
can see in the query results.
So for this,
let's again go back to SQL Server
Management Studio and I have a simple.
Table valued inline function here,
which will basically verify
that when Rachel is logging in,
what is her user ID based on the
user context and making sure that.
Should that the logic in the mapping
table is checked and she can only
access the patients that she's mad too?
So let's first go ahead and
create this function.
After creating the function,
we have to create a security policy
that encapsulates this particular
function and we have to specify the
different tables on which we want
to activate the security policy on.
So let's go ahead and create a security
policy and this is on by default so you
don't don't need to change anything.
Let's now go back to and see if
the restriction logic worked.
So yes, that worked,
and when I refresh the page,
Rachel can now only see the patients
who are assigned to her and we have
managed to using low level security
to block out the other roads that
Rachel should not have access to.
You could also go ahead and.
The under policy off if you want
to return to the original state.
And when you refresh,
it restores the original state.
So what we saw here was how easily
you can create a table valued
inline function and then a policy
to enforce that function for.
Hiding the number of rows from
query results based on the user
context or the execution context.