Column Level Security in Fabric Data Warehouse

Hello everyone. In the previous video I talked about how we can implement

dynamic data masking to protect sensitive data from unauthorized access. So in this

new video we're going to take it a step forward by talking about the column level

security. So let's get started. Now the column level security is an important

security features in fabric warehouse. and of course, SQL server and it allows us the admin to restrict access to specific columns

in the table and of course it is super useful to 1942 to see that confidential data within the

database table. So I'm going to work you through on how to implement that from the scratch

Enough of talking, let's get started. So we're going to use the same data masking workspace

we used in the previous video and of course we can see the warehouse

switch to the data warehouse SQL endpoint and of course we can see the top three rows from the

M record table to implement the column level security so that Enoch the other user can be able to

select the M. ID, phone name and annual salary columns from its own dashboard. We're going to use the

grant data control statements. So I'm going to type in grants. Now we want to grant select and

of course we need to specify the name of the column so inside square bracket I going to type in N and the close the square bracket and of course want to allow him to select from the full name close that and finally the

annual salary so this is going to be the three columns and close the bracket so click enter

now we need to specify the name of the scheme and of course the name of the table so we have to use

the on keywords on db0.m records and of course the grantee would be to inside double

code ano at excel jet consult triple one dot on microsoft dot com so let's check it out so we can see the grant

select on the m id full name annual salary on db0.m.m.m. records to

the grantee so you can go ahead and execute or run the query and there we go you can see commands

completed successfully after you can see it took just one second three 50 milliseconds so having done

i'm going to go back to the workspace and very importantly i'm going to click on this elipsis

and i will click on share on check this and specify the name

of the user which is A knock from my organization and of course I not going to notify by email so I take that and go ahead and grant so that done now I going to go back to the Chrome and of course

you can see the user is logged in so I can refresh the page and of course you can see the

name of the works the warehouse rather so Hrd underscore info and if you can see the name of the table

mp records now in this case there is absolutely nothing visible so

let's open the new query and then let's try to query all from the m records table so when i

try to click on run see what i'm going to get there we go it's working fine started running query

at line so the select permission was denied on the column gender of the object the amp records in the

database hrd underscore info and of course the schema dbo so we can see we can't actually select some of

the other columns of course we can select the m id the full name and the ancillary so we can see that

the column level security is working pretty fine let's be more specific and choose the three columns

that we have access to so i'm going to get rid of this star and inside square brackets m id

okay close the square brackets and put in the comment so the next one is the full name so

open and close the square bracket and full name and the last

one is the annual salary so annual salary now what I go ahead and run this query i going to see the records because of course we have the permissions to access the three records and there we go so we can see

the m id the full name and annual salary is working but other ones are not working let's go back to the

admin let's say i want to add the pension so i can actually modify or let me just copy this

control c let me control v and let's want to add the pension um field or the column so i'm going to delete

and let's add pension okay so let's go ahead and execute this now before i execute let me come back

to the user i want to include pension i'm going to put in the command and type in pension

and then go ahead and execute so click on wrong so you can see the select permission

was denied on the column pension of the object and records. So let's go to the admin and execute these

So we can see the command run successfully. And when I go back and query this again, of course, that's going to come out because we just granted an access to the pension column

And of course, there we go. So we can see we have the pension. So the column level security is working pretty fine

So I trust you on. this video if you do like share with your friends and comment thank you and bye for now cheers