IS_DATABASE_ROLE_IN_SESSION¶

Syntax¶

Literal — specify a database role directly:

IS_DATABASE_ROLE_IN_SESSION( '<string_literal>' )

Nonliteral — specify a column:

IS_DATABASE_ROLE_IN_SESSION( <column_name> )

Copy

Arguments¶

'string_literal'

The name of the database role.

Specify either the fully qualified name or the relative name of the database role.

column_name

The column name in a table or view.

Usage Notes¶

These notes only apply to the IS_DATABASE_ROLE_IN_SESSION function:

• A database role becomes active in the role hierarchy when the database containing the database role is in use or when querying a table in the same database that contains the database role.

  If you are a data sharing consumer and you are using a database role to query policy-protected data, load the database containing the database role with a USE DATABASE database_name command prior to querying the policy-protected data. This command activates the database role in the role hierarchy of the current role for the user. For details, see Share data protected by a policy.

• When the user property DEFAULT_SECONDARY_ROLES value is ALL, the function returns True if any account role granted to the user inherits the privileges of the specified database role.

• When using this function in the condition of a masking policy or row access policy that protects shared data, ensure the database containing the policy and the policy-protected data are both shared to the consumer account. The policy and the policy-protected data can be in the same database or in different databases. For details, see Share data protected by a policy.

These notes apply to both the IS_DATABASE_ROLE_IN_SESSION and IS_ROLE_IN_SESSION functions:

• Use one syntax.

• Literal syntax:

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

  • The argument must be a string and capitalized.

  • The name of an account role is not supported.

• 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;
  

  Copy

  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_authz_role) FROM v2;
  

  Copy

• Policies:

  If you use these functions with a masking policy or row access policy, 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.

• Result cache:

  If you use this function in a masking policy or row access policy and neither the policy nor the table or column protected by the policy change from a previous query, you can use the RESULT_SCAN function to return the results of a query on the protected table. The result cache applies when using the nonliteral syntax only.

• These functions cannot be used in the materialized view definition because the functions are not deterministic and Snowflake cannot determine what data to materialize.

Examples¶

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

SELECT IS_DATABASE_ROLE_IN_SESSION('DB1.R1');

+---------------------------------------+
| IS_DATABASE_ROLE_IN_SESSION('DB1.R1') |
+---------------------------------------+
| True                                  |
+---------------------------------------+

Return database role values for the column named ROLE_NAME:

SELECT *
FROM myb.s1.t1
WHERE IS_DATABASE_ROLE_IN_SESSION(role_name);

Copy

For additional examples related to secure data sharing, see Share data protected by a policy.