Snowflake Provision User — Script
A couple of weeks ago a customer of mine wanted to know how to auto provision a user from their SCIM/SSO integration. What they wanted to do was auto provision a sandbox schema for the user. I had already shared my Sandbox script with the user which is located here:
SANDBOX MEDIUM POST:
Below is a script that builds a stored procedure that creates a sandbox schema for the user.
The hard part was getting a set of users that were new and did not have a sandbox schema built for them already. To do this I wrote the following code:
SHOW USERS;
CREATE OR REPLACE TABLE SANDBOX_DB.PUBLIC.USER_P AS SELECT “login_name” || ‘_DEV’ SCHEMA_NAME
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
where “disabled” = false AND “created_on” >= current_date() ;
In the code above you will see that we use the MINUS clause to find out what users are new and if a schema exists for them based on the sandbox provisioning script detailed in the link above. If the user does not have a schema associated with their user id then it will go ahead and create one.
CAUTION: This does not limit users that are new and should not have a sandbox schema.
To get SHOW USERS to run in a stored procedure I had to add in the following code: EXECUTE AS CALLER
create or replace procedure PROVISION_USER_SANDBOX()
returns varchar
language sql
EXECUTE AS CALLER
You will need to add this stored procedure to a TASK to get it to fire off every hour or so. See my medium blog on Streams and Tasks if you need help.
The script is below and I am sure there are a hundred ways of doing this better so I look forward to your feedback!
— SCRIPT
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
— STORED PROCEDURE TO DEVELOP OBJECTS FOR USER PROVISIONING
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -
create or replace procedure PROVISION_USER_SANDBOX()
returns varchar
language sql
EXECUTE AS CALLER
as
declare
s_name VARCHAR;
n_name VARCHAR;
— c1 cursor;
begin
SHOW USERS;
CREATE OR REPLACE TABLE SANDBOX_DB.PUBLIC.USER_P AS SELECT “login_name” || ‘_DEV’ SCHEMA_NAME
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
where “disabled” = false AND “created_on” >= current_date() ;
let res resultset := (
SELECT SCHEMA_NAME FROM SANDBOX_DB.PUBLIC.USER_P
minus
SELECT SCHEMA_NAME FROM SANDBOX_DB.INFORMATION_SCHEMA.SCHEMATA as SCHEMA_NAME);
let c1 cursor for res;
for row_variable in c1 do
s_name := row_variable.SCHEMA_NAME;
n_name := RTRIM(s_name, ‘_DEV’);
call ADD_SANDBOX_USER(:n_name);
end for;
return ‘DONE’;
end;
call PROVISION_USER_SANDBOX();
