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

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.

References: