Snowflake Provision User — Script

Humans are users too….

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:

Snowflake Sandbox — With Scripts

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();