How to…perform mass user creation / role assignment

A recent request came up at a customer who was migrating to a larger independent HANA sidecar scenario and wanted to move large groups of users from one HANA instance to another. Since there is no mass user creation functionality that I am aware of, we had to create one. This would be helpful for applications services teams who are responsible for creating users and assigning roles in large amounts. This can certainly be enhanced to cover a number of other scenarios, but this works for the requirement at hand and was fast to implement.

Basic workflow

– Populate a table either through file upload or another UI method, that would have the structure shown below

– Create users that do not already exist, taking into account that they may or may not have SAML enabled and also may or may not have a specific validity date.

– Add roles to users that do not already have that role assigned

– Clean out maintenance table

DDL

CREATE COLUMN TABLE "HANA_FOUNDATION"."USER_MAINTAIN" ("USER_NAME" NVARCHAR(12),
     "PASSWORD" NVARCHAR(15),
     "VALID_TO" NVARCHAR(8),
     "SAML_ENABLED" NVARCHAR(1),
     "SAML_PROVIDER" NVARCHAR(40),
     "EXTERN_ID" NVARCHAR(20),
     "ROLE" NVARCHAR(60)) UNLOAD PRIORITY 5 AUTO MERGE

SQL Script code

/********* Begin Procedure Script ************/
--Justin Molenaur 02/12/2015
--Create users, assign roles and enable SAML based on excel file upload
--Check for existing user and role assignment beforehand
i INTEGER;
row_count INTEGER;
loop_current_SQL NVARCHAR(200);
valid_date NVARCHAR(8);
valid_SAML NVARCHAR(1);
BEGIN
--Select unique users to be created that don't already exist
it_user_list = SELECT DISTINCT A."USER_NAME", A."PASSWORD", A."SAML_ENABLED", A."SAML_PROVIDER", A."EXTERN_ID", A."VALID_TO"
FROM "HANA_FOUNDATION"."USER_MAINTAIN" A
LEFT OUTER JOIN "SYS"."USERS" B
ON (A."USER_NAME" = B."USER_NAME")
WHERE B."USER_NAME" IS NULL;
SELECT COUNT("USER_NAME") into row_count FROM :it_user_list; --Get count of users to create
--Loop for Creation of users that don't exist yet
FOR i IN 0 .. :row_count -1 DO
SELECT "VALID_TO" --Check if a validity date is maintained
into valid_date FROM :it_user_list
LIMIT 1 OFFSET :i;
SELECT "SAML_ENABLED" --Check if a validity date is maintained
into valid_SAML FROM :it_user_list
LIMIT 1 OFFSET :i;
IF :valid_date IS NULL AND :valid_SAML = 'Y' THEN --No Validity, SAML
    SELECT 'CREATE USER ' || A."USER_NAME" || ' PASSWORD ' || A."PASSWORD"
    || ' WITH IDENTITY ''' || A."EXTERN_ID" || ''' FOR SAML PROVIDER ' || A."SAML_PROVIDER"
    INTO loop_current_SQL
    FROM :it_user_list A
    LIMIT 1 OFFSET :i;
ELSEIF :valid_date IS NOT NULL and :valid_SAML = 'Y' THEN --Validity, SAML
    SELECT 'CREATE USER ' || A."USER_NAME" || ' PASSWORD ' || A."PASSWORD"
    || ' WITH IDENTITY ''' || A."EXTERN_ID" || ''' FOR SAML PROVIDER ' || A."SAML_PROVIDER"
    || ' VALID UNTIL ''' || A."VALID_TO" || 235900 || ''''
    INTO loop_current_SQL
    FROM :it_user_list A
    LIMIT 1 OFFSET :i;
ELSEIF :valid_date IS NOT NULL and :valid_SAML = 'N' THEN --Validity, No SAML
    SELECT 'CREATE USER ' || A."USER_NAME" || ' PASSWORD ' || A."PASSWORD"
    || ' VALID UNTIL ''' || A."VALID_TO" || 235900 || ''''
    INTO loop_current_SQL
    FROM :it_user_list A
    LIMIT 1 OFFSET :i;
ELSE --No Validity, No SAML
    SELECT 'CREATE USER ' || A."USER_NAME" || ' PASSWORD ' || A."PASSWORD"
    INTO loop_current_SQL
    FROM :it_user_list A
    LIMIT 1 OFFSET :i;
END IF;
    EXEC(:loop_current_SQL);
END FOR;
--Select distinct role assignments needed, checking for already existing role assignments
it_role_list = SELECT DISTINCT A."USER_NAME", A."ROLE"
FROM "HANA_FOUNDATION"."USER_MAINTAIN" A
LEFT OUTER JOIN "SYS"."GRANTED_ROLES" B
ON (A."USER_NAME" = B."GRANTEE" AND A."ROLE" = B."ROLE_NAME")
WHERE B."GRANTEE" IS NULL;
--Get count of roles to assign
SELECT COUNT("USER_NAME") into row_count FROM :it_role_list ;
--Loop for assignment of roles
FOR i IN 0 .. :row_count -1 DO
    SELECT 'GRANT "' || A."ROLE" || '" TO ' || A."USER_NAME"
    INTO loop_current_SQL
    FROM :it_role_list A
    LIMIT 1 OFFSET :i;
    EXEC(:loop_current_SQL);
END FOR;
DELETE FROM "HANA_FOUNDATION"."USER_MAINTAIN"; --Clear out maintenance table when complete
END;
/********* End Procedure Script ************/

There you go, simple as that. Now get out there and create users in a massive way!

Happy HANA,

Justin