0
0
Snowflake provides a rich set of features for identity management such as password policies, MFA, SSO integration, and role-based access control. However, one limitation we often face is automating password expiry notifications in Snowflake, since the platform does not natively notify users when their password is about to expire.
In large organizations, this can lead to:
- Sudden login failures when passwords expire.
- Disruptions in scheduled jobs using service accounts.
- Additional overhead for administrators handling urgent password reset requests.
To solve this, I built a framework in Snowflake that automatically detects users whose passwords are about to expire (e.g., within 10 days) and sends them an email alert.
Imagine an enterprise with:
- 100+ Snowflake users (mix of developers, analysts, and service accounts).
- A password policy with 90-day expiry.
- Frequent disruptions because users forget to reset their passwords.
By automating expiry notifications:
- Users are reminded in advance (say 10 days before expiry).
- Password resets happen proactively.
- Admin workloads reduce significantly.
Framework Overview
Framework Overview
The solution consists of two stored procedures and one supporting table:
USER_MASTER_TABLE
Stores metadata about each user (login, email, password expiry date).
CREATE OR REPLACE TABLE DEMO_DB.PUBLIC.USER_MASTER_TABLE (LOGIN VARCHAR,EMAIL VARCHAR,PWD_EXPIRY DATE);
Procedure 1: USER_MASTERDETAILS
-
- Creates user (if not exists).
- Reads the password policy (PASSWORD_MAX_AGE_DAYS).
- Calculates expiry date.
- Inserts login/email/expiry into USER_MASTER_TABLE.
- Assigns default roles and privileges.
Executed the procedure:
call user_masterdetails('SMITTAL','sachin.mittal04@gmail.com');
Stored Procedure 2 — Password Expiry Email Notification
Once user details are captured, the next step is to proactively notify users if their password is nearing expiry. This is where the PWD_SENT_EMAIL procedure comes in.
Core Logic
- Reads all active users from SNOWFLAKE.ACCOUNT_USAGE.USERS.
- Dynamically calculates each user’s password expiry date using:
- PASSWORD_LAST_SET_TIME (when the password was last reset).
- PASSWORD_MAX_AGE_DAYS (from the assigned password policy).
- Compares this expiry date with the current date.
- If the password is set to expire in 10 days or less, triggers an email via SYSTEM$SEND_EMAIL.
Verify PASSWORD_LAST_SET_TIME for user:
Execute the proc:
call PWD_SENT_EMAIL();
An Email sent to the stake holder:
Now Reset the password and run the proc again.
alter user SMITTL SET PASSWORD = ‘XXXXXX;
This time Dynamic expiry calculation works with no stale data, even if the user resets their password.
Scheduling the Notification
We can schedule the procedure PWD_SENT_EMAIL to run daily using a Snowflake Task:
CREATE OR REPLACE TASK PWD_EXPIRY_TASK
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 0 9 * * * UTC'
AS
CALL PWD_SENT_EMAIL();
This ensures that users are reminded every morning if their password is nearing expire date.
With a few stored procedures and tasks, we can bridge a critical governance gap in Snowflake — password expire notifications.
This framework is simple, effective, and extensible. For organizations managing hundreds of Snowflake users, it ensures business continuity and better security hygiene.
