Categories:

Context Functions

CURRENT_AVAILABLE_ROLES

Returns a string (VARCHAR) that lists all roles granted to the current user.

The returned string contains a JSON-encoded list of all roles that the user can activate in a user session (using USE ROLE or USE SECONDARY ROLES statements).

The list includes all roles that are granted directly to the user plus all roles lower in the hierarchies of these roles. The returned value can be passed to the PARSE_JSON function to get a VARIANT that contains a list of all the available roles.

See also:

CURRENT_ROLE , CURRENT_SECONDARY_ROLES , IS_ROLE_IN_SESSION

Syntax

CURRENT_AVAILABLE_ROLES()

Usage Notes

  • This function returns a list of roles only when queried by a user. Querying the function using a service that has no active user could result in a failed query. For example, the function does not return a list of roles when queried within a task, because the task runs are executed by a system service that is not associated with a user. In this case, the query could time out because the query plan cannot be completed.

Examples

Return the list of roles granted to the current user:

SELECT CURRENT_AVAILABLE_ROLES();

+----------------------------------------------------------+
| ROW | CURRENT_AVAILABLE_ROLES()                          |
+-----+----------------------------------------------------+
|  1  | [ "PUBLIC", "ANALYST", "DATA_ADMIN", "DATA_USER" ] |
+-----+----------------------------------------------------+

Use the PARSE_JSON function to return a VARIANT and the FLATTEN function to obtain a single row for each role:

SELECT INDEX,VALUE,THIS FROM TABLE(FLATTEN(input => PARSE_JSON(CURRENT_AVAILABLE_ROLES())));

+-----+-------+------------------------+---------------------------+
| ROW | INDEX | VALUE                  | THIS                      |
+-----+-------+------------------------+---------------------------+
|   1 |     0 | "PUBLIC"               | [                         |
|     |       |                        |   "PUBLIC",               |
|     |       |                        |   "ANALYST",              |
|     |       |                        |   "DATA_ADMIN",           |
|     |       |                        |   "DATA_USER"             |
|     |       |                        | ]                         |
+-----+-------+------------------------+---------------------------+
|   2 |     1 | "ANALYST"              | [                         |
|     |       |                        |   "PUBLIC",               |
|     |       |                        |   "ANALYST",              |
|     |       |                        |   "DATA_ADMIN",           |
|     |       |                        |   "DATA_USER"             |
|     |       |                        | ]                         |
+-----+-------+------------------------+---------------------------+
|   3 |     2 | "DATA_ADMIN"           | [                         |
|     |       |                        |   "PUBLIC",               |
|     |       |                        |   "ANALYST",              |
|     |       |                        |   "DATA_ADMIN",           |
|     |       |                        |   "DATA_USER"             |
|     |       |                        | ]                         |
+-----+-------+------------------------+---------------------------+
|   4 |     3 | "DATA_USER"            | [                         |
|     |       |                        |   "PUBLIC",               |
|     |       |                        |   "ANALYST",              |
|     |       |                        |   "DATA_ADMIN",           |
|     |       |                        |   "DATA_USER"             |
|     |       |                        | ]                         |
+-----+-------+------------------------+---------------------------+