In previous posts, we’ve talked about creating roles and granting privileges to those roles in the Snowflake Data Cloud. Here we will answer the question, “how do I know what privileges my role already has?”Â
This can be answered by using the `SHOW GRANTS’ statement.
SHOW GRANTS TO ROLE <role>;
We’ll use the data from the previous post (Pt. 4), so be sure to read that first if you want to see how these objects were built.
In that post, we created a database and granted ALL privileges to the ‘administrator’ role. We then showed all the privileges, from the perspective of the database itself.
To view all privileges granted to a role, we can use the SHOW GRANTS TO ROLE statement:
SHOW GRANTS TO ROLE <role>;
Show grants to the administrator role with the statement:
SHOW GRANTS TO ROLE administrator;
The result will be:
Row | 1 | 2 | 3 | 4 | 5 |
created_on | 2021-07-23 10:09 | 2021-07-23 10:11 | 2021-07-23 10:23 | 2021-07-23 10:25 | 2021-07-23 10:35 |
privilege | CREATE SCHEMA | MODIFY | MONITOR | REFERENCE_USAGE | USAGE |
granted_on | DATABASE | DATABASE | DATABASE | DATABASE | DATABASE |
name | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP |
granted_to | ROLE | ROLE | ROLE | ROLE | ROLE |
granted_name | ADMINISTRATOR | ADMINISTRATOR | ADMINISTRATOR | ADMINISTRATOR | ADMINISTRATOR |
grant_option | false | false | false | false | false |
granted_by | SYSADMIN | SYSADMIN | SYSADMIN | SYSADMIN | SYSADMIN |
This result table has the same fields as if you had run:Â
SHOW GRANTS ON OBJECT ...
The fields in the result are:
- created_on: when this privilege was granted
- privilege: the name of the privilege that was granted
- granted_on: the type of object (database) the privilege was granted on
- granted_to: the type of object the privilege was granted to. Using this statement, this will always be ‘ROLE’. It can be ‘USER’ when we are granting roles to users.
- grantee_name: the name of the role we are granting the privilege to
- grant_option: whether the role can also re-grant this privilege to other roles
- granted_by: the role that ran the grant statement (in this case just a ‘CREATE DATABASE’ statement)
This is a simple role that doesn’t inherit from any other role. If it did inherit another role’s privileges, that role would also be shown in the results. To see that, we can create another role and grant it to the administrator role:
USE ROLE securityadmin;
CREATE ROLE engineer;
GRANT ROLE engineer TO ROLE administrator;
USE ROLE SYSADMIN;
CREATE SCHEMA rocketship.telemetry;
USE ROLE SECURITYADMIN;
GRANT MODIFY ON SCHEMA rocketship.telemetry TO ROLE engineer;
Now we can run our show role grants statement again:
SHOW GRANTS TO ROLE administrator;
Row | 1 | 2 | 3 | 4 | 5 | 6 |
created_on | 2021-07-23 10:09 | 2021-07-23 10:11 | 2021-07-23 10:23 | 2021-07-23 10:25 | 2021-07-23 10:35 | 2021-07-23 10:45 |
privilege | CREATE SCHEMA | MODIFY | MONITOR | REFERENCE_USAGE | USAGE | USAGE |
granted_on | DATABASE | DATABASE | DATABASE | DATABASE | DATABASE | ROLE |
name | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP | ENGINEER |
granted_to | ROLE | ROLE | ROLE | ROLE | ROLE | ROLE |
granted_name | ADMINISTRATOR | ADMINISTRATOR | ADMINISTRATOR | ADMINISTRATOR | ADMINISTRATOR | SYSADMIN |
grant_option | false | false | false | false | false | true |
granted_by | SYSADMIN | SYSADMIN | SYSADMIN | SYSADMIN | SYSADMIN | SYSADMIN |
Notice the new row for the role ‘engineer.’ The name field is ENGINEER and the ‘granted_on’ field is set to ‘ROLE.’
In Closing
This post showed how to query the privileges granted to a role in Snowflake, and understand the resulting output. The next post will dig deeper into role grants, and show how to view and understand a Snowflake role hierarchy, with many nested role grants.
