HANA Security: Creating End Users with minimal rights

[vivekbhoj]

Hi Everyone,

In this document, I am going to explain how we can create an End User for reporting purposes with minimal rights.

This document is based on HANA Rev 82.

Lets say we have created an Analytic View AN_TEST as shown below and we want the end user to access this View using  reporting tools like Analysis Office for Excel/Tableau or using Web.

First, we want the User to access the View using Web.

Below is a screenshot of how the View is displayed in Web from a Developer's ID.

Now we will create a new user in HANA and assign him a role so that he can access the View via web.

• The first thing we need to do is Create a Role for our end user. We can create Catalog Roles and Repository Roles. The difference between the two is listed below:

Catalog Role	Repository Role
are runtime objects	are design-time objects
Not transportable and not versioned	Transportable and versioned
Created in runtime(directly in Production System)	Created in Development System, transported and activated in Production System
Grantor must have a privilege to include it in a role	Grantor does not need privileges included in role
Creator can always grant/revoke role	Any user with EXECUTE privilege on built-in GRANT/REVOKE procedures can grant/revoke roles
Easy to create using SQL or Graphical interface	Needs to create a .hdbrole file and needs to code few lines to create a role

As per the SAP HANA Security Guide, it is recommended to create roles as repository roles because

1. They can be transported between systems
2. They are not directly associated with a database user(All design-time objects are owned by user _SYS_REPO)

Let's start by creating a role for our end-user.

For this purpose, we will create a Project in HANA Development Perspective.

Now let's say we also want to apply restriction on our End User - user should be able to see data only related to Sales Region PUNJAB.

For this purpose, we will create an Analytic Privilege. For this, R-Click on Project -> Select New -> Other -> Select Analytic Privilege and name it AP_AN_TEST and then add restriction as shown below:

Now we have our Analytic View and Analytic Privilege ready. So now we can create a role for our end user.

To create a Role, R-Click on Project -> Select New -> Other -> Select Role and name it AN_TEST_ROLE and provide it access to Column View of Analytic View present in _SYS_BIC schema and also add analytic Privilege that we created in the step above.

On activating this design-time role, it becomes a runtime role that can be granted to any user.

Now our role is ready.

• The second  thing I need to do is Create a User. In SPS08, we can create a Normal Database User and a Restricted User. The difference between the two is listed below:

Normal Database User	Restricted Database User
PUBLIC role is granted implicitly	PUBLIC role is not granted
Can create objects in its own schema and can connect to HANA Database using JDBC and ODBC connections or HTTP/HTTPS interface	Can not create objects in its own schema and can connect to HANA Database only through HTTP/HTTPS interface and cannot view any data in the database as they are not even granted PUBLIC role

As we don't want our end user to create anything in Production Environment, so we will create a Restricted User.

To create a restricted User, expand Security Folder -> R-Click on Users and Select New User-> Enter user name as TEST and check the restricted User Check box.

By default no roles are granted to the end user. To grant the AN_TEST_ROLE that we created above, we will run the below SQL and call GRANT_ACTIVATED_ROLE procedure.

Now our user is created and he is able to access the View via Web as shown below

We have applied restriction that our user should be able to view only PUNJAB region data so the user is able to see only PUNJAB sales Region data.

Now, lets say the user wants to access this View using Analysis Office for Excel. When the user will try to log into HANA, using Analysis Office for Excel,

he will get the below error:

The reason being, our User is a restricted User and is not allowed to log in using ODBC access.

To resolve this error, we need to grant RESTRICTED_USER_ODBC_ACCESS role to our User and we also need to provide SELECT privilege on _SYS_BI schema(or we can provide SELECT access on few objects like BIMC* objects in _SYS_BI schema) so that the user can access metadata otherwise the user will get the below error:

As RESTRICTED_USER_ODBC_ACCESS  is a Catalog Role, so any user which has ROLE ADMIN privilege and has RESTRICTED_USER_ODBC_ACCESS role with GRANT option can grant this role to our User TEST.

But as per the document How to Define Standard Roles for SAP HANA Systems, we should not grant ROLE ADMIN privilege to any user.

But as we know that _SYS_REPO user has ROLE ADMIN privilege, so we can grant RESTRICTED_USER_ODBC_ACCESS role to _SYS_REPO user with GRANT option.

After that we will create a new role where we will extend RESTRICTED_USER_ODBC_ACCESS role and AN_TEST_ROLE role that we created earlier and will also provide SELECT access to _SYS_BI schema.

Once the above role is activated, we can see that it contains both RESTRICTED_USER_ODBC_ACCESS role and Security.roles::AN_TEST_ROLE roles.

Now we will revoke AN_TEST_ROLE and grant newly created AN_TEST_OD_ROLE role to the TEST user.

Now when we log into Analysis Office for Excel using user TEST, we can select the Analytic View for reporting

Then we can drag and drop fields to check the report as shown below:

Keep the following things in mind when creating a End User.

1. If you are running HANA SPS08 or higher, I would recommend you to create End Users as Restricted Users if you don't want them to create content in Production System.
2. Create repository roles as they can be versioned and transported
3. Never Grant SELECT access on the Tables to end users
4. Never Grant SELECT access on _SYS_BIC schema to end users
5. The design-time version of a role in the repository and its activated runtime version should always contain the same privileges
6. It is not possible to drop the runtime version of a role created in the repository using the SQL statement DROP ROLE or in the SAP HANA studio. To drop a repository role, you must delete it in the repository and activate the change. The activation process deletes the runtime version of the role

These are some of the basic things that I have mentioned here and of course there are lot of other things related to User Administration that need to be checked.

Please refer the below documents to know more about HANA Security:

SAP HANA Security Guide - SAP Library

How to Define Standard Roles for SAP HANA Systems

SAP HANA Administration. of Richard Bremer, Lars Breddemann - by SAP PRESS

Note: From HANA SPS09, we will be able to create Repository roles via GUI available through HANA Web IDE.

Regards,

Vivek