- Categories:
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 specify in a USE ROLE statement. This includes all roles that are directly granted to the user plus all lower-privileged roles in the hierarchy of the roles granted to the user indirectly. 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:
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" | | | | | ] | +-----+-------+------------------------+---------------------------+