Categories:

Context Functions (Session Object)

IS_ROLE_IN_SESSION

Verifies whether the argument value (i.e. role) is in the user’s active primary or secondary role hierarchy for the session or if the specified column contains a role that is in the user’s active primary or secondary role hierarchy for the session.

Inputs

String literal to specify a role directly or nonliteral to specify a column name.

Returns

  • TRUE:

    • For a string literal argument, the current user’s active primary role or secondary roles in the session inherit the privileges of the specified role.

      When the DEFAULT_SECONDARY_ROLES value is ALL, any role granted to the user inherits the privileges of the specified role.

      The specified role can be the current primary role or secondary role (i.e. the roles returned by CURRENT_ROLE or CURRENT_SECONDARY_ROLES, respectively) or any role lower in the role hierarchy.

    • For a nonliteral argument, Snowflake evaluates each row and returns a row that contains a value that specifies an active primary or secondary role in the user’s current session. Each row corresponds to a role name that the active primary or secondary role can see.

  • FALSE:

    • For a string literal argument, the specified role is either higher in the role hierarchy of the current primary or secondary roles or is not in the role hierarchy at all.

    • For a nonliteral argument, Snowflake evaluates each row. If a row contains a role name that is either higher in the role hierarchy of the current primary or secondary roles or is not in the role hierarchy at all, Snowflake does not return this row. In this case, Snowflake only returns rows containing the role names the active primary or secondary role can see (if any).

  • NULL:

    • In a data sharing consumer account, this function returns NULL if referencing a shared object (e.g. secure UDF or secure view), such as in a masking policy condition. This behavior prevents exposing the role hierarchy in a data sharing consumer account.

See also:

IS_OBJECT, Advanced Column-level Security Topics

Syntax

Literal: specify a role directly:

IS_ROLE_IN_SESSION( '<string_literal>' )

Nonliteral: specify a column:

IS_ROLE_IN_SESSION( <column_name> )

Arguments

'string_literal'

The name of the role.

column_name

The column name in a table or view.

Usage Notes

  • Use one syntax.

  • If using this function with Column-level Security or Row Access Policies, verify that your Snowflake account is Enterprise Edition or higher.

    Snowflake recommends using this function when the policy conditions need to evaluate role hierarchy and inherited privileges.

  • The function cannot cannot be used in the materialized view definition because the function is not deterministic and Snowflake cannot determine what data to materialize.

  • Literal syntax:

    • Only one role name can be passed as an argument.

    • The argument must be a string and capitalized.

  • Nonliteral syntax:

    • Only one column can be passed as an argument.

    • The column must have a STRING data type.

    • Specify the column as one of the following:

      • column_name

      • table_name.column_name

      • schema_name.table_name.column_name

      • database_name.schema_name.table_name.column_name

  • Virtual columns:

    A virtual column, which contains the result of a calculated value from an expression rather than the calculated value being stored in the table, is not supported.

    select is_role_in_session(upper(authz_role)) from t1;

    A virtual column is supported only when the expression has an alias for the column name:

    create view v2 as
select
  authz_role,
  upper(authz_role) as upper_authz_role
from t2;

select is_role_in_session(upper_auth_role);

Examples

Verify if the privileges granted to a specified role are inherited by the current role in the session:

select is_role_in_session('ANALYST');

+-------------------------------+
| IS_ROLE_IN_SESSION('ANALYST') |
|-------------------------------|
| True                          |
+-------------------------------+

Return active primary or secondary role values for the column named role_name:

select *
from myb.s1.t1
where is_role_in_session(t1.role_name);

Specify a role directly in a masking policy condition:

create or replace masking policy allow_analyst as (val string)
returns string ->
case
  when is_role_in_session('ANALYST') then val
  else '*******'
end;

Specify the column named AUTHZ_ROLE (i.e. authorized role) in a row access policy and then set the policy on the table column:

Create the policy:

create or replace row access policy rap_authz_role as (authz_role string)
returns boolean ->
is_role_in_session(authz_role);

Add the policy to a table:

alter table allowed_roles
  add row access policy rap_authz_role on (authz_role);

Specify the column named AUTHZ_ROLE in a row access policy that uses a mapping table to lookup the authorized role in a mapping table column called ROLE_NAME. After creating the policy, add the policy to the table containing the AUTHZ_ROLE column:

Create the policy:

create or replace row access policy rap_authz_role_map as (authz_role string)
returns boolean ->
exists (
  select 1 from mapping_table m
  where authz_role = m.key and is_role_in_session(m.role_name)
);

Add the policy to a table:

alter table allowed_roles
  add row access policy rap_authz_role_map on (authz_role);
⇧
Back to top