Author: Sachin Rai
A stored procedure to automate the implementation of layered access control.
Requirement
- To automate the implementation of the below depicted layered access control with snowflake stored procedure.
- Parameterizing the users and providing them as input arrays.
- Creating the users, roles, functional, and access roles as illustrated.
- Assigning the privileges to roles on database objects and warehouses and further to respective users.
- Storing the first login credentials of users in a table named USER_CRED_DETAILS.
- After creating the user, snowflake should enforce changing the password.
Prerequisite objects
Below objects are created with role sysadmin.
To change the role to sysadmin: use role sysadmin;
- A database ASNMNT_DB
- create database ASNMNT_DB;
- Schemas DEV, MAIN, USERS_SC
- create schema DEV;
- create schema MAIN;
- create schema USERS_SC;
- Three warehouses, two comparatively larger than the third DEV_WH, MAIN_WH, ELT_WH
- create warehouse if not exists DEV_WH warehouse_size=SMALL AUTO_SUSPEND=100 AUTO_RESUME=TRUE initially_suspended=TRUE;
- create warehouse if not exists MAIN_WH warehouse_size=SMALL AUTO_SUSPEND=100 AUTO_RESUME=TRUE initially_suspended=TRUE;
- create warehouse if not exists ELT_WH warehouse_size=XSMALL AUTO_SUSPEND=100 AUTO_RESUME=TRUE initially_suspended=TRUE;
- A table to store first login credentials USER_CRED_DETAILS.
- Create or replace table ASNMNT_DB.USERS_SC.USER_CRED_DETAILS
(username varchar(30),
user_password varchar(30));
After the objects are created, we need to change the role to securityadmin and grant privileges on these objects to securityadmin.
To change the role to securityadmin: use role securityadmin;
Grants:
- grant usage on warehouse DEV_WH to role securityadmin with grant option;
- grant usage on warehouse MAIN_WH to role securityadmin with grant option;
- grant usage on warehouse ELT_WH to role securityadmin with grant option;
- grant usage on warehouse COMPUTE_WH to role securityadmin with grant option;
- grant usage on database asnmnt_db to role securityadmin with grant option;
- grant usage on schema asnmnt_db.dev to role securityadmin with grant option;
- grant usage on schema asnmnt_db.main to role securityadmin with grant option;
- grant all privileges on schema asnmnt_db.users_sc to role securityadmin with grant option;
- grant all privileges on all tables in schema ASNMNT_DB.USERS_SC to role securityadmin;
- grant all privileges on future tables in schema ASNMNT_DB.USERS_SC to role securityadmin;
Note:
- The role is changed to securityadmin as only securityadmin and useradmin have access to create roles and users and grant roles.
- “with grant option” is used in the above queries to enable securityadmin to grant privileges on these objects to further created roles.
Implementation:
Step 1: Use role securityadmin and run the below-mentioned code to create the stored procedure:
CREATE OR REPLACE PROCEDURE user_role_setup2(USR_ARR array)
returns string
language javascript
strict
execute as owner
as
$$
var usr_roles = [“DEV_TEAM”,”READ_ALL”,”MAIN_TEAM”,”ELT”,”DEV_CRUD”,”DEV_READ_ONLY”,”MAIN_READ_ONLY”,”MAIN_CRUD”];
// creating users
var crt_usr_to_exec=[]
var drp_all=[]
// drop users
for (j=0; j<USR_ARR.length; j++)
{
var drp_usr=”drop user if exists “+USR_ARR[j];
var del_tbl = “delete from asnmnt_db.users_sc.user_cred_details where username ='”+USR_ARR[j]+”‘”;
drp_all.push(drp_usr);
drp_all.push(del_tbl);
}
//drop roles
for (s=0; s<usr_roles.length; s++)
{
var drp_roles = “drop role if exists “+usr_roles[s];
drp_all.push(drp_roles);
}
for (i=0; i<USR_ARR.length; i++)
{
var pass=Math.random().toString(36).substring(2, 7)+”d”+ Math.random().toString(36).substring(2, 7);
var crt_usr = “create or replace user “+USR_ARR[i]+” password= ‘” +pass+ “‘ default_role = PUBLIC must_change_password = true”;
var usr_cred = “insert into asnmnt_db.users_sc.user_cred_details values(‘”+USR_ARR[i]+”‘,'”+pass+”‘)”;
crt_usr_to_exec.push(crt_usr);
crt_usr_to_exec.push(usr_cred);
}
// creating roles
var crt_roles_to_exec=[]
for (k=0; k<usr_roles.length; k++)
{
var crt_role = “create or replace role “+usr_roles[k];
crt_roles_to_exec.push(crt_role);
}
// granting access roles
var grnt_dev_crud_role = “grant insert,update,delete,truncate on future tables in schema ASNMNT_DB.DEV to role DEV_CRUD”;
var grnt_dev_read_only_sc = “grant select on future tables in schema ASNMNT_DB.DEV to role DEV_CRUD”;
var grnt_dev_read_only_wh = “grant usage on warehouse DEV_WH to role DEV_READ_ONLY”;
var grnt_main_read_only_wh = “grant usage on warehouse MAIN_WH to role MAIN_READ_ONLY”;
var grnt_main_read_only_sc = “grant select on future tables in schema ASNMNT_DB.MAIN to role MAIN_READ_ONLY”;
var grnt_main_crud_role = “grant insert,update,delete,truncate on future tables in schema ASNMNT_DB.MAIN to role MAIN_CRUD”;
// granting functional roles
var dev_team_role = “grant role DEV_TEAM to role DEV_CRUD”;
var dev_crud_dev_read_only_role = “grant role DEV_CRUD to role DEV_READ_ONLY”;
var dev_read_role = “grant role READ_ALL to role DEV_READ_ONLY”;
var main_read_role = “grant role READ_ALL to role MAIN_READ_ONLY”;
var main_dev_role = “grant role MAIN_CRUD to role MAIN_READ_ONLY”;
var main_role = “grant role MAIN_TEAM to role MAIN_CRUD”;
var elt_role = “grant role ELT to role MAIN_CRUD”;
// granting roles
var grnt_usr_role_to_exec=[]
for (a=0; a<USR_ARR.length; a++)
{
if (a==0 || a==1)
{
var dev_team_usr = “grant role DEV_TEAM to user “+USR_ARR[a];
grnt_usr_role_to_exec.push(dev_team_usr);
}
else if (a == 2)
{
var read_all_usr = “grant role READ_ALL to user “+USR_ARR[a];
grnt_usr_role_to_exec.push(read_all_usr);
}
else if (a == 3 || a==4)
{
var main_team_usr = “grant role MAIN_TEAM to user “+USR_ARR[a];
grnt_usr_role_to_exec.push(main_team_usr);
}
else if (a==4 || a==5)
{
var elt_usr = “grant role ELT to user “+USR_ARR[a];
grnt_usr_role_to_exec.push(elt_usr);
}
}
try
{
for (g=0; g<crt_usr_to_exec.length; g++)
{
snowflake.execute({sqlText: crt_usr_to_exec[g]});
}
for (l=0; l<crt_roles_to_exec.length; l++)
{
snowflake.execute({sqlText: crt_roles_to_exec[l]});
}
snowflake.execute({sqlText: grnt_dev_crud_role});
snowflake.execute({sqlText: grnt_dev_read_only_sc});
snowflake.execute({sqlText: grnt_dev_read_only_wh});
snowflake.execute({sqlText: grnt_main_read_only_wh});
snowflake.execute({sqlText: grnt_main_read_only_sc});
snowflake.execute({sqlText: grnt_main_crud_role});
snowflake.execute({sqlText: dev_team_role});
snowflake.execute({sqlText: dev_crud_dev_read_only_role});
snowflake.execute({sqlText: dev_read_role});
snowflake.execute({sqlText: main_read_role});
snowflake.execute({sqlText: main_dev_role});
snowflake.execute({sqlText: main_role});
snowflake.execute({sqlText: elt_role});
for (f=0; f<grnt_usr_role_to_exec.length; f++)
{
snowflake.execute({sqlText: grnt_usr_role_to_exec[f]});
}
return “Success”;
}
catch (err)
{
for (d=0; d<drp_all.length; d++)
{
snowflake.execute({sqlText: drp_all[d]});
}
return “Failed: ” + err;
}
$$;
Step 2: Once the procedure is created, call the procedure:
CALL user_role_setup2(array_construct(‘JANEEN’,’TEDD’,’RAJ’,’IAN’,’TREVOR’,’GUIDO’));
Output
- After successful completion of the procedure, provided users, functional and access roles are created, and the above-mentioned requirement of layered access control is implemented.
- If any exception or failure is encountered while executing the procedure, the functionalities implemented by it are rolled back.