Role-based Access Control (RBAC) - Part II
1. Role-based Access Control (RBAC) - Part II
Let’s pick up where we left off. In the last part, we talked about how privileges – the ability to do something – can be assigned to roles, and those roles can be assigned to users. An important thing to note is you can also assign roles to other roles, so you can have a hierarchy where the top roles inherit the privileges of roles lower down, but we’re not going to show examples of that here. Now I want to talk about Snowflake’s five other automatically generated roles, in addition to accountadmin. These are orgadmin, securityadmin, useradmin, sysadmin, and public. To save time, I’m not going to cover orgadmin here – it’s a role that can span multiple accounts so it’s unlikely you’ll run into it in the wild. The easiest way to see what each of these roles does is to run the SHOW GRANTS TO ROLE command. Let’s start with SECURITYADMIN: SHOW GRANTS TO ROLE securityadmin; You can see that this only has a handful of privileges, but these are powerful – They let the securityadmin set account-level security policies, like a password policy. SHOW GRANTS TO ROLE useradmin; Useradmin’s privileges are even more simple, but also powerful – The useradmin can create roles, and create users. SHOW GRANTS TO ROLE sysadmin; The Sysadmin is primarily able to create databases and warehouses. SHOW GRANTS TO ROLE public; And the public role is primarily able to run queries. You can always look it up. The last thing I’ll mention is that, the accountadmin role has access to the securityadmin role, and the securityadmin role has access to the useradmin role. The accountadmin role also has access to the sysadmin role. And *all* of these roles have access to the public role – so they can all run queries. That’s a lot of info, and you don’t need to internalize these differences between the different automatically generated roles. I just wanted to emphasize that they do have different privileges, and the roles are hierarchical, so if you ever find that you have insufficient privileges to do what you want to do, you can get it done by moving higher up the hierarchy of roles. The one term we still haven’t explicitly covered that we said we would is “securable object,” so I’ll just note that these are the entities those with the right privileges can manipulate in some way – So a warehouse is a securable object, and we gave tasty_de the ability to create this object. We could have granted tasty_de the ability to use a database, or we could have granted tasty_de ownership of a schema – and thus all powers related to that schema, including dropping that schema – etc. Okay, that’s it for RBAC! And honestly, that was more fun than I expected. It’s actually kind of satisfying to run those SHOW GRANTS TO ROLE commands and see exactly what a role can do and what it can’t. We’ve covered a lot of ground on this topic. One, we learned about securable objects, roles, privileges, and users. Two, we learned about the system-defined roles – orgadmin, accountadmin, securityadmin, useradmin, sysadmin, public. Three, we learned how to assume a role. Four, we learned how to create a new role. Five, we learned how to grant privileges to a role. Six, we learned how to show what privileges a role has been granted. And seven, we learned how to grant a role to a user. That’s a lot of stuff! We’re not quite RBAC black belts, but we’re probably at least RBAC blue belts or something. Great job.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.