Skip to main content

is_role_in_session

Description

Verifies whether a role (or a nested role) is active in the current session.

This function is supported from v3.1.4 onwards.

Syntax

BOOLEAN is_role_in_session(VARCHAR role_name);

Parameters

role_name: the role you want to verify (can also be a nested role). Supported data type is VARCHAR.

Return value

Returns a BOOLEAN value. 1 indicates the role is active in the current session. 0 indicates the opposite.

Examples

  1. Create roles and a user.

    -- Create three roles.
    create role r1;
    create role r2;
    create role r3;

    -- Create user u1.
    create user u1;

    -- Pass roles r2 and r3 to r1, and grant r1 to user u1. This way, user u1 has three roles: r1, r2, and r3.
    grant r3 to role r2;
    grant r2 to role r1;
    grant r1 to user u1;

    -- Switch to user u1 and perform operations as u1.
    execute as u1 with no revert;
  2. Verify whether r1 is active. The result shows this role is not active.

    select is_role_in_session("r1");
    +--------------------------+
    | is_role_in_session('r1') |
    +--------------------------+
    | 0 |
    +--------------------------+
  3. Run the SET ROLE command to activate r1 and use is_role_in_session to verify whether the role is active. The result shows r1 is active and roles r2 and r3 that are nested in r1 are also active.

    set role "r1";

    select is_role_in_session("r1");
    +--------------------------+
    | is_role_in_session('r1') |
    +--------------------------+
    | 1 |
    +--------------------------+

    select is_role_in_session("r2");
    +--------------------------+
    | is_role_in_session('r2') |
    +--------------------------+
    | 1 |
    +--------------------------+

    select is_role_in_session("r3");
    +--------------------------+
    | is_role_in_session('r3') |
    +--------------------------+
    | 1 |
    +--------------------------+