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
Great Work !!! Thanks for sharing the code.
Hi Justin,
This is an absolute gem of code – thanks so much for sharing!
Kind regards.
Rich
Hi Justin,
Appreciate Thanks for the code.
Can you please let me know what should I need to give in parameters while calling the procedure.
Many Advance Thanks.
Ravi
Hello,
Thanks for your script, I will try to use it,
Please let me know how to input the :
I assume it is a excel file, do I need to create it localy in a special path?
Thanks for your time.
Regards
Denis Rossi
Hello, I got it !
Hereafter the step of the process to clarify a little bit for the non expert like me (;-)
CREATE PROCEDURE DROSSI.USER_MAINTAIN_PROC LANGUAGE SQLSCRIPT
AS
BEGIN
I hope it’s help ….
Denis Rossi
Hi,
When I am calling procedure CALL “_SYS_BIC”.”ZUSERS/ZUSER_CREATE I am facing below issue:
SAP DBTech JDBC: [257]: sql syntax error: search table error: “_SYS_BIC”.”ZUSERS/ZUSER_CREATE”: line 51 col 5 (at pos 2327): [257] (range 3) sql syntax error exception: sql syntax error: incorrect syntax near “@”: line 1 col 29 (at pos 29)
Please reply.
Thanks
Kanika Gupta