SHOW GRANTS¶
Lists all access control privileges that have been explicitly granted to roles, users, and shares.
For more information about privileges and roles, see Overview of Access Control.
For more information about shares, see Introduction to Secure Data Sharing.
Note
SHOW GRANTS is a special variation that uses different syntax from all the other SHOW <objects> commands.
Syntax¶
SHOW GRANTS
SHOW GRANTS ON ACCOUNT
SHOW GRANTS ON <object_type> <object_name>
SHOW GRANTS TO { ROLE <role_name> | USER <user_name> | SHARE <share_name> }
SHOW GRANTS OF ROLE <role_name>
SHOW GRANTS OF SHARE <share_name>
SHOW FUTURE GRANTS IN SCHEMA { <schema_name> }
SHOW FUTURE GRANTS IN DATABASE { <database_name> }
SHOW FUTURE GRANTS TO ROLE <role_name>
Variants¶
SHOW GRANTSSyntactically equivalent to
SHOW GRANTS TO USER current_user. Lists all the roles granted to the current user.
SHOW GRANTS ON ...
ACCOUNTLists all the account-level (i.e. global) privileges that have been granted to roles.
object_type object_nameLists all privileges that have been granted on the object.
SHOW GRANTS TO ...
ROLE role_nameLists all privileges and roles granted to the role. If the role has a grant on a temporary object, then the grant only exists in the session that the temporary object was created.
USER user_nameLists all the roles granted to the user. Note that the PUBLIC role, which is automatically available to every user, is not listed.
SHARE share_nameLists all the privileges granted to the share.
SHOW GRANTS OF...
ROLE role_nameLists all users and roles to which the role has been granted.
SHARE share_nameLists all the accounts for the share and indicates the accounts that are using the share.
SHOW FUTURE GRANTS IN ...
SCHEMA database_name.schema_nameLists all privileges on new (i.e. future) objects of a specified type in the schema granted to a role.
database_name.specifies the database in which the schema resides and is optional when querying a schema in the current database.DATABASE database_nameLists all privileges on new (i.e. future) objects of a specified type in the database granted to a role.
SHOW FUTURE GRANTS TO ROLE role_nameLists all privileges on new (i.e. future) objects of a specified type in a database or schema granted to the role.
Usage Notes¶
The GRANTED_BY column indicates the role that authorized a privilege grant to the grantee. The authorization role is known as the grantor.
When you grant privileges on an object to a role using GRANT <privileges>, the following authorization rules determine which role is listed as the grantor of the privilege:
If an active role is the object owner (i.e. has the OWNERSHIP privilege on the object), that role is the grantor.
If an active role holds the specified permission with the grant option authorized (i.e., the privilege was granted to the active role with the GRANT <privileges> … TO ROLE <role_name> WITH GRANT OPTION, where <role_name> is one of the active roles). If so, the role that holds the privilege with the grant option authorized is the grantor role. Note that if multiple active roles meet this criterion, it is non-deterministic which of the roles becomes the grantor role.
If an active role holds the global MANAGE GRANTS privilege, the grantor role is the object owner, not the role that held the MANAGE GRANTS privilege. That is, the MANAGE GRANTS privilege allows a role to impersonate the object owner for the purposes of granting privileges on that object.
If the GRANTED_BY column is empty, the privilege was granted by the Snowflake SYSTEM role. Certain internal operations are performed with this role. Grants of privileges authorized by the SYSTEM role cannot be modified by customers.
The command does not require a running warehouse to execute.
The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.
To view results for which more than 10K records exist, query the corresponding view (if one exists) in the Snowflake Information Schema.
To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.
Examples¶
List all privileges that have been granted on the sales database:
SHOW GRANTS ON DATABASE sales; +---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------+ | created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by | |---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------| | Thu, 07 Jul 2016 05:22:29 -0700 | OWNERSHIP | DATABASE | REALESTATE | ROLE | ACCOUNTADMIN | true | ACCOUNTADMIN | | Thu, 07 Jul 2016 12:14:12 -0700 | USAGE | DATABASE | REALESTATE | ROLE | PUBLIC | false | ACCOUNTADMIN | +---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------+
List all privileges granted to the analyst role:
SHOW GRANTS TO ROLE analyst; +---------------------------------+------------------+------------+------------+------------+--------------+------------+ | created_on | privilege | granted_on | name | granted_to | grant_option | granted_by | |---------------------------------+------------------+------------+------------+------------+--------------+------------+ | Wed, 17 Dec 2014 18:19:37 -0800 | CREATE WAREHOUSE | ACCOUNT | DEMOENV | ANALYST | false | SYSADMIN | +---------------------------------+------------------+------------+------------+------------+--------------+------------+
List all the roles granted to the demo user:
SHOW GRANTS TO USER demo; +---------------------------------+------+------------+-------+---------------+ | created_on | role | granted_to | name | granted_by | |---------------------------------+------+------------+-------+---------------+ | Wed, 31 Dec 1969 16:00:00 -0800 | DBA | USER | DEMO | SECURITYADMIN | +---------------------------------+------+------------+-------+---------------+
List all roles and users who have been granted the analyst role:
SHOW GRANTS OF ROLE analyst; +---------------------------------+---------+------------+--------------+---------------+ | created_on | role | granted_to | grantee_name | granted_by | |---------------------------------+---------+------------+--------------+---------------| | Tue, 05 Jul 2016 16:16:34 -0700 | ANALYST | ROLE | ANALYST_US | SECURITYADMIN | | Tue, 05 Jul 2016 16:16:34 -0700 | ANALYST | ROLE | DBA | SECURITYADMIN | | Fri, 08 Jul 2016 10:21:30 -0700 | ANALYST | USER | JOESM | SECURITYADMIN | +---------------------------------+---------+------------+--------------+---------------+
List all privileges granted on future objects in the sales.public schema:
SHOW FUTURE GRANTS IN SCHEMA sales.public; +-------------------------------+-----------+----------+---------------------------+----------+-----------------------+--------------+ | created_on | privilege | grant_on | name | grant_to | grantee_name | grant_option | |-------------------------------+-----------+----------+---------------------------+----------+-----------------------+--------------| | 2018-12-21 09:22:26.946 -0800 | INSERT | TABLE | SALES.PUBLIC.<TABLE> | ROLE | ROLE1 | false | | 2018-12-21 09:22:26.946 -0800 | SELECT | TABLE | SALES.PUBLIC.<TABLE> | ROLE | ROLE1 | false | +-------------------------------+-----------+----------+---------------------------+----------+-----------------------+--------------+