CODEWITHSUNDEEP
sqlhana
Astrid
Astrid

Reputation: 1828

Grant permission to role if exists in SAP HANA

I have a HANA database deployment set up using Flyway and the HANA JDBC driver that creates and populates a schema. Something I would also like to do as part of the deployment is grant a particular database role read access to that schema. However, in order to avoid migration errors, I'd first like to verify that this role exists, and I can't get this part of the logic to work.

The closest I've come is

DO
BEGIN
    DECLARE I INTEGER;
    SELECT COUNT(*) INTO I
    FROM roles
    WHERE role_name = 'MYROLE';
    IF I > 0
    THEN
        GRANT SELECT ON SCHEMA myschema TO MYROLE;
    END IF;
END;

but this fails with

SQL State  : HY000
Error Code : 7
Message    : SAP DBTech JDBC: [7] (at 140): feature not supported: DDL statements other than CREATE/DROP TABLE is/are not supported in anonymous block: line 9 col 9 (at pos 140)
Location   : db/migration/V1.10__my_script.sql (snip)
Line       : 1
Statement  : DO

I also tried this via trying to create a temporary stored procedure and executing that - same problem with DDL statements not being supported.

The problems:

At this point, I'm not sure if what I'm trying to do is even possible. Pointers would be very much appreciated.

Upvotes: 0

Views: 3438

Answers (1)

Lars Br.
Lars Br.

Reputation: 10388

Your code should work with a few modifications like the following:

DO BEGIN DECLARE I INTEGER;

SELECT COUNT(*) INTO I
FROM roles
WHERE role_name = 'MYROLE';

IF :I > 0 THEN
    exec 'GRANT SELECT ON SCHEMA myschema to MYROLE';
END IF;

END;

To access the I variable value in the IF statement you need to use the : notation. As you mentioned some DDL statements are not directly supported in SQL Script, but you can use the EXEC command to run them as dynamic SQL commands.

Generally speaking, this approach to handle privileges is rather problematic since the outcome of your procedure, that is what privileges exactly are available to MYROLE, is dependent on

  • if there already exists a role with the same name
  • what privileges the security context that runs the procedure is allowed to grant

SAP HANA provides HDI (HANA Deployment Infrastructure) repository object type .hdbrole that allows to bundle privileges into roles and have those deployed fully (or not at all) upon installation time. This approach also allows updating privilege assignments to roles even after the role had been assigned to other roles and users without the need for re-assignment.

Dynamically building roles and assigning privileges makes it much harder to understand when, where and why privileges are assigned to roles/users. That is typically not what you want; instead, you like to have privileges assigned at a well-known place in your application and nowhere else. Therefore the pointer is to actually not use your procedure but to use the HANA tools available for role-definition. All that is explained in a lot more detail in the SAP HANA documentation.

Upvotes: 3

Related Questions