title: Grant permission to role if exists in SAP HANA
body: <p>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.</p>
  
  <p>The closest I've come is</p>
  
  <pre><code>DO
  BEGIN
      DECLARE I INTEGER;
      SELECT COUNT(*) INTO I
      FROM roles
      WHERE role_name = 'MYROLE';
      IF I &gt; 0
      THEN
          GRANT SELECT ON SCHEMA myschema TO MYROLE;
      END IF;
  END;
  </code></pre>
  
  <p>but this fails with</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I also tried this via trying to create a temporary stored procedure and executing that - same problem with DDL statements not being supported.</p>
  
  <p>The problems:</p>
  
  <ul>
  <li>I need to do an IF-THEN-ELSE based on the result of a select query </li>
  <li>HANA doesn't seem to support nesting a SELECT statement inside an IF clause, so I need to save the result in a variable and use that instead</li>
  <li>Declaring variables is only supported inside blocks, such as anonymous blocks or the bodies of stored procedures</li>
  <li>Blocks also forbid executing most DDL statements - <code>GRANT</code> being one of them.</li>
  </ul>
  
  <p>At this point, I'm not sure if what I'm trying to do is even possible. Pointers would be very much appreciated.</p>