Next up in our series on access controls in the Snowflake Data Cloud, we’re going to explore how to view privileges granted on an object in Snowflake. If you’re new to this series or missed our last post on how to grant privileges to a role in Snowflake, be sure to check it out.
Getting Started
To view privileges granted on an object in Snowflake, use the SHOW GRANTS statement:
SHOW GRANTS ON <object type> <object name>;
The SHOW GRANTS statement can be used on any Snowflake securable-object (<object type>) like databases, warehouses, and schemas.
The following examples will use an example object (the ‘rocketship’ database):
USE ROLE sysadmin;
CREATE DATABASE rocketship;
After running the SHOW GRANTS statement on the database, we can examine the output, and see the privileges granted on the object:
SHOW GRANTS ON DATABASE rocketship;
No explicit grants have been run on the database yet, but there’s still a result showing the OWNERSHIP privilege on the role that created the database (‘grantee_name’). In this case it is the SYSADMIN that was implicitly granted the privilege on creation:
Row | 1 |
created_on | 2021-02-17 14:04:20.427 -0800 |
privilege | OWNERSHIP |
granted_on | DATABASE |
name | ROCKETSHIP |
granted_to | ROLE |
grantee_name | SYSADMIN |
grant_option | true |
granted_by | SYSADMIN |
The columns in the result are as follows:
- 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 diagram shows the ownership privilege visually, SYSADMIN has OWNERSHIP on the database rocketship:
Image created by Traverse
To show this also works for other types of grants, we can create a role administrator and grant it all privileges on the database:
USE ROLE securityadmin;
CREATE ROLE administrator;
GRANT ALL ON DATABASE rocketship TO ROLE administrator;
Now when the SHOW GRANTS statement again:
SHOW GRANTS ON DATABASE rocketship;
The new privileges can be seen:
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 | OWNERSHIP |
granted_on | DATABASE | DATABASE | DATABASE | DATABASE | DATABASE | DATABASE |
name | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP | ROCKETSHIP |
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 that, as we mentioned above, ALL isn’t a ‘real’ privilege, it was expanded out by Snowflake to all of the individual privileges available to the DATABASE type object.
Image created by Traverse
In this post we showed how to find out which privileges have been granted to Snowflake securable objects. Check back for the next blog in the series where we describe how to view privileges granted to roles.
