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

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Ravikumar Shar

    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

    (0) 
  2. Denis Rossi

    Hello,

    Thanks for your script, I will try to use it,

    Please let me know how to input the :

    :it_user_list

    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

    (0) 
  3. Denis Rossi

    Hello, I got it !

    Hereafter the step of the process to clarify a little bit for the non expert like me (;-)

    1. Create Excel file:
    2. Creation of the Table into the Hana DB (for example into your own schema)
    3. Deletion of all old rows into the Table if need.
    4. Load the Excel File into the Table :
    5. Create the proc├ędure : (Add the lines

    CREATE PROCEDURE DROSSI.USER_MAINTAIN_PROC LANGUAGE SQLSCRIPT
    AS
    BEGIN

    1. Call Proc├ędure using the command: CALL DROSSI.USER_MAINTAIN_PROC;
    2. The process create the users and the roles using the content of the table into Hana DB.

    I hope it’s help ….

    Denis Rossi

     

     

    (0) 
  4. DELHI BASIS Support

    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

    (0) 

Leave a Reply