Categories:
User & Security DDL (Access Control)

SHOW GRANTS

Lists all access control privileges that have been granted to roles, users, and shares. For more information about privileges and roles, see Access Control in Snowflake. 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 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> }

Variants

SHOW GRANTS ON ...

ACCOUNT
Lists all the account-level (i.e. global) privileges that have been granted to roles.
object_type object_name
Lists all privileges that have been granted on the object.

SHOW GRANTS TO ...

ROLE role_name
Lists all privileges and roles granted to the role.
USER user_name
Lists all the roles granted to the user.
SHARE share_name
Lists all the privileges granted to the share.

SHOW GRANTS OF...

ROLE role_name
Lists all users and roles to which the role has been granted.
SHARE share_name
Lists all the accounts for the share and indicates the accounts that are using the share.

SHOW FUTURE GRANTS IN ...

SCHEMA database_name.schema_name
Lists 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.

Usage Notes

  • 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 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        |
+-------------------------------+-----------+----------+---------------------------+----------+-----------------------+--------------+