Snowflake Sessions & Session Policies

This topic describes Snowflake sessions and session policies and provides instructions for configuring session policies at the account or user level.

Snowflake Sessions

A session begins when a user connects to Snowflake and authenticates successfully using a Snowflake programmatic client, Snowsight, or the Classic Console. A session is independent of an identity provider (i.e. IdP) session. If the Snowflake session expires but the IdP session remains active, a user can log in to Snowflake without entering their login credentials again (i.e. silent authentication).

A session is maintained indefinitely with continued user activity. After a period of inactivity in the session, known as the idle session timeout, the user must authenticate to Snowflake again. The idle session timeout has a maximum value of four hours and a session policy can modify the idle session timeout period. The idle session timeout applies to the following:

Snowflake recommends reusing existing sessions when possible and to close the connection to Snowflake when a session is no longer needed.

Snowsight Sessions

Snowflake creates a new session for each worksheet in Snowsight. A worksheet session enforces the session policy that applies to the user that creates the worksheet.

Caution

Active queries are not canceled when the session ends and the user is logged out, even if the ABORT_DETACHED_QUERY parameter is set to true.

Classic Console Sessions

In the Worksheets Worksheet tab tab, Snowflake creates a new session every time a new worksheet is created. Each worksheet is limited to a maximum of 4 hours of idle behavior, and the idle timeout for each worksheet is tracked separately.

When a worksheet is closed, the user session for the worksheet ends.

After the 4-hour time limit expires for any open worksheet, Snowflake logs the user out of the web interface.

Note

Note that passive behaviors such as scrolling through the query result set or sorting a data set do not reset the idle session timeout tracker.

To prevent a session from closing too early and being logged out of the Classic Console, save any necessary SQL statements to a local file and close any open worksheets that are not in use.

Monitor Session Usage

You can monitor active sessions and session usage using Snowsight or a SQL view. You can view your own sessions, or use a role with access to view the SESSIONS view to view sessions for your account. See ACCOUNT_USAGE Schema SNOWFLAKE Database Roles.

SQL:

Query the SESSIONS view in the ACCOUNT USAGE schema of the shared SNOWFLAKE database to monitor session usage.

Snowsight:

Select Admin » Security, and then select Sessions. You can review the session ID, user name, start time, client driver in use for the session, client net address, and authentication method. Hover over the start time to view the exact date and time that the session started, in your local time zone.

Session Policies

A session policy defines the idle session timeout period in minutes and provides the option to override the default idle timeout value of 4 hours.

The session policy can be set for an account or user with configurable idle timeout periods to address compliance requirements. If a user is associated with both an account and user-level session policy, the user-level session policy takes precedence. After the session policy is set on the account or user, Snowflake enforces the session policy.

There are two properties that govern the session policy behavior:

  • SESSION_IDLE_TIMEOUT_MINS for programmatic and Snowflake Clients.

  • SESSION_UI_IDLE_TIMEOUT_MINS for the Classic Console and Snowsight.

The timeout period begins upon a successful authentication to Snowflake. If a session policy is not set, Snowflake uses a default value of 240 minutes (i.e. 4 hours). The minimum configurable idle timeout value for a session policy is 5 minutes. When the session expires, the user must authenticate to Snowflake again. However, Snowflake does not enforce any setting defined by the Custom logout endpoint.

For more information, see Managing session policies.

Considerations

  • If a client supports the CLIENT_SESSION_KEEP_ALIVE option and the option is set to TRUE, the client preserves the Snowflake session indefinitely as long as the connection to Snowflake is active. Otherwise, if the option is set to FALSE, the session ends after 4 hours. When possible, avoid using this option since it can result in many open sessions and place a greater demand on resources which can lead to a performance degradation.

  • You can use the CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY parameter to specify the number of seconds between client attempts to update the token for the session. The web interface session can be refreshed as Snowflake objects continue to be used, such as executing DDL and DML statements. Snowflake checks for this behavior every 30 seconds.

  • Creating a new worksheet or opening an existing worksheet continues to use the established user session but with its idle session timeout reset to 0.

  • Tracking session policy usage:

    • Query the Account Usage SESSION_POLICIES view to return a row for each session policy in your Snowflake account.

    • Use the Information Schema table function POLICY_REFERENCES to return a row for each user that is assigned to the specified session policy and a row for the session policy assigned to the Snowflake account.

      Currently, only the following syntax is supported for session policies:

      POLICY_REFERENCES( POLICY_NAME => '<session_policy_name>' )
      
      Copy

      Where session_policy_name is the fully qualified name of the session policy.

      For example, execute the following query to return a row for each user that is assigned the session policy named session_policy_prod_1, which is stored in the database named my_db and the schema named my_schema:

      SELECT *
      FROM TABLE(
        MY_DB.INFORMATION_SCHEMA.POLICY_REFERENCES(
          POLICY_NAME => 'my_db.my_schema.session_policy_prod_1'
        )
      );
      
      Copy

Limitations

Future grants:

Future grants of privileges on session policies are not supported.

As a workaround, grant the APPLY SESSION POLICY privilege to a custom role to allow that role to apply session policies on a user or the Snowflake account.

Implementing a Session Policy

The following steps are a representative guide to implementing a session policy.

These steps assume a centralized management approach in which a custom role named policy_admin owns the session policy (i.e. has the OWNERSHIP privilege on the session policy) and is responsible for setting the session policy on an account or user (i.e. has the APPLY SESSION POLICY on ACCOUNT privilege or the APPLY SESSION POLICY ON USER privilege).

Note

To set a policy on an account, the policy_admin custom role must have the following permissions:

  • USAGE on the database and schema that contain the session policy.

  • CREATE SESSION POLICY on the schema that contains the session policy.

Follow these steps to implement a session policy.

Step 1: Create the POLICY_ADMIN Custom Role

Create a custom role that allows users to create and manage session policies. Throughout this topic, the example custom role is named policy_admin, although the role could have any appropriate name.

If the custom role already exists, continue to the next step.

Otherwise, create the POLICY_ADMIN custom role.

USE ROLE USERADMIN;

CREATE ROLE policy_admin;
Copy

Step 2: Grant Privileges to the POLICY_ADMIN Custom Role

If the POLICY_ADMIN custom role does not already have the following privileges, grant these privileges as shown below:

  • USAGE on the database and schema that will contain the session policy.

  • CREATE SESSION POLICY on the schema that will contain the session policy.

  • APPLY SESSION POLICY on the account.

  • APPLY SESSION POLICY on each user, if you plan to set session policies at the user level.

USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE mydb TO ROLE policy_admin;

GRANT USAGE, CREATE SESSION POLICY ON SCHEMA mydb.policies TO ROLE policy_admin;

GRANT APPLY SESSION POLICY ON ACCOUNT TO ROLE policy_admin;
Copy

If associating a session policy with an individual user:

GRANT APPLY SESSION POLICY ON USER jsmith TO ROLE policy_admin;
Copy

For more information, see Summary of DDL Commands, Operations, and Privileges.

Step 3: Create a New Session Policy

Using the POLICY_ADMIN custom role, create a new session policy where the idle timeout value for programmatic clients, Snowflake clients, and the web interface is 60 minutes each. For more information, see CREATE SESSION POLICY.

USE ROLE POLICY_ADMIN;

CREATE SESSION POLICY mydb.policies.session_policy_prod_1
  SESSION_IDLE_TIMEOUT_MINS = 60
  SESSION_UI_IDLE_TIMEOUT_MINS = 60
  COMMENT = 'Session policy for the prod_1 environment'
;
Copy

Where:

mydb.policies.session_policy_prod_1

The fully qualified name of the session policy.

session_idle_timeout_mins = 60

The idle timeout period in minutes for Snowflake Clients and programmatic clients.

session_ui_idle_timeout_mins = 30

The idle timeout period in minutes for the Snowflake web interface.

comment = 'Session policy for the prod_1 environment'

A comment specifying the purpose of the session policy.

Step 4: Set the Session Policy on an Account or User

Using the POLICY_ADMIN custom role, set the policy on an account with the ALTER ACCOUNT command, or a user (e.g. username jsmith) with the ALTER USER command.

USE ROLE policy_admin;

ALTER ACCOUNT SET SESSION POLICY mydb.policies.session_policy_prod_1;

ALTER USER jsmith SET SESSION POLICY my_database.my_schema.session_policy_prod_1_jsmith;
Copy

Important

To replace a session policy that is already set for an account or user, unset the session policy first and then set the new session policy for the account or user. For example:

ALTER ACCOUNT UNSET session policy;

ALTER ACCOUNT SET SESSION POLICY mydb.policies.session_policy_prod_2;
Copy

Step 5: Replicate the Session Policy to a Target Account

A session policy and its references (i.e. assignments to a user or the account) can be replicated from the source account to the target account using database replication and account replication. For details, refer to:

Managing Session Policies

Session Policy Privilege Reference

Snowflake supports the following session policy privileges to determine whether users can create, set, and own session policies.

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

Privilege

Usage

CREATE

Enables creating a new session policy in a schema.

APPLY SESSION POLICY

Enables applying a session policy at the account or user level.

OWNERSHIP

Grants full control over the session policy. Required to alter most properties of a session policy.

Summary of DDL Commands, Operations, and Privileges

The following table summarizes the relationship between the session policy DDL operations and their necessary privileges.

Operation

Privilege required

Create session policy

A role with the CREATE SESSION POLICY privilege on the schema.

Alter session policy

A role with the OWNERSHIP privilege on the session policy.

Drop session policy

A role with the OWNERSHIP privilege on the session policy.

Describe session policy

A role with the OWNERSHIP privilege on the session policy or . the APPLY SESSION POLICY privilege on the account.

Show session policies

A role with the OWNERSHIP privilege on the session policy or . the APPLY SESSION POLICY privilege on the account.

Set & unset session policy

For accounts, a role with the APPLY SESSION POLICY privilege on the account and the OWNERSHIP privilege on the session policy, or a role with the APPLY SESSION POLICY privilege on the account and the APPLY ON SESSION POLICY privilege on a specific session policy.

For users, a role with the APPLY SESSION POLICY on USER <username> privilege.

Session Policy DDL Reference

Snowflake provides the following DDL commands to manage session policy objects:

To set or unset a session policy on the account, execute the ALTER ACCOUNT command as shown below.

ALTER ACCOUNT SET SESSION POLICY mydb.policies.session_policy_prod_1;
Copy
ALTER ACCOUNT UNSET SESSION POLICY;
Copy

To set or unset a user-level session policy, execute the ALTER USER command as shown below.

ALTER USER jsmith SET SESSION POLICY mydb.policies.session_policy_prod_1_jsmith;
Copy
ALTER USER jsmith UNSET SESSION POLICY;
Copy

Troubleshooting Session Policies

  • If a session policy is assigned to an account or a user and the database or schema that contains the session policy is dropped, and then a new session policy is assigned to the account or user, the user will not be held to the idle session timeout value(s) in the new session policy.

    The workaround is to unset the original session policy from the account using an ALTER ACCOUNT command or from the user using an ALTER USER command as shown in this topic.

  • The following table summarizes some error messages that can occur with session policies.

    Behavior

    Error Message

    Troubleshooting Action

    Cannot create a session policy.

    Cannot perform CREATE SESSION POLICY. This session does not have a current database. Call ‘USE DATABASE’, or use a qualified name.

    Specify a database prior to executing CREATE SESSION POLICY or use the fully qualified object name in the CREATE SESSION POLICY statement.

    Cannot create a session policy.

    SQL access control error: Insufficient privileges to operate on schema ‘<schema_name>’

    Verify that the role executing the CREATE SESSION POLICY statement has the CREATE SESSION POLICY on SCHEMA privilege.

    Cannot create a session policy.

    SQL compilation error: Database ‘<database_name>’ does not exist or not authorized.

    Verify that the database exists and that the role executing the CREATE SESSION POLICY statement has the USAGE privilege on the schema in which the session policy should exist.

    Cannot execute a describe statement.

    SQL compilation error: Schema ‘<schema_name>’ does not exist or not authorized.

    Verify that the role executing the DESC SESSION POLICY statement has the OWNERSHIP privilege on the session policy or the APPLY privilege on the session policy.

    Cannot drop a session policy.

    SQL compilation error: Session policy ‘<policy_name>’ does not exist or not authorized.

    Verify that the role executing the DROP SESSION POLICY statement has the OWNERSHIP privilege on the session policy.

    Cannot drop a session policy.

    Session policy <policy_name> cannot be dropped because it is attached to an account.

    Unset the session policy from the account with an ALTER ACCOUNT statement and try the drop statement again.

    Cannot set a session policy on an account.

    Session policy ‘<policy_name> is already attached to account <account_name>.

    An account can only have one active session policy. Determine which session policy should be set for the account. . If necessary, unset the current session policy from the account with a ALTER ACCOUNT command; then set the other session policy on the account with another ALTER ACCOUNT command.

    Cannot set a timeout value.

    SQL compilation error: invalid value ‘<integer>’ for property ‘session_idle_timeout_mins’

    The session timeout value, in minutes, must be an integer between 5 and 240, inclusive. . Choose a valid integer for the session timeout and execute the CREATE or ALTER SESSION POLICY statement again.

    Cannot update an existing session policy.

    SQL compilation error: Session policy ‘<policy_name>’ does not exist or not authorized.

    Verify the name of the session policy, the syntax of the ALTER SESSION POLICY command, and the privileges to operate on the session policy, database, and schema.