User Creation: Create Store Procedure in common or shared schema to access others

CREATE OR REPLACE PROCEDURE "CREATE_USER"("P_EMAIL_ADDRESS" VARCHAR(16777216), "P_LAN_ID" VARCHAR(16777216), "P_DEFAULT_ROLE" VARCHAR(16777216), "P_DEFAULT_WAREHOUSE" VARCHAR(16777216), "P_DEFAULT_NAMESPACE" VARCHAR(16777216), "P_COMMENT" VARCHAR(16777216)) RETURNS VARCHAR(16777216) LANGUAGE JAVASCRIPT EXECUTE AS OWNER AS '';

For user creation Purpose we will use RL_DBA_ROLE roles, warehouse(can be any) and database will be DBA1. For this purpose we have to call the store procedure “create_user”  with some parameters as below:

USE ROLE RL_DBA_ROLE;
USE WAREHOUSE WH_DBA_SM_XS;
USE DBA1.DBUSERS;

call dba1.dbusers.create_user('xxxxx.yyyyy@dbadeeds.com' – Email ID of requested for user ,abcdefgh -- LanID ,'RL_DEVELOPER' – Default Role ,'WH_DBA_SM_XS' – Default warehouse , 'DEV' – Default database, 'DB Access request: Created by: dbadeeds);

Check the roles assigned to any user:                                                                               

show grants to user <username/ lanid>;