How can we specify "WITH GRANT OPTION" in the hdbrole file?

[Former Member]

Hi

I would like to specify the WITH GRANT OPTION for the object/schema privileges in the HDBROLE file.

For example, I have following:

role mypack.sap.db::MY_ROLE
{
// schema privileges
catalog schema "MY_DB": CREATE ANY, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE, EXECUTE, INDEX, TRIGGER, DEBUG;

}

Here I am specifying several permissions in the schema privilege. But how do I specify "WITH GRANT OPTION", or in other words how can I specify that any body with this role should be able to grant these permissions?

[Former Member · Accepted solution]

When a role is created by the file hdbrole the owner of the role is _SYS_REPO. Then any user having the system privilege ROLE_ADMIN should be able to grant the generated role to any other user.

Now to answer your question, it is not possible to embed the grant option into hdbrole. The reason for this is stated in HANA Developer Guide on pages 530 and 531. Please refer to below excerpt -

WITH ADMIN OPTION and WITH GRANT OPTION
When you create a role on the basis of SQL statements (that is, as a runtime object), you can grant privileges with 
the additional parameters WITH ADMIN OPTION or WITH GRANT OPTION. This allows a user who is granted the 
role to grant the privileges contained within the role to other users and roles. However, if you are implementing 
your authorization concept with privileges encapsulated within roles created in design time, then you do not want 
users to grant privileges using SQL statements. Therefore, it is not possible to pass the parameters WITH ADMIN 
OPTION or WITH GRANT OPTION with privileges when you model roles as repository objects.
Similarly, when you grant an activated role to a user, it is not possible to allow the user to grant the role further 
(WITH ADMIN OPTION is not available).

[Former Member · Accepted solution]

Thanks a lot Sharan for the reply. That was very useful.