User Role Automation

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

1. create database ASNMNT_DB;

• Schemas DEV, MAIN, USERS_SC

1. create schema DEV;
2. create schema MAIN;
3. create schema USERS_SC;

• Three warehouses, two comparatively larger than the third DEV_WH, MAIN_WH,  ELT_WH

1. create warehouse if not exists DEV_WH warehouse_size=SMALL AUTO_SUSPEND=100 AUTO_RESUME=TRUE initially_suspended=TRUE;
2. create warehouse if not exists MAIN_WH warehouse_size=SMALL AUTO_SUSPEND=100 AUTO_RESUME=TRUE initially_suspended=TRUE;
3. 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.

1. 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:

1. The role is changed to securityadmin as only securityadmin and useradmin have access to create roles and users and grant roles.
2. “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

1. 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.
2. If any exception or failure is encountered while executing the procedure, the functionalities implemented by it are rolled back.