Grant SELECT on all tables from a schema to role?

[former_member297082]

Hello community,

do you know if it's possible to grant SELECT privileges on all tables from a distinct schema (or the whole db) to a role?
It would be nice to have something like a "read only" user for the whole database, just in case you want to look up a data set without accidently editing anything.

All the solutions I found don't match with the possible SQL syntax for HANA and system privileges like catalog_read don't provide the same possibilities.

Thank you.

[pfefferf · Accepted solution]

Hello Nicolai,

you can grant the SELECT object privilege on schema level which allows the user to which the privileges are assigned, to select from all tables within that schema (check the object privilege reference: Object Privileges (Reference) - SAP HANA Security Guide - SAP Library).

Regards,

Florian

[former_member297082 · Accepted solution]

Thank you. I've already tried that by executing GRANT SELECT ON SCHEMA XY TO ROLENAME, but the desired user still cannot select any tables from that schema. In the security tab and the role profile the result is an object privilege with SELECT permissions, so that should be good.
Even when I right-click on the schema and perform a find table request it won't find anything.

When I try to select the tables by using SQL it says table not found (but the table is definitely there).

SAP DBTech JDBC: [259]: invalid table Name: Could not find table/view XYZ in schema XY

[pfefferf · Accepted solution]

The error message you get is not related to an authorization problem. If you do not have the privileges, then you get a corresponding error message.

It seems really that the table is not existing (at least in the schema you try). Can you check by a select on system view TABLES, if your table is available (of course you need the corresponding privileges to be able to do so).

Regards,

Florian

[rindia · Accepted solution]

Hello Nicolai,

First try to give access on schema name(where the table resides) to role:

Example:

GRANT SELECT ON SCHEMA RSALLA TO SELECT_T1;

Then Assign role to user.

Example:

GRANT SELECT_T1 TO USER_T1;

Now the user USER_T1 should be able to do the data preview.

Regards

Raj