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