Insufficient Privilege Error during Engine Connection when using Account without Permissions to SYS.DUMMY

[matthew1davis]

Line 305 in dilect.py throws a sqlalchemy.exc.DBAPIError error when I use a HANA Database account credentials to connect to HANA. For context, the HANA Database account does not have access to the DUMMY table in the SYS schema in our enterprise HANA server, but it does have access to other tables and views in the HANA environment. Can we update the sqlalchemy-hana code so that there is not a hard requirement to have access to the DUMMY table in order to connect to a given server and database?

from sqlalchemy import create_engine
engine = create_engine('hana://username:[email protected]:30015')  # where username and password are for a HANA database account
conn = engine.connect().execution_options(stream_results=True)

Full error:

sqlalchemy.exc.DBAPIError: (hdbcli.dbapi.Error) (258, "insufficient privilege: Detailed info for this error can be found with guid 'xxxxx')

[jarus]

Hi @matthew1davis, could you please describe what privileges your database user has? I actually never saw a HANA database user without privileges on DUMMY as it would also block access on so many functions, and typical statements like SELECT CURRENT_TIMESTAMP FROM DUMMY.

[matthew1davis]

@jarus I spoke with our IT organization regarding this and they were able to add my Sys ID in HANA to the SYS.DUMMY table. However, they noted that typically only individual accounts (i.e. the PUBLIC role in HANA) are given access to that table. Here is a screenshot of the HANA database permissions: SELECT * FROM granted_privileges WHERE OBJECT_NAME LIKE '%DUMMY%'

I am raising this issue here because I think there may be other cases in the future where a database account does not have access to DUMMY but has access to whatever table they are actually querying data from. Can we update the _get_default_schema_name() function in the dilect.py to handle such a case?

[kasium]

We need access to the DUMMY table at a point where we don't know any table, like for isolation level selection. I guess it's possible to add a parameter to customize it, but to be honest normally a user should have access to DUMMY.. I'll close this until there is increased demand for it