How to create a role that can access only selected tables during runtime

Question

I'm running a SAP HANA database in HDI container and created a corresponding HDI Container admin. I would like to be able to grant users (for support purpose) access not to the whole schema, but only to a few selected tables. How to do it? All examples I found online look like this - and grant access only to a whole schema

CREATE ROLE SCHEMA_NAME.ROLE_NAME NO GRANT TO CREATOR; 
GRANT SELECT ON SCHEMA SCHEMA_NAME TO ROLE_NAME;

I know there is an option to use .hdbrole file during deployment, where also object privileges can be written up, but it seems I would have to run deployment each time whenever I need to create a new role. And I would like to create these roles right from the SQL console. Is it possible? And if yes, how to do it?

Answer 1

By running the CREATE ROLE and GRANT commands in the SQL console you create catalog/runtime roles. These cannot be transported to any other container or DB but only live in this very instance of the database.

Now, that is usually not what you want to have when dealing with HDI containers or containerized applications in general. Creating schema objects on the fly commonly leads to operational challenges (who has access to what, where, when, why, how?) and should rather be avoided.

But, sure, you can simply connect to the container with any user that has appropriate permissions and run those commands. That includes the usual GRANT SELECT on <table_name> TO <role>; it doesn't have to be the whole schema.