hello and welcome back to another video on snowflake in this video we are going to focus
on the topic of access control privileges and and kind of digging into an example of how security
works specifically as it entails roles and objects in terms of which roles have access to certain
objects how hierarchies work stuff like that roles so at a high level as it says here roles
are entities to which privileges on objects can be granted and revoked they're assigned to users
to allow them to perform actions a user can be assigned to multiple roles
this is important here roles can also be granted to other roles
creating a hierarchy of roles and this is actually what they recommend and what you'll
see oftentimes how this ends up working out so as an example here just in terms of a visual look
here's what the hierarchy looks like and here are where the custom roles come into play all the
permissions for a lower level role is inherited by the one above it so in this case account admin
being at the top is actually inheriting everything below it in this case here if we look at let's say
this custom roll here the second one inherit everything from this first custom roll as well
and then this third one would inherit both this and this one high level that's what's going on
so let's walk through some examples and i'll show you what that looks like in snowflake itself
and hopefully we'll get you started and you can start to work with this on your own
we are going to create a role hierarchy in this example what we'll do is we'll create three roles
we'll create an analyst role which will have the lowest amount of privileges we'll create
a developer role which will inherit this analyst role plus we'll give it another permission to read
another database that we have and then we'll also create a third one which is the ci role
that's going to include developer which in turn also includes analyst as well as we'll give it
something else so at a high level here from lowest to more privileges to more privileges
and then eventually you'd get up to account admin similar to what we see here think of
this as analyst developer ci something like that so let's get started let's just see you know what
this looks like and work through it and hopefully it will make more sense as we go about and you can
start to think of different scenarios where you might want to use this currently i'm logged in
as the account admin so i have permissions to everything i'm the very top of that hierarchy
there's nothing that i cannot see here so just to give an overview of what this looks like
here are some of the tables and schemas and whatnot in terms of warehouses we have a few
here that we'll use let's create this analyst role start to add some permissions and see what changes
first i'll make sure i'm set as the account admin so that i have permissions to create these things
create this role using the create role statement here now let's verify that this
is here and there's no grants associated to it a lot of these statements here in snowflake are
pretty straightforward which i appreciate it makes it a little easier to understand exactly
what these permissions are doing now right now i don't have visibility to this actual
to this role right now i don't see it as an option and that's because my user is not assigned to this
role so let's do let's go ahead and do that now i should be able to use this role and see now i
can switch to role analysts but if you look over here now if i refresh this you can see there's no
there's no permissions there are no warehouses to pick from this role really has nothing with it so
let's give this role permission to use the dev warehouse to grant usage on a warehouse called
dev warehouse which is one that i have to this role now if we go back to using this role we can
see that this is now an option but we still don't have any access over here to any of the databases
so let's go back to the account admin and you can see now that this more stuff is popping and
you can see as i switch more things are popping back up let's grant usage on the demo db staging
schema which we don't have if i refresh we can see that that's here we want to give it to the
analyst role so grant usage on that and we'll give select to all tables in that schema as well
so let's hop back into that role so now i've switched back to here through this statement
and i can see here i have access now to staging and i should be able to select from this table
because i've given that permission so again i'm running as analysts if i run this query
i can see the results let's compare let's hop back and forth here so we're
currently analyst if i go back to account admin and refresh there's a lot more back to analyst
and again it's trimmed down let's show the roles we can see the specific
grants now that are associated to this role and because my user is tied to this role my user has
visibility to use this role and then all of the permissions that come along with it one
other thing to note i know we briefly touched on the usage and select and these things here
all this is available to understand through the documentation you can see what different types
of privileges call for the different descriptions for them and you can see exactly what that entails
i'm not going to get into that because you know it's just that's a lot to cover in one video
so now we have one role we have the analyst role let's create a second role called developer
that will inherit all the roles of analysts but then we'll also give it a couple more privileges
as well go back as the account admin role we'll create this developer role we can see currently
there are no privileges at all it's just like the analyst role was when we created it initially it's
just it's basically just a placeholder it's empty we will assign my user to this role i
should be able to access this role and i can if i refresh now you can see this is back to very
basic placeholder default databases and schemas nothing else now in addition to giving this
to myself we mentioned that roles can also be assigned to other roles and this is how you create
that hierarchy of inheritance so what i'm going to do is use the account admin role again and grant
the role analyst to the role developer meaning i'm going to give developer all of the roles that
we just gave to analysts as well so now if i go back to using this role i should be able to see
the same things as analysts and we do we can see this here and we would have select privileges as
well now let's give some additional privileges on another database to give some additional
you know privileges to this developer role i will run all three of these
and now if we use the developer role refresh we now have access to this database and
this schema and table if we go back to analyst we don't have that anymore account admin everything
we see everything back to developer
analyst
so you can see it was pretty easy to inherit all of that and then add on top
that's the beauty behind how this works show the grants to the analyst role
and now let's show the grants to the developer role and we can see all these additional things
here another thing i'll point out here is granted by you see that this says 5 train roll we didn't
we didn't actually touch that at all here but the reason that for these
objects here this says granted by this is because that is the owner of those objects
so you can see owner here is five trend roll 5g enroll but sysadmin here for the database that's
because when i originally created these tables it was created by that role the five chain rule
that's the only reason that's there just like account admin we created the role of the analysts
via account admin so therefore they granted us as a developer that role as well so it's based
on who the owner is for this here lastly we'll create the ci roll that will have a little more
privileges as well so we'll go the same thing here create the role prove that there's nothing there
assign my user to it prove that we can log in as it and we can now let's grant developer to
ci and again this means that ci is going to inherit all of the permissions of developer
which in turn is also inheriting all the permissions of analysts so really this is getting
everything below it now if we use this ci role we can see we have currently we haven't added
anything else everything exactly as the developer role would have let's go back to account admin
and assign some additional privileges here we'll give permissions to a different database
and we'll also give insert an update we're not going to actually do that
here but just for the sake of example now let's compare the roles now if we go in a ci
we can see we have visibility now to this database my first db as well as
the demo schema if we go back to developer we lose that one and if we go to analyst
we lose the five tran and account admin everything so again going up the ladder let's start at
analyst with this developer ci now as account admin one final thing here is we'll look at
the difference in the grants altogether so this is analyst developer we have a little bit more
ci a little bit more as well you can see developer inheriting analyst so you can kind of follow the
breadcrumbs in terms of what's included in those roles what i think is nice about that
is that means you don't have to assign the same privileges multiple times because you just can
assign it to one level of the hierarchy and then as you inherit it you don't have to do it again it
just brings that into the next role the last thing i'll say is we went through this whole exercise
here using actual sql syntax in the worksheet here itself but another option here is to use a tool
that does infrastructure as code something like terraform that actually can allow you to manage
your permissions in various files so that you can source control it you know exactly what all
the permissions are at any given time and you can quickly redeploy that exact same environment with
the same exact permissions across the board by just you know a simple terraform apply in in that
case if you're interested in terraform there are some other videos on this channel that can walk
you through the basics of how to get started with that and how to use it specifically on snowflake
so that'll be it for this video i really just wanted to show you the basics of how
this works because this i think is pretty fundamental to setting up your environment
so as always thank you for watching if you found it helpful please like the video
leave a comment subscribe and i'll see you at the next one thank you