How to grant repository roles via SQL statement in HANA?

[david_fernndez2]

Hi at all,

Lately I'm having a lot of work adding users to our HANA systems and adding one by one it's an inefficient and boring job to do, so I've tried to find how to create users through SQL.

We are using SAML authentication so this SQL it's working fine (I can see the user has been created with SAML check marked and with the correct identity inside).

CREATE USER ZZTEST WITH IDENTITY 'TW1~500/ZZTEST' FOR SAML PROVIDER TN1IB1;

Ok, after that I want to add roles to user. If I want to add a Catalog Role I use the next SQL and it's working fine.

GRANT MODELING TO ZZTEST;

But we are using repository roles so I want to execute an SQL query to grant the repo roles.

Actually If I'm on Hana Studio and I click on "+" to Add a Role I can see our Repo roles mixed with catalog roles, for example "Roles.Rol_Usuari_BI::Z_Usuari_BI" where Roles it's a package, Rol_Usuari_BI it's a subpackage and Z_Usuari_BI it's the repo role.

I've tried to execute the next SQL query but it didn't work:

GRANT Roles.Rol_Usuari_BI::Z_Usuari_BI TO ZZTEST;

It returns:

Could not execute 'GRANT Roles.Rol_Usuari_BI::Z_Usuari_BI TO ZZTEST'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "::Z_Usuari_BI": line 1 col 26 (at pos 26)

Can you help me with this issue? Is it possible to assign Repo roles via SQL?

I couldn't find any information anywhere.

Thanks in advance.

[dvankempen · Accepted solution]

Hi David,

See Stored Procedures Used to Grant/Revoke Privileges on Activated Repository Objects in the Security Guide

--CALL GRANT_ACTIVATED_ROLE ('<role>','<user>'/'<role>')
CALL GRANT_ACTIVATED_ROLE ('Roles.Rol_Usuari_BI::Z_Usuari_BI','ZZTEST')

Solves your issue?

Regards,

Denys van Kempen / SAP HANA Academy

[david_fernndez2 · Accepted solution]

Hi Denys,

Thank you so much, that solves my issue, I was doing it wrong.

I couldn't find on Security Guide that information so thanks for that.

Regards