hi,
I am working on my HANA Express Edition (version 2, sps 2) while waiting for access to the HANA server(s) at work.
We plan to programmatically provision users to HANA, and assign privilges/roles when needed using a System type user through JDBC calls.
We would rather not use the "SYSTEM" id to do this provisioning. I created a user (IDM_ACE_DEVELOPER) that I am using to run SQL. I have gradually added the needed security roles/privilges to this user when I encounter the dreaded "insufficient privilege" error. However, I am stuck trying to grant a system privilege. I am trying to execute (JDBC call) this SQL:
"GRANT CATALOG READ TO TESTUSER" where TESTUSER is a newly provisioned user. I am using this link as a reference: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.00/en-US/20f674e1751910148a8b990d33efbdc5.html
The GRANT CATALOG READ works when signed in as the SYSTEM user, but my IDM_ACE_DEVELOPER id lacks the sufficient privilege. I have turned on the tracing, and then looked at the index server log file. I can find where it fails, but it doesn't seem to provide any clues to what I need to add to my IDM_ACE_DEVELOPER id to execute this SQL. Using HANA Studio, I have tried to compare the roles/privileges my SYSTEM user (which works) with my IDM_ACE_DEVELOPER user, but I have tried assigning everything that seemed to make sense, but it still fails (yes I remember to click save each time).
Any ideas would be much appreciated!
Thanks.
Does your IDM_ACE_DEVELOPER user have the Catalog Read System Privilege with Grant Option assigned?
You can easily check this with following SQL:
select * from effective_privileges where user_name = 'IDM_ACE_DEVELOPER' and object_type = 'SYSTEMPRIVILEGE' and privilege = 'CATALOG READ';
It is necessary that the Grant Option is set, otherwise the user is not allowed to grant the privilege to another user. In the result of the above SQL you can see that in column IS_GRANTABLE.
Regards,
Florian
Florian,
Sorry I was a little late getting back to you, but you seem to be exactly correct. I ran the query you suggested, and sure enough, my IDM_ACE_DEVELOPER user was set to fale for IS_GRANTABLE.
Using my SYSTEM user, I modified my IDM_ACE_DEVELOPER user to make everything showing up in SYSTEM Privilege tab to Grantable.
I then ran my JDBC, and did not receive any errors this time, and when I double checked my TESTUSER id, it had CATALOG READ.
Very nice, and I very much appreciate your assistance!