Easy way to provide schema access to a BTP HANA Cloud Standard User

[kallolathome]

Problem

While working with the CAP applications, I have came across a situation that a standard HANA cloud user used in CPI cannot perform direct CRUD operations on the tables via the JDBC driver. The CPI will throw the error: insufficient privileges.  So, I am writing the blogpost for reference purpose.

Solution

Please follow the below mentioned steps:

1. Open the SQL Console of the BTP HANA Cloud by selecting the DBADMIN node.


2. Create the User group:
   

   CREATE USERGROUP USERGROUP_NAME

   
   

   

   
   


3. Creating the User & assigning it to the group: USER_01
   

   CREATE USER USER_01 PASSWORD Password1111 NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP USERGROUP_NAME

   
   


4. Grant the User: USER_01 operator of the group: USERGROUP_NAME
   

   GRANT USERGROUP OPERATOR ON USERGROUP USERGROUP_NAME TO USER_01

   
   

   

   
   


5. If there is already a user present then please ignore the above steps.


6. Now, its time to check the SCHEMA_OWNER.
   
   
   
   1. Get the SCHEMA name. Add the instance by selecting Instance Type: HDI Container.
   
   
   2. Expand & click on the Tables. Select any table & then double click to open. You will get the SCHEMA name.
   
   
   3. 
   
   
   
   


7. Go to the SAP HANA Cockpit.


8. Click on the drop down list & select Security and User Management.


9. Under the User & Role Management tile, select the Role Assignment option.


10. Select Assign roles to a user. Enter the user.


11. Click on the EDIT button & add the roles by providing the SCHEMA name.


12. Select & add the role you want. Then, save it.


13. Click on the added role to check more details.
    
    
    
    1. Object Privilege
    
    
    2. Assigned Users
    
    
    
    


14. Now, the user is having access to the SCHEMA.


15. That's it.

 

Now you can use the same user in the CPI for CRUD operations.

 

If you want to learn more about the privileges then please check this link.

 

If I have missed something, please feel free to add it in the comment section so that, this post can be useful to others.

 

References:

1. SAP HANA Cloud User Management


2. 
   
   
   
   SAP HANA One Administration Guide
   
   
   
   

[martinstenzig]

Just a question. the HDI container usually has a standard "...::access_role". You didn't want to use that?

[kallolathome]

Yes. I have just selected a different role. That's it.

[Willem_Pardaens]

Indeed, the best practice would be to use the standard access_role created by default:

GRANT "MyHDISchemaName::access_role" TO MyCPIUser;

[martinfrick]

Or even using the HDI Container SQL API features... Just my two cents!

Grant a User a Role from the SAP HDI Container's Schema

Grant Access to an SAP HDI Container's Schema