Please consider, everything written below are mainly my personal experiences and opinions and I may be wrong or inaccurate. Please leave a comment if you find a mistake. Thank you!

SAP® HANA Authorization (HANA 1.x)

SAP® HANA Authorization, applied to the user, is covered by the following areas:

• Granted Roles
• System Privileges
• Object Privileges
• Analytic Privileges
• Package Privileges
• Application Privileges
• Privileges on User

The same is shown in the HANA Studio:

Where the privilege areas can be explained as follows:

• Granted Roles
  Roles granted to the user.
  
  
• System Privileges
  System privileges control general system activities. They are mainly used for administrative purposes, such as creating schemas, creating and changing users and roles, performing data backups, managing licenses, and so on.
  
  
• Object Privileges
  Object privileges are used to allow access to and modification of database objects, such as tables and views. Depending on the object type, different actions can be authorized (for example, SELECT, CREATE ANY, ALTER, DROP, and so on).
  
  
• Analytic Privileges
  Analytic privileges are used to allow read access to data in SAP HANA information models (that is, analytic views, attribute views, and calculation views) depending on certain values or combinations of values. Analytic privileges are evaluated during query processing.
  In a multiple-container system, analytic privileges granted to users in a particular database authorize access to information models in that database only.
  
  
• Package Privileges
  Package privileges are used to allow access to and the ability to work in packages in the repository of the SAP HANA database.
  Packages contain design-time versions of various objects, such as analytic views, attribute views, calculation views, and analytic privileges.
  In a multiple-container system, package privileges granted to users in a particular database authorize access to and the ability to work in packages in the repository of that database only.
  
  
• Privileges on User
  I’m not familiar with this Privileg.

 

To get Privileges assigned to a specific user, please use the following code:

SELECT * FROM "PUBLIC"."EFFECTIVE_PRIVILEGES"
WHERE USER_NAME = '<SAPID>'
AND object_name LIKE '<analaytical object name>'
ORDER BY Object_name;

 

Data access Privileges

When we have an idea about the authorization, we can check the ways how to secure read-only data access to information models (Attribute views, Analytic views, Calculation views). In that case, the authorization can be split into three main types:

1. 1. No authorization(?)
   2. Classical Analytic Privileges
   3. SQL Analytic Privileges

The above are possible choices when checking Properties of a Calculation View:

 

Classical Analytic Privileges

In the case of using the Classical Analytic Privileges, data is filtered based on the Privileges applied to the user, which is accessing the information model.

SQL Analytic Privileges

The SQL Analytic Privileges can be implemented in three different ways:

1. Attributes (static privileges)
2. SQL Editor (reading privileges from other sources by SQL code)
3. Dynamic (reading privileges from the procedure)

The first option gives us “only” static filtering on the data. As written in the official documentation:

“Static SQL analytic privileges or fixed analytic privileges allows you to combine one or multiple filter conditions on the same attribute or different attributes using the logical AND or OR operators.

Static SQL analytic privileges conditions typically have the following structure, <attribute> <operator> <scalar_operands_or_subquery>. For example, “country IN (scalar_operands_or_subquery) AND product = (scalar_operands_or_subquery).” The supported operator types are IN, LIKE, BETWEEN, <=, >=, <, >.”

SQL Editor

The advantage of this implementation is that the SQL output is not limited by the number of characters.

However, the disadvantage is that the filter is basically based on “not complex” SQL. Most likely just based on data in another table.

Please check my How-To post about this topic: How-To implement SQL Editor SQL Analytic Privileges in SAP® HANA

Or for more details, please see the official documentation in the link(s) below.

Dynamic

The advantage of this implementation is the possible complexity written in a Procedure, where you can implement any desired business logic.

The disadvantage is, that the output of the Procedure can have a maximum of 5000 characters. This can be potentially a big disadvantage in the case of complex filter criteria with long column names and actually caused significant problems in one of my projects.

Please check my How-To post about this topic: How-To implement Dynamic SQL Analytic Privileges in SAP® HANA

Or for more details, please see the official documentation in the link(s) below.

Performance perspective

By implementing the SQL Analytic Privileges we have to consider the performance. The time needed to show the data is split into two main parts:

1. The runtime of the information model (Attribute views, Analytic views, Calculation views)
2. Filter generation and implementation on the received data

In general, the HANA DB is very fats and based on my experiences it is much faster to implement the filter criteria in the form of WHERE clause instead of for example joins. This was one of my personal surprises in the case of using column-based tables in the in-memory database.

However, the filter generation must be fast because this is the extra time regardless of the information model performance.

 

HANA Authorization generated from BW

In some cases, the authorization on HANA is generated from BW authorization relevant objects. In that case, the relevant transactions are:

• RS2HANA_ADMIN
• RS2HANA_GEN (to re-generate the roles)

The relevant table where the current settings are shown is:

• RS2HANA_AUTH_STR

We have been facing issues with the generation of the roles. We have to use a specific list of the objects for which the roles should be generated. We cannot use the checkbox: All Enabled BW Objects!

The procedure, in that case, is the following:

1. The security team has to prepare roles for the users;
2. Transaction: RS2HANA_GEN, has to be initiated;
3. The output can be checked in the: RS2HANA_AUTH_STR, table.

 

How-To check the Analytic authorization

To check details about the analytic authorization the following SQL can be used:

SELECT * from "PUBLIC"."EFFECTIVE_STRUCTURED_PRIVILEGES" where ROOT_SCHEMA_NAME = '<schema>' AND ROOT_OBJECT_NAME = '<relevant object/model>' AND USER_NAME = '<user's SAP ID>'

For example:

• model = local.pmynarik/MY_MODEL
• user = 12345678

SELECT * from "PUBLIC"."EFFECTIVE_STRUCTURED_PRIVILEGES" where ROOT_SCHEMA_NAME = '_SYS_BIC' AND ROOT_OBJECT_NAME = 'local.pmynarik/MY_MODEL' AND USER_NAME = '12345678'

The output contains also the specific definition/logic of the analytic authorization!

 

How-To analyze the authorization issue

• Start the authorization trace (see SQL code below);
• Reproduce the issue you have been facing;
• In HANA Studio, open the administration console, and go to the Diagnosis Files tab;
• Open the latest index server trace and search for SAMLAuthenticator.cpp (in case of SAML auth. issue);
• You should find a detailed error message that indicates the root cause, like the following example:
  [code gutter=”false”][3957]{-1}[-1/-1] 2018-09-11 21:40:23.815797 d Authentication
  SAMLAuthenticator.cpp(00091) : Element ‘{urn:oasis:names:tc:SAML:2.0:assertion}Assertion’,
  attribute ‘ID’: ‘123123123123123’ is not a valid value of the atomic type ‘xs:ID’.[/code][code gutter=”false”][3957]{-1}[-1/-1] 2018-09-11 21:40:23.815914 i Authentication SAMLAuthenticator.cpp(00403) :
  No valid SAML Assertion or SAML Protocol detected[/code]
• Once the troubleshooting is complete, stop the authentication trace (see SQL code below).

Start the authorization trace

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM')
SET ('trace', 'authentication') = 'debug' WITH reconfigure;

Stop the authorization trace

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM')
UNSET ('trace', 'authentication');

Assign role to user

GRANT "<role>" TO "<user>"

Grant HANA model to user

GRANT SELECT ON "_SYS_BIC"."<hana_model>" TO "<user>";

 

In case of an error where is written: Detailed info for this error can be found with guid …, the following procedure will show the error details:

call sys.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS('<guid>', ?);

 

To revoke the authorization from a specific user use:

revoke select on schema "<schema>" from "<user>"

In case of additional authorization issue try to grand select on schema to the _SYS_REPO user:

GRANT SELECT ON SCHEMA "schema" TO _SYS_REPO WITH GRANT OPTION;

 

 

Resources

• How To: SAP HANA Authorization Tracing
• Setting Up Roles and Privileges
• SAP HANA Developer Guide
• Master SAP HANA Authorization without Breaking a Sweat!
• Examples: Securing Views Using SQL-Based Analytic Privileges