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.

In this Topic:

Snowflake Sessions

A session begins when a user connects to Snowflake and authenticates successfully using a Snowflake programmatic client or the classic or new web interface. 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.

Snowflake Classic Web Interface 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 web interface, save any necessary SQL statements to a local file and close any open worksheets that are not in use.

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.

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 web interface.

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.

Note

Note the following regarding session policies:

For more information, see:

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.

  • Additionally, the CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY parameter can be used to specify the number of seconds in 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.

Limitations

  • Snowsight does not currently support session policies. This support is expected when session policies become generally available (GA).

  • Replication. Database Replication and Failover/Failback does not include session policies and their associations in a snapshot of database objects. This support is expected when session policies are generally available.

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;

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 my_database to role policy_admin;

grant usage, create session policy on schema my_schema to role policy_admin;

grant create session policy on schema my_schema to role policy_admin;

grant apply session policy on account to role policy_admin;

-- If associating a session policy with an individual user.
grant apply session policy on user jsmith to role policy_admin;

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 my_database.my_schema.session_policy_prod_1
  SESSION_IDLE_TIMEOUT_MINS = 60
  SESSION_UI_IDLE_TIMEOUT_MINS = 60
  COMMENT = 'Session policy for the prod_1 environment'
;

Where:

my_database.my_schema.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 my_database.my_schema.session_policy_prod_1;

-- user

alter user jsmith set session policy my_database.my_schema.session_policy_prod_1_jsmith;

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 my_database.my_schema.session_policy_prod_2;

Step 5: Enforce the Session Policy — Optional

As an account administrator (i.e. a user with the ACCOUNTADMIN system role), set the ENFORCE_SESSION_POLICY parameter.

alter account set enforce_session_policy = true;

Important

When the ENFORCE_SESSION_POLICY parameter is set to TRUE:

  • When multiple worksheets are open in the classic web interface, any user activity in any worksheet updates the observed idle_session_timeout_value, as shown in the next bullet.

  • The classic web interface tracks user activity, such as mouse clicks, scrolling through results, and keyboard clicks, and sends a heartbeat to an internal Snowflake monitor. This heartbeat recording is sent every three minutes and is not configurable.

    Setting this parameter updates the observed idle session timeout value as follows:

    idle_session_timeout_value = (default_idle_timeout - session_policy_timeout) + 3 minutes`
    

    For example, the default_idle_timeout value is 4 hours, and the session_policy_timeout value is 1 hour (i.e. session_idle_timeout_mins = 60), then the observed idle_session_timeout_value is:

    idle_session_timeout_value = (240-60) + 3
    
    idle_session_timeout_value = 183 minutes
    

Session Policy Privilege Reference

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

Note

Session policies are schema-level objects.

Operating on a session policy also requires the USAGE privilege on the parent database and schema.

Privilege

Usage

CREATE SESSION POLICY

Set on the schema in which you are creating a session policy. Enables creating a new session policy in a schema.

APPLY SESSION POLICY

Set on the account or on individual users. Enables applying a session policy at the account or user level.

OWNERSHIP

Set on a session policy. 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. . 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.

-- set

alter account set session policy <policy_name>;

-- unset

alter account unset session policy;

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

-- set
alter user <username> set session policy <policy_name>;

-- unset
alter user <username> unset session policy;

Troubleshooting Session Policies

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 DROP SESSION POLICY statement has the OWNERSHIP privilege on the session policy or the APPLY SESSION POLICY on ACCOUNT privilege.

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.