cannot grant rights to HDI-generated user on HANA 2

[former_member499127]

hi,

i am trying to access a classic schema from within an HDI container as explained in the Documentation. I successfully did that on another server on HANA 1 SP12. Trying to do that on HANA 2 Express Edition i get an error that the user generated by HDI cannot be seen by the "normal" user that owns the schema i want to access:

i created a user defined service, let's call it "my-grantor" that uses the username "SCHEMA" to access the schema "SCHEMA". i can actually see that this works well in the database explorer by connecting to this service. now in the HDI container "myHDI" the mta.yaml and .grants-file are setup, two roles "external_access" and "external_access_g" are available.

i build and i get (everything in this message replaced with names explained above):

Error: Error executing: GRANT "<SCHEMA>::external_access_g" to "<myHDI>_HDI_DB_1#00";
(nested message: invalid user name: <myHDI>_HDI_DB_1#00: line 1 col 35 (at pos 34))
grantor service: <my-grantor>, granting user: <SCHEMA>

on the HANA 1 server where everything worked i would also be able to see the user <myHDI>_HDI .... when logged in as user SYSTEM. here i can't. What can i do?

[thomas_jung · Accepted solution]

My guess is that your user provided service points to a different tenant (via the SQL Port) than the tenant being used by the container. It worked in HANA 1.0 because you were in a single tenant system. Now in HANA 2.0 SPS 01 and higher you must be MDC even if all you have is a single tenant and the SystemDB.

[former_member499127 · Accepted solution]

hi Thomas,

thanks for your reply. I was wondering if that was my problem. but if i understand correctly, it actually can't be: i can connect to the user-defined service i created with the database explorer in the web ui and see my data there. so the user-defined service can access the DB. what seems to be failing is the HDI container i am in to access the user-defined service?

[thomas_jung · Accepted solution]

Just because you can see data via the User Provided Service doesn't mean that its pointing to the same tenant as your space/container. You could well be seeing the SYSTEMDB via one connection and the first tenant in another.

[former_member499127 · Accepted solution]

thanks again - unfortunately i did not get a notification that you had answered. i looked into this now. trying a

select * from "SYS"."M_DATABASES"

shows only HXE. using that as tenant my user can log in and see things, so it seems to be the tenant used. i then dropped my initial service, and created a new one:

xs cups <SCHEMA>-table-grantor -p "{\"host\":\"hxehost\",\"port\":\"39015\",\"dbname\":\"HXE\",\"user\":\"<USER>\",\"password\":\"<PASSWORD>\",\"driver\":\"com.sap.db.jdbc.Driver\",\"tags\":[\"hana\"] , \"schema\" : \"<SCHEMA>\" }"

with the service started i tried to build my db module again, but still get a

Error executing: GRANT "<SCHEMA>::external_access_g" to <APPNAME>_HDI_DB_1#00";

(nested message: invalid user name: OPTNET_HDI_DB_1#00: line ...

grantor service: <SCHEMA>-table-grantor, granting user: <USER>

meanwhile i realized that the database had not been enabled for XSA, i did that. also mapped the space to this Database. but still the same error

[thomas_jung · Accepted solution]

>also mapped the space to this Database. but still the same error

Are you sure you mapped it to the tenant? Did you delete the HDB service after you did the mapping? The existing service instances could still be pointing to the wrong tenant.

[former_member499127 · Accepted solution]

i just deleted the grantor-service again and created it again.

i did the mapping from xs advanced administration->service broker configuration. it shows:

HANAExpress / development

• HXE (default)

i checked, that i am working in the space development in the web ide.

i enabled the database for XSA in the logical database setup of th XS advanced administration. I checked with the di-space-enablement-ui and it also shows the space development to be enabled.

do i need to somehow clear any build cache of hdi?

[thomas_jung · Accepted solution]

No not the grantor service, but delete your HDI service/container.

[former_member499127 · Accepted solution]

that finally did the trick. thank you

- the database needs to be enabled.

- if the database had not been enabled when originally building the service needs to be deleted

thank you!

(and maybe needing to enable the database should be prominently put at the top of the "Getting Started"-chapter of the Manual 🙂

[former_member257453 · Accepted solution]

Hi Thomas,

Could you tell me how to delete the HDB service? We are having the same issue and it seems like we might be pointing to the wrong tenant as well? Is there a way to check this?

Thanks,

Kartik

[former_member257453 · Accepted solution]

Could you explain how to delete the HDI service? We are having what seems to be a similar issue to yours?

[thomas_jung · Accepted solution]

You can delete the service with the command xs delete-service

[former_member257453 · Accepted solution]

So we tried that just now but still getting the error. Our error is the same but it's for a virtual table in our case:

ERR Error: Error executing: GRANT "CREATE VIRTUAL TABLE","DROP" ON REMOTE SOURCE "<source>" TO "<user>#OO"; ERR (nested message: invalid user name: <user>#OO: line 1 col 63 (at pos 62)) ERR grantor service: "<servicename>", type: "sql", user: "<user>"

[thomas_jung · Accepted solution]

That error means that your grantor service is pointing to a different tenant than your project space/container.

[former_member257453 · Accepted solution]

That fixed it! Thanks Thomas!

[thiagofcs]

I had the same situartion here.

I have an S4P tenant with the SAPHANADB schema.

Also I have a PAH tenant which is used by the container and where I set up a cross-database access so I can see the SAPHANADB schema from the PAH tenant. So far so good.

Problem is if I configure the user provided service with the S4P SQL port, I get the same issue Mike Beck described. But if I configure the user provided service with the PAH port, I can`t find the SAPHANADB tables which comes from the S4P tenant.

Any idea on how to deal with this scenario?

Best Regards,

Thiago

[ayoub_azzouzi]

Hello, I am currently trying to do the same, have you found the solution please?

Ayoub