Search code examples
hanahana-sql-scripthana-studio

How to add tables in object privileges for a role

I am looking for a SQL command which will help me add multiple tables in object privileges for a new role in SAP HANA Studio. Currently I am adding tables for a role using following sequence.

  1. In SAP HANA Studio, I create a new role from Security>Roles
  2. Enter Role Name and select schema.
  3. Select Object privileges and select add option.
  4. select Catalog object window is displayed with all tables listed (including tables in other schemas)
Solution

  • The command you are looking for is GRANT. 

    GRANT SELECT, INSERT, DELETE on "MySchema"."MyTab" to MyRole;
GRANT SELECT, INSERT, DELETE on "MySchema"."MyTab2" to MyRole;

    This way, you can bundle sets of privileges into roles and assign those roles to users (or other roles). This is the recommended way to manage privileges in SAP HANA. See the SAP HANA documentation for details on that.

    When developing roles you should consider building them as so-called repository objects. This allows for deploying them to different systems without having to manually execute SQL scripts on each system.

    You may also want to check the SAP HANA Security Guide for further information and examples.

    Generally speaking, you don't want to use SAP HANA Studio or SQL scripts to build your roles due to the lack of deployment/transport infrastructure.