3
0
As we know, when the User gets access to Snowflake a database session creates for the user. Once the Session is available, Application can use this session to submit queries into Snowflake. By default this session remains active for next 4 hours before logging out if user does not perform any activity or operation on snowflake Account.
Now in case if Business wants to override this 4 hours Timeout value as per their mutual agreement. So the first question comes, Should we get in touch with Snowflake support team to override this session parameter value. The answer is ‘No’, as snowflake has given full leverage to the Account administrator that they can configure this value based on their use case.
To override the time limit of 4 hours snowflake introduces the concept of Session Policy. Session policies require Enterprise Edition or higher.
A session policy defines the idle session timeout period in minutes. Administrators can optionally set different timeout values for the Snowflake web interface and other Snowflake clients.The session policy can be set for both at ACCOUNT and USER level. In case a policy is tied to both level, 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 Console
Syntax to create the Policy:
CREATE [OR REPLACE] SESSION POLICY [IF NOT EXISTS] <name>
[ SESSION_IDLE_TIMEOUT_MINS = <integer>]
[ SESSION_UI_IDLE_TIMEOUT_MINS = <integer>]
[ COMMENT = '<string literal> ‘]
Integer value: Represents the number of minutes specified. The number of minutes can be any integer between 5 and 240, inclusive.
Session Policy example:
CREATE OR REPLACE SESSION POLICY SESSION_TIM_OUT
SESSION_IDLE_TIMEOUT_MINS = 100
SESSION_UI_IDLE_TIMEOUT_MINS = 100;
This will logout your session after 100 mins due to inactivity on the account.
Set at User level:
ALTER USER SACHINSNOWPRO SET SESSION POLICY SESSION_TIM_OUT;
Set at Account Level:
ALTER ACCOUNT SET SESSION POLICY SESSION_TIM_OUT;
Get Policy Details:
Describe session policy SESSION_TIM_OUT;
Policy References:
POLICY_REFERENCES returns the row to show Session Policy is assigned to User and Account.
SELECT *
FROM TABLE(
DEMO_DB.INFORMATION_SCHEMA.POLICY_REFERENCES(
POLICY_NAME => 'SESSION_TIM_OUT'
));
Unset Policy:
To Unset the Policy below commands used:
ALTER ACCOUNT UNSET SESSION POLICY;
ALTER USER sachinsnowpro UNSET SESSION POLICY;
CLIENT_SESSION_KEEP_ALIVE
In extension to it if we want the session remain active indefinitely as long as the connection to Snowflake is active then snowflake provides parameter:
CLIENT_SESSION_KEEP_ALIVE with option equals to TRUE.
Verify the Parameter value at ACCOUNT and User level:
SHOW PARAMETERS in ACCOUNT;
SHOW PARAMETERS in USER;
Set at Account or User level:
ALTER ACCOUNT SET CLIENT_SESSION_KEEP_ALIVE = TRUE;
ALTER USER SET CLIENT_SESSION_KEEP_ALIVE = TRUE;
Note: Sometimes we see that despite setting the CLIENT_SESSION_KEEP_ALIVE = TRUE, session is still logging out.
It can be the possibility that Session Policy is there and it is causing sessions to terminate despite CLIENT_SESSION_KEEP_ALIVE being set to True. So first unset or drop the session policy and set SESSION_KEEP_ALIVE parameter. Therefore, This will make your session remains active and will not ask the password to get login again.
As recommended by Snowflake we should not change it very frequently unless required for your specific business use case.
