Is there a way to automate SAP HANA Authorizations?

[joseph_gonzales]

Hi SAP HANA Friends:

Is there a way to automate the creation of SAP HANA Authorizations?

I'm looking for a way to automate the creations of users, Roles and Analytic Privileges.

Has any discovered any scripts that could assist in this development effort?

Regards,

Joe Gonzales

856 912 1136

[thomas_jung · Accepted solution]

Its pretty easy to write a SQLScript procedure to generate users. You could also generate Roles as well, but both Roles and Analytic Privileges should really be created in the Repository and with direct SQL. I would think that you might build the a few roles manually but a large number of users.  Here is an example SQLScript Procedure we use for mass generation of users for workshops.  Perhaps it will give you some ideas to built upon.

CREATE PROCEDURE _SYS_BIC.CREATE_USERS(IN IM_PREFIX VARCHAR(30), IN IM_PASSWORD VARCHAR(30), IN IM_NUMBER INTEGER)
          LANGUAGE SQLSCRIPT
          SQL SECURITY DEFINER AS lv_user varchar(30) := null;
lv_counter integer := 0; 
 /********* Begin Procedure Script ************/ 
 BEGIN 
  WHILE :lv_counter < :im_number DO 
   lv_counter := :lv_counter + 1;
   lv_user := :im_prefix || LPAD(:lv_counter, 2, '0'); 
   EXEC 'CREATE USER ' || :lv_user || ' PASSWORD ' || :im_password  || ' SET PARAMETER CLIENT = ''001'''; 
   EXEC 'ALTER USER ' || :lv_user || ' DISABLE PASSWORD LIFETIME';
   call "GRANT_ACTIVATED_ROLE"('workshop.admin.roles::workshop_user', :lv_user);
   call "GRANT_ACTIVATED_ROLE"('sap.hana.democontent.epm.data::model_access', :lv_user);   
   call "GRANT_ACTIVATED_ROLE"('sap.hana.uis.db::SITE_DESIGNER', :lv_user);   
   call "GRANT_ACTIVATED_ROLE"('sap.hana.uis.db::SITE_USER', :lv_user);   
   EXEC 'GRANT REPO.EXPORT to ' || :lv_user;  
   EXEC 'GRANT REPO.MAINTAIN_DELIVERY_UNITS to ' || :lv_user;    
  END WHILE;
 END; 
 /********* End Procedure Script ************/

[rindia · Accepted solution]

Hi Thomas,

That was a nice procedure.

But as per my understanding Roles created as above will come under run-time objects and there will re-work especially when moving the objects from Dev to Test and then to Prod environment.

Please let me know if I am wrong.

Thanks and Regards

Raj Kumar

[thomas_jung · Accepted solution]

>But as per my understanding Roles created as above will come under run-time objects and there will re-work especially when moving the objects from Dev to Test and then to Prod environment.

What do you mean by that?  I suggested that roles should be created in the repository via the .hdbrole object. Those are design time objects which generate the run time role upon activation or Delivery Unit import.  These should be created in your development system and then transported to test and production via Delivery Unit Import.  Why would you need to rework them when moving them to different system?

[rindia · Accepted solution]

Hi Thomas,

I had a wrong perception that roles modeled on the basis of SQL statements are run time objects instead of design time objects.

Now I am clear and thanks for your explanation.

Regards

Raj Kumar

[joseph_gonzales · Accepted solution]

Hi Thomas:

I think Raj is thinking about this line on page 35 of the SAP HANA Security Guide:

"An additional disadvantage of creating analytic privileges using SQL is that these

analytic privileges are not in the SAP HANA repository and they cannot be transported between different systems."

Document Version: 1.1 - 2012-12-21

Regards,

Joe Gonzales

[thomas_jung · Accepted solution]

Yes and I said something similar in my message: " but both Roles and Analytic Privileges should really be created in the Repository".  I guess that is where my confusion comes from.