SHOW ROLES¶
Lists all the roles which you can view across your entire account, including the system-defined roles and any custom roles that exist.
Important
Snowflake allows users to list roles; however, the ability to list roles is not the same as using any role. Knowing the names of roles does not allow any additional access.
This is a part of Discretionary Access Control and Role-Based Access Control. For more information, see Overview of Access Control.
- See also:
Syntax¶
SHOW ROLES [ LIKE '<pattern>' ]
Parameters¶
LIKE 'pattern'
Filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (
%
and_
).For example, the following patterns return the same results:
... LIKE '%testing%' ...
... LIKE '%TESTING%' ...
Usage Notes¶
The command does not require a running warehouse to execute.
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¶
Show all roles:
SHOW ROLES;---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+ created_on | name | is_default | is_current | is_inherited | assigned_to_users | granted_to_roles | granted_roles | owner | comment | ---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+ Fri, 05 Dec 2014 16:25:06 -0800 | ACCOUNTADMIN | Y | Y | N | 1 | 0 | 2 | | | Mon, 15 Dec 2014 17:58:33 -0800 | ANALYST | N | N | N | 0 | 6 | 0 | SECURITYADMIN | Data analyst | Fri, 05 Dec 2014 16:25:06 -0800 | PUBLIC | N | N | Y | 0 | 0 | 0 | | | Fri, 05 Dec 2014 16:25:06 -0800 | SECURITYADMIN | N | N | Y | 0 | 1 | 0 | | | Fri, 05 Dec 2014 16:25:06 -0800 | SYSADMIN | N | N | Y | 5 | 1 | 2 | | | ---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+
In this example:
The ACCOUNTADMIN system-defined role is the current role and default role for the current (i.e. logged-in) user.
In addition to the four system-defined roles, one custom role (ANALYST) has been created. The role is owned by the SECURITYADMIN system-defined role.