Modifying the default access_role in HDI containers

[paulpfeiffer]

Overview

Every HDI container in SAP HANA XSA comes with a predefined access_role that (you might have guessed it) grants access to the container's schema. It only grants limited access, though -  and sometimes you may need more. This post is about modifyingthe privileges included in this role.

Before we get into the details, let's first take a look at the obvious question.

Why?

Why would you want to do this?

The access_role is useful because it's assigned by default to many of the standard users that are created with the HDI container. That includes, but isn't limited to, the #OO-User, the #DI-User and the #RT-User(s).

As stated before, the default access_role only contains limited privileges. The following privileges are contained for the schema:

• CREATE TEMPORARY TABLE


• DELETE


• EXECUTE


• INSERT


• SELECT


• SELECT CDS METADATA


• UPDATE

That set of privileges is a great baseline and probably works well for most use cases. But I ran into an issue where I needed a procedure in a Flowgraph to create a new table. Internally, this is done by the #RT-User and not supported with the standard role.

You could now create a new role or assign the missing privilege to the user manually. That is only a workaround, though and doesn't work for new deployments. So a much better way to solve is is adapting the role.

How?

In short: The solution is described in the following SAP Help entry: https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.06/en-US/9235c9dd8dbf410f915ffe3052...

However, as the entry doesn't contain an example, I thought it would be helpful to provide one.

To solve the issue, a role definition needs to be created in the path src/defaults/default_access_role.hdbrole. The role needs to be named "default_access_role" and has to be included in the deployment.

It's important to note that the existance of this new role will remove all existing privileges on the schema from the access_role. So, if you prefer to retain those privileges, they need to be added to the new role.

Because I want to keep all privileges and only add "CREATE ANY", the resulting role definition looks like this:

{

  "role" : {

    "name" : "default_access_role",

    "schema_privileges": [

      {

        "privileges": [

          "SELECT",

          "INSERT",

          "UPDATE",

          "DELETE",

          "EXECUTE",

          "CREATE TEMPORARY TABLE",

          "SELECT CDS METADATA",

          "ALTER",

          "CREATE ANY"

        ]

      }

    ]

  }

}

Make sure to also put a .hdinamespace file into the src/ folder that redefines the namespace. Otherwise the role name will cause errors.

{

    "name":    "",

    "subfolder": "ignore"

}

With that addition, the build and deployment should work fine.

Result

After those modifications and a successful deployment, you should see that the access_role has been changed.

The direct schema privileges are removed - but the role now contains the "default_access_role" with all privileges described in the role definition.

As a result, all technical users like #OO and #DI now get the modified role, in my case including the "CREATE ANY" privilege.

 

It took me some time to figure out the best way to do this. So I hope this helps someone.

[dvankempen]

Hi Paul,

Left a comment in the documentation to consider adding an example.

Thanks for your contribution.

[ravindrapawar]

Hi Paul,

I want to assign the privileges mentioned in default_access_role to #_RT user. With above setting the privileges are assigned to #OO user only.

Is there any way to achieve it?

Thanks,

Ravindra

[paulpfeiffer]

Hi Ravindra,

I didn’t mention the _RT user in the article, but I tested it and the _RT user also gets the access_role by default. So, if you follow the directions, the new permissions should apply to that user too, because they have the default_access_role in their access_role.

Let me know if it works!

[Bruhn]

Great stuff - I face an issue with the hdbjobscheduler which I wanted to set as disabled and then use the alter scheduled job enable directly from SQL. After applying this change it works from the SQL of the container itself..
However I want to grant the ALTER access further to an admin role (a runtime role) - I looked at note 3125732 - "Cannot assign missing ALTER object privilege to refresh virtual table for HDI container" which describes the situation I am in currently - I am trying to create a role with alter access on the schema - has anyone made a similar concept work? and if yes how do you manage schema references changing (i.e. from MY_HDI_1 -> MY_HDI during deployment)

/MiB

[Ravi86]

Very helpful blog Paul, thanks.