Hardening Snowflake security — Part 2

In the first part of my blog series on hardening Snowflake security, I demonstrated how to effectively use Snowflake Trust Center to surface security vulnerabilities in our Snowflake instance. I also covered how to query trust center finding views to identify users having passwords. I could not stress enough on the fact that, password usage should be discouraged to login to Snowflake.

Having said that, we might still retain passwords for few users from below category:

1. Users having ACCOUNTADMIN roles granted. This is required because, in case of SSO system disruption, at least ACCOUNTADMIN users should be able to access Snowflake system and perform regular monitoring, health checks and optimization duties.
2. In this modern era of IP applications, there are still few applications — mostly on premise, that do not work well with RSA Key pair, SSO or OAuth due to technical limitations.

In such cases, it is required to retain the password based connectivity. However, this should be allowed only in the combination of MFA. You can check process to setup MFA for Snowflake here.

The next action you would like to consider as a Snowflake platform administrator is to implement effective password policy. Password policy in Snowflake is a set of rules that you can enforce on a password set for any user or at entire account level. It consists of below possible rules that you can customize and try to align with your organizations generic password policy. Below are all possible rules that can be applied with password policy.

1. NAME: This is the name of the password policy that you wish to provide. Password policy is a schema level object and hence take precaution to create it in right pre defined schema — possible schema which stores all types of policies e.g. masking policy etc
2. PASSWORD_MIN_LENGTH : This enforces minimum length for user defined password. A longer password increases complexity and makes it harder to crack. This length can range from 8 to 256, default being 8.
3. PASSWORD_MAX_LENGTH: This enforces maximum length limit on password length — minimum 8 to maximum 256 with default 256. It should also be greater than on equal to sum of various parameters like PASSWORD_MIN_LENGTH, PASSWORD_MIN_UPPER_CASE_CHARS and PASSWORD_MIN_LOWER_CASE_CHARS.
4. PASSWORD_MIN_UPPER_CASE_CHARS: This specifies the number of minimum upper case characters that your password must contain. It can hold value from 0 to 256, default being 1.
5. PASSWORD_MIN_LOWER_CASE_CHARS: Same as above, except this is for lower case characters in the password
6. PASSWORD_MIN_NUMERIC_CHARS: Same as above, except this is for numerical characters in the password
7. PASSWORD_MIN_SPECIAL_CHARS: Same as above, except this is for numerical characters in the password
8. PASSWORD_MIN_AGE_DAYS: This specifies the number of days admin should make users wait before they change recently changed password. It can range from 0 to 999, default being 0
9. PASSWORD_MAX_AGE_DAYS: This is very important parameter compared to others. With this, account administrators can force users to change their passwords after certain number of days. You can align this parameter with your organization standard security guidelines.
10. PASSWORD_MAX_RETRIES: This parameter dictates how many login attempts are allowed before user is locked out of Snowflake. It can hold value from 1 to 10, default being 5.
11. PASSWORD_LOCKOUT_TIME_MINS: This parameter specifies the number of minutes user account will remain locked after exhausting PASSWORD_MAX_RETRIES count. It is defaulted to 15, with range from 1 to 999.
12. PASSWORD_HISTORY: This specifies number of history / old passwords Snowflake remembers (and do not allow users to reuse).
13. COMMENT: Standard Snowflake comment property

Once created, it can be applied at user or account or both levels by using ALTER USER user1 SET PASSWORD POLICY policy_name or ALTER ACCOUNT SET PASSWORD POLICY policy_name statements.

Unfortunately, as of now, Snowflake does not have mechanism to alert users if their password is about to expire through email or alert notification in Snowsight UI. Hence we have to build a custom solution with below logic to implement this feature. We will build procedure with below business logic implemented.

1. Execute SHOW USERS and collect the output in a table variable inside the procedure
2. DECLARE a cursor on he RESULTSET and LOOP to pick all the users with password one by one.
3. Execute DESC USER user1 and pick up the value of parameter PASSWORD_LAST_SET_TIME. Add value of parameter PASSWORD_MAX_AGE_DAYS to it. In the example below, considering 90 days of maximum ageing days value, calculated value would be 2024–09–01.

4. Now compare this calculated value against current timestamp. Now based on your organization security posture, you can start sending Email alerts to users 7 days before password expiry till the time when user actually change the password. For the above example, this procedure should start sending alert to user from 25th August 2024.

5. Change of password by user will automatically advance the field value for PASSWORD_LAST_SET_TIME and procedure will not generate any false alert.

I would place sample code for this procedure in the next part. Please stay tuned !

This same process has to be followed for RSA public keys set for service accounts — technical applications connecting to Snowflake. However, currently Snowflake does not provide a field qRSA_KEY_LAST_SET_TIME that can help us to drive the reminder emails for proactive password change. We can maintain a custom table or leverage COMMENT field for USER to achieve this.

In the next part in this series, we will see another security measure that we must implement in order to improve our account security.

Thank you !