CURRENT_AVAILABLE_ROLES¶

Syntax¶

CURRENT_AVAILABLE_ROLES()

Arguments¶

None.

Returns¶

Returns a string (VARCHAR) that is a JSON-encoded list of available account-level 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.

Usage notes¶

• This function returns a list of account-level 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.

• This function does not return the names of database roles, application roles, or class instance roles.

• This function does not account for role activation in a session.

  For example, if specifying this function in the conditions of a masking policy or a row access policy, the policy might inadvertently restrict access.

  If role activation and role hierarchy is necessary in the policy conditions, use IS_ROLE_IN_SESSION.

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" ] |
+-----+----------------------------------------------------+

Copy

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"             |
|     |       |                        | ]                         |
+-----+-------+------------------------+---------------------------+

Copy