1
0
During this post we will discuss latest feature in snowflake i.e. User Notification: SYSTEM$SEND_EMAIL() Stored Procedure. Snowflake has introduced a new mechanism to notifying users based on monitoring and administrative email notification with in the same account. SYSTEM$SEND_EMAIL() uses the notification integration object, which is a Snowflake object that provides an interface between Snowflake and third-party services (e.g. cloud message queues, email, etc.
Therefore, to create an email notification integration, use the CREATE NOTIFICATION INTEGRATION command with TYPE=EMAIL:
We have already seen couple of post how we can configure and enable this feature in snowflake. For our scenario we will implement this new feature with the one use case.
Consider the scenario where multiple USERS exists or created in your snowflake account. The source of these users are from different systems. Some of the users are directly created by ACCOUNTADMIN from the UI itself. However, some users are created by the SUPER Role, a role created by ACCOUTADMIN with CREATE ROLE and USER privileges ON ACCOUNT to super role. Also we have Azure Active directory(AAD) integration and some of the users gets synch by AAD provisioning. So in short there are multiple ways the Users are available in Snowflake Account.
Moreover, Now Management want to keep the track how the users are getting created and an email notification should be triggered. Finally, this Email should provide the count of Users along with User Name belong to the Source system.
Notification:
So we have created below two integrations:
Firstly ADMIN_USER:
CREATE OR REPLACE NOTIFICATION INTEGRATION
ADMIN_USER
TYPE=EMAIL
ENABLED=TRUE
ALLOWED_RECIPIENTS=('sachin.mittal04@gmail.com','meetsachin29@rediffmail.com')
COMMENT = 'Snowflake Users Created by Accountadmin or Super Role';
This will notify the above two mail ID if User is gets create by ACCOUNTADMIN or SUPER Role
Secondly,AAD_USER for Azure provisioning.
CREATE OR REPLACE NOTIFICATION INTEGRATION
AAD_USER
TYPE=EMAIL
ENABLED=TRUE
ALLOWED_RECIPIENTS=('sachin.mittal04@gmail.com','sachin.mittal@centricconsulting.com')
COMMENT = 'Snowflake Users Created by Azure Active Directory';
This will notify the above two mail ID if User gets create by AAD.
So, We have developed the below javascript stored procedure and leverage the vanilla SYSTEM$SEND_EMAIL() Stored Procedure. This procedure evaluates the users and send the respective details to the concerned stakeholders.
Execute the above procedure and it will send an email to respective stakeholders along with User details.
call USERS_TYPE_NOTIFY();
output:
Thanks for the detailed steps.
I am unable to get variable printed in the stored proc system$send_email. It is only printing plain text.
Is this feature enabled in snowflake?
If possible could you help me with the script to test this?