Role-based Access Control (RBAC) - Part I

1. Role-based Access Control (RBAC) - Part I

All right, now we’re going to learn a bit about role-based access control (RBAC) in Snowflake, which is a system that lets you control access to particular Snowflake objects by granting privileges to roles and then assigning those roles to users. I’ll just come out and say it: Role-based access control isn’t my all-time favorite topic. I have a healthy appreciation for many things that others would consider dry or boring – I love technical documentation, I love math, I love poetry – but RBAC pushes even my limits. That said, it’s very important to cover, because: One, there are few things more frustrating than not being able to take some action because you don’t have the right privileges Two, good data governance helps you prevent both bad actors from intentionally messing with your data, and good-but-fallible-actors from inadvertently messing with your data. The happy news is that role-based access is something that Snowflake handles very elegantly. It’s pretty painless in Snowflake, and if you’re a super organized person, you might even find it enjoyable, in like a “look how organized my bookshelf is” kind of way. Okay, so there are four main concepts we need to understand in order to understand RBAC – securable objects, privileges, roles, and users. I think the best way to learn about these is to see them in practice, and as we’re using them, we can define them and talk about how they relate to each other. So let’s start by using the role accountadmin, which is the role we’ve used for everything in the course so far – a sloppy, even risky, practice, but one we’ve been doing for simplicity. Okay, so we assume a role with the USE ROLE command, followed by the name of the role. Let’s take on the accountadmin role. USE ROLE accountadmin; Accountadmin is one of the six main roles that automatically exist in every account (we’ll talk about the others in a moment) so we never actually had to create it. We could also have assumed the role by going to the top right of the Snowsight UI, clicking on the current role name, and selecting a new role name. Now that we’ve assumed the Accountadmin role, we can make any other role and give that new role any of the powers Accountadmin has. So let’s make a new tasty_de role. We do that by running the CREATE ROLE command, followed by the name of the role we want to create. CREATE ROLE tasty_de; Awesome. Now we have a new role. Roles are useful because you can grant them privileges – so the authority to do stuff – and then you can assign roles to users. In this way, you can give individual users access to those privileges. I like to think of roles as buckets of privileges – Having roles as an intermediate step between privileges and users is cleaner than just granting privileges directly to users, because typically users fall into user types, and it’s more maintainable to have a set number of user types you maintain than it is to maintain individual privileges for each user, one by one. Imagine if every new data engineer at your company had to be granted *all* of the necessary privileges one by one. Much easier to maintain a data engineer role, and give that new user access to that role. We can see what privileges have been granted to our tasty_de role by running the SHOW GRANTS TO command, followed by the role name: SHOW GRANTS TO tasty_de; We don’t get any results, which makes sense – We just created this role, and haven’t granted it any privileges. It’s powerless. Like a bunny stuck in a windowwell. We get a *very* different result if we run the SHOW GRANTS TO command followed by the accountadmin role: SHOW GRANTS TO ROLE accountadmin; Yikes! Who knew there were so many privileges one could grant! And accountadmin has all of them! I’m seeing hundreds and hundreds of rows. Privileges like ownership of some databases, usage of some schemas, “select” on many tables and views. Let’s grant the role tasty_de to my username – If I scroll to the bottom, I can click on the icon on the bottom left, and hover over the “account” tab, and see that my user name is [peterintrotosnowflake]. So then I just have to run the command: GRANT ROLE tasty_de TO USER [peterintrotosnowflake]; And then I can assume this role by running: USE ROLE tasty_de; Remember, we still haven’t given any privileges to tasty_de – it’s like an empty shell – so if I try to do something like create a new warehouse, that should fail: CREATE WAREHOUSE tasty_de_test; And sure enough, it fails with a “SQL Access Control” error: “Insufficient privileges to operate on account” So now let’s finally give tasty_de a privilege! If we go back to using the accountadmin role, we can grant the “create warehouse” privilege to tasty_de: GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE tasty_de; After we run that, if we run the SHOW GRANTS TO ROLE command again, and specify that we want to see the grants for tasty_de: SHOW GRANTS TO ROLE tasty_de; We can see that tasty_de now has the “CREATE WAREHOUSE” privilege! So let’s hop back over to the tasty_de role, and try creating that warehouse again: USE ROLE tasty_de; CREATE WAREHOUSE tasty_de_test; Cool, that worked! We made the “tasty_de_test” warehouse. Coming up, we’ll talk about securable objects, and learn about the five other roles that, like Accountadmin, Snowflake automatically generates.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.