SHOW GRANTS¶
Lists all access control privileges that have been explicitly granted to roles, users, and shares.
For more information about privileges and roles, refer to Overview of Access Control.
For more information about shares, refer to About Secure Data Sharing.
Note
SHOW GRANTS is a special variation that uses different syntax from all the other SHOW <objects> commands.
Syntax¶
SHOW GRANTS [ LIMIT <rows> ]
SHOW GRANTS ON ACCOUNT [ LIMIT <rows> ]
SHOW GRANTS ON <object_type> <object_name> [ LIMIT <rows> ]
SHOW GRANTS TO {
APPLICATION <app_name>
| APPLICATION ROLE [ <app_name>. ]<app_role_name>
| SERVICE ROLE <service_name>!<service_role_name>
| <class_name> ROLE <instance_name>!<instance_role_name>
| ROLE <role_name>
| SHARE <share_name> [ IN APPLICATION PACKAGE <app_package_name> ]
| USER <user_name>
} [ LIMIT <rows> ]
SHOW GRANTS OF {
APPLICATION ROLE <app_role_name>
| SERVICE ROLE <service_name>!<service_role_name>
| ROLE <role_name>
} [ LIMIT <rows> ]
SHOW GRANTS OF SHARE <share_name> [ LIMIT <rows> ]
SHOW FUTURE GRANTS IN SCHEMA { <schema_name> } [ LIMIT <rows> ]
SHOW FUTURE GRANTS IN DATABASE { <database_name> } [ LIMIT <rows> ]
SHOW FUTURE GRANTS TO ROLE <role_name> [ LIMIT <rows> ]
SHOW FUTURE GRANTS TO DATABASE ROLE <database_role_name>
Variants¶
SHOW GRANTSSyntactically equivalent to
SHOW GRANTS TO USER current_user. Lists all the roles granted to the current user.
LIMIT rowsOptionally limits the maximum number of rows returned. The actual number of rows returned might be less than the specified limit. For example, the number of existing objects is less than the specified limit.
Default: No value (no limit is applied to the output).
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.
For database roles, you can use the fully qualified name,
database_name.database_role_name, or the relative name,database_role_name. If you use the relative name for the database role, Snowflake uses the database in session to resolve the relative name of the database role.
SHOW GRANTS TO ...APPLICATION app_nameLists all the privileges and roles granted to the application.
APPLICATION ROLE [ app_name. ]app_role_nameLists all the privileges and roles granted to the application role.
The name of the application,
app_name, is optional. If not specified, Snowflake uses the current application. If the application is not a database, this command does not return results.SERVICE ROLE service_name!service_role_nameLists the service endpoints for which the service role is granted the USAGE privilege.
class_name ROLE instance_name!instance_role_nameLists all the privileges and roles granted to the instance role.
If the database and schema that contains the
class_nameis not in use or is not specified in your search path, specify the fully-qualified name of the class. For example,SNOWFLAKE.CORE.BUDGET.For details, see the instance role example.
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.
SHARE share_nameLists all the privileges granted to the share.
SHARE share_name IN APPLICATION PACKAGE app_package_nameLists all of the privileges and roles granted to a share in the application package.
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.
SHOW GRANTS OF...APPLICATION ROLE [ app_name. ]app_roleLists all the users and roles to which the application role has been granted.
The name of the application,
app_name, is optional. If not specified, Snowflake uses the current application. If the application is not a database, this command does not return results.SERVICE ROLE service_name!service_role_nameLists all the users and roles to which the service role has been granted.
ROLE role_nameLists all users and roles to which the role has been granted.
SHARE share_nameLists all the accounts that are consuming the share. Accounts that have not yet consumed the share are excluded. To see all accounts that have been added to a share, query the SNOWFLAKE.ACCOUNT_USAGE.SHARES view.
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.
SHOW FUTURE GRANTS TO DATABASE ROLE database_role_nameLists all privileges on new (i.e. future) objects of a specified type in a database or schema granted to the database role.
A shared database role does not support future grants. For details, see the usage notes in the GRANT DATABASE ROLE … TO SHARE command.
Usage notes¶
The
granted_bycolumn 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_bycolumn 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.When using the SHOW GRANTS … TO SHARE IN APPLICATION PACKAGE syntax:
The
grantee_namecolumn specifies the name of the application package.The
granted_tocolumn specifiesAPPLICATION PACKAGE SHARE.
The
granted_by_role_typecolumn specifies the type of grantor role that performed the grant:ROLE,DATABASE_ROLE, orAPPLICATION_ROLE. This column only appears in the output when using the SHOW GRANTS ON syntax.A data sharing consumer can only view the privileges on objects that are granted to the share, such as SELECT on a table. Depending on how the grants are set up, the output of a SHOW GRANTS command that is run by the consumer might show empty values for shared objects in the following columns:
granted_to,grantee_name,granted_by_role_type, andgranted_by. For example:If an account role owns the shared object, the consumer cannot view the OWNERSHIP privilege on shared objects because the consumer cannot access (resolve) the role that owns the object (account roles are not shared).
If a database role owns the shared object and the provider shares the database role, the consumer can view the OWNERSHIP privilege on the shared object because they can resolve the shared database role.
The
grant_optionscolumn returnsFALSEwhen you run a SHOW GRANTS ON <object_type> <object_name> command for an object in the managed access schema.The
privilegecolumn includes the OWNERSHIP and MANAGE GRANTS privileges for the role that owns the managed access schema when you run a SHOW GRANTS ON SCHEMA <managed_access_schema> command.With database roles and the SHOW FUTURE GRANTS TO DATABASE ROLE syntax, the command returns results for database roles that are not granted to a share.
In the data sharing consumer account, this command does not return any rows when a shared database role is granted future privileges. However, depending on your account and the timing of support for future privileges to database roles in this command, you might see this error message:
Invalid state of the shared database role. Please revoke the future grants to the shared database role.As the consumer, ask the provider to revoke the future grants from the shared database role.
The command doesn’t require a running warehouse to execute.
The command only returns objects for which the current user’s current role has been granted at least one access privilege.
The MANAGE GRANTS access privilege implicitly allows its holder to see every object in the account. By default, only the account administrator (users with the ACCOUNTADMIN role) and security administrator (users with the SECURITYADMIN role) have the MANAGE GRANTS privilege.
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_role_type | granted_by | +---------------------------------+-----------+------------+------------+------------+--------------+--------------+----------------------+--------------+ | Thu, 07 Jul 2016 05:22:29 -0700 | OWNERSHIP | DATABASE | REALESTATE | ROLE | ACCOUNTADMIN | true | ROLE | ACCOUNTADMIN | | Thu, 07 Jul 2016 12:14:12 -0700 | USAGE | DATABASE | REALESTATE | ROLE | PUBLIC | false | ROLE | 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 | +-------------------------------+-----------+----------+---------------------------+----------+-----------------------+--------------+
List all roles privileges granted to the instance role named cost.budgets.my_budget!ADMIN:
SHOW GRANTS TO SNOWFLAKE.CORE.BUDGET ROLE cost.budgets.my_budget!ADMIN;+-------------------------------+-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------+ | created_on | privilege | granted_on | name | +-------------------------------+-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------+ | 2023-10-31 15:57:41.489 +0000 | USAGE | ROLE | SNOWFLAKE.CORE.BUDGET!ADMIN | | 2023-09-25 22:56:12.798 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!ACTIVATE():VARCHAR(16777216) | | 2023-09-25 22:56:13.304 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!ADD_RESOURCE(TARGET_REF VARCHAR):VARCHAR(16777216) | | 2023-09-25 22:56:12.863 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_ACTIVATION_DATE():DATE | | 2023-09-25 22:56:12.412 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_BUDGET_NAME():VARCHAR(16777216) | | 2023-09-25 22:56:11.510 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_CONFIG():TABLE: () | | 2023-09-25 22:56:13.432 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_LINKED_RESOURCES():TABLE: () | | 2023-09-25 22:56:11.582 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_MEASUREMENT_TABLE():TABLE: () | | 2023-09-25 22:56:12.153 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_NOTIFICATION_EMAIL():VARCHAR(16777216) | | 2023-09-25 22:56:12.016 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_NOTIFICATION_INTEGRATION_NAME():VARCHAR(16777216) | | 2023-09-25 22:56:12.286 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_NOTIFICATION_MUTE_FLAG():VARCHAR(16777216) | | 2023-09-25 22:56:13.068 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_SERVICE_TYPE_USAGE(SERVICE_TYPE VARCHAR):TABLE: () | | 2023-09-25 22:56:13.245 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_SERVICE_TYPE_USAGE(SERVICE_TYPE VARCHAR, TIME_DEPART VARCHAR, USER_TIMEZONE VARCHAR, TIME_LOWER_BOUND VARCHA | | 2023-09-25 22:56:12.595 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_SPENDING_HISTORY():TABLE: () | | 2023-09-25 22:56:12.732 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_SPENDING_HISTORY(TIME_LOWER_BOUND VARCHAR, TIME_UPPER_BOUND VARCHAR):TABLE: () | | 2023-09-25 22:56:11.716 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!GET_SPENDING_LIMIT():NUMBER(38,0) | | 2023-09-25 22:56:13.367 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!REMOVE_RESOURCE(TARGET_REF VARCHAR):VARCHAR(16777216) | | 2023-09-25 22:56:11.856 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!SET_EMAIL_NOTIFICATIONS(NOTIFICATION_CHANNEL_NAME VARCHAR, EMAIL VARCHAR):VARCHAR(16777216) | | 2023-09-25 22:56:12.349 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!SET_NOTIFICATION_MUTE_FLAG(USER_MUTE_FLAG BOOLEAN):VARCHAR(16777216) | | 2023-09-25 22:56:11.780 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!SET_SPENDING_LIMIT(SPENDING_LIMIT FLOAT):VARCHAR(16777216) | | 2023-09-25 22:56:12.475 +0000 | USAGE | PROCEDURE | SNOWFLAKE.CORE.BUDGET!SET_TASK_SCHEDULE(NEW_SCHEDULE VARCHAR):VARCHAR(16777216) | +-------------------------------+-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------+