HANA Authorization Fundamental

1. Introduction to HANA and Authorization

SAP HANA Authorization is to protect important data from unauthorized access and ensure compliance and standards are met as adopted by an organization.

SAP HANA provides a facility of multitenant database container i.e multiple databases can be created on single SAP HANA DB System. Hence SAP HANA provides Authorization related feature for all multitenant database container.

2. HANA Architecture

SAP HANA can be used as a relational database in a classic 3-tier architecture (client, application server, and database).

Three Tier Architecture

In this architecture, security-related features, such as authentication, authorization, encryption, and auditing, are located and enforced primarily in the Application server layer. The database is used as a data store only. Applications connect to the database using a technical user, and direct access to the database is only possible for database administrators. End users do not have direct access to either the database itself or the database server on which it’s running.

A two-tier architecture model has a Presentation layer and combined Web application and database layer.

3. HANA Studio

HANA Studio is an Eclipse-based, which is one of the applications used for Development of artifacts in a HANA database.

SAP HANA studio provides an environment for Administration, Modeling and Data Provisioning etcc.

Below are the widely used HANA Perspectives

– Administration Console

– Modeler

– SAP HANA Development

In HANA Studio there are 4 main sub-nodes for each HANA system

Catalog – We can view all the list of Schemas and Public Synonyms

Content – The Content represents the design-time repository, which holds all information of data models created i.e Packages. Physically these models are stored in database tables which are also visible under Catalog.

Provisioning – We can view list of Remote Source users.

Security – We can view Users and Roles list.

4. HANA Database Objects

Package

HANA Package is a container of individual Development objects such as Tables, Views Procedures.

Packages can be used to Transport the objects from development system to other systems.

HANA roles will be created based on Packages.

Tables

Tables are used to store the data.

In HANA, Data can be stored in both Row and Column based

Procedure

Procedures are reusable processing blocks and describe a sequence of data transformations.

Procedure is a unit/module that performs a particular task.

Procedures can have multiple input and output parameters.

Schema

Schema holds all the database objects such as tables, views, procedures etcc

4.1. Types of Schema

User Defined Schema – Created by an database or developer user.

System Defined Schema – Following are the standard system Schema created when a HANA system is configured.

SYS_BI – This schema stores all the metadata of created column Views.

SYS_BIC – This schema will holds all the Columns Views of activated objects.

When the user activates any view/ Procedure, the respective entry will be under _SYS_BIC->Column Views. When the client consumes the Views, only these column views will be exposed.

SYS_REPO – This schema holds the list of Active and Inactive Objects, Package details and

Runtime Objects.

SLT Derived Schema – Schema of a data Replication user

4.2 Types of Database Objects – Views

1. Attribute View

- Attribute views are used to join to a dimension or attribute view.

- In most cases used to model master data like entities (like Product, Employee, Business Partner)

- Highly re-used and shared in Analytic and Calculation Views

2. Analytic View

- Analytic views are star schemas or fact tables surrounded by dimensions,

- Analytic views leverage the computing power of SAP HANA to calculate aggregate data, e. g., the number of bikes sold per region.

- It is specifically designed to execute star schema queries.

3. Calculation View

- Calculation views are composite views used on top of analytical and attribute views.

- Calculation views can be referred as combination of tables, attributes views and analytical views to deliver a complex business reporting requirement.

5. HANA User Authentication

Users verified through a process Named “Authentication.” SAP HANA supports many authentication methods and following are the types where Single Sign-on (SSO) are used to integrate several Authentication methods.

Kerberos

- Directly from JDBC and ODBC Client (SAP HANA Studio).

- When HTTP is used to access SAP HANA XS

Username / Password

- Authentication with database username and password

Security Assertion Markup Language (SAML)

- Mapping external user identity to the internal database user.

SAP Logon and Assertion Tickets

- Use authentication by Logon or Assertion Tickets

X.509 Clients Certificates

- Authenticate Client certificates signed by a trusted Certification authority (CA)

6. HANA User Administration

Standard System users

These are the default users created at the time of HANA system set up and these users will have wider access.

SAP HANA Database system provides following user by default as standard user

· SYSTEM

· SYS

· _SYS_REPO

Technical User

These users are used for Data Replication and connecting external system with HANA system.

Database User

End users, Admin users who wants to work on SAP HANA database, need a database user.

Restricted User

- Restricted users are used to access HANA system with some applications i.e HTTP.

- Created with “CREATE RESTRICTED USER“ sql statement.

- Restricted User cannot create database objects and cannot view data in the database.

Sample User Creation in HANA DB via SQL command – Option 1

Select on HANA System and click on SQL icon. Advantage of Sql command is that we can create multiple users with role addition, validity, password for individual users.

In the SQL Console, execute below SQL query to create a user

CREATE USER <userid>

PASSWORD <Password>

VALID UNTIL <‘validity’> ;

Once user is created, then click on Deploy.

If you want to disable initial password change

ALTER USER <userid> DISABLE PASSWORD LIFETIME;

Sample User Creation – Option 2

We can create User using Security option, but with this we can create only one user at a time and manually requires to provide all the details such as Date, password, roles etcc

7. HANA Role Administration

HANA Roles are nothing but collection of Privileges. Users are assigned with necessary privileges via Roles.

7.1 Types of HANA Roles

NOTE: PUBLIC Role is Generic role and is assigned to all database users by default. This role contains read only access to system views and execute privileges for some procedures. This role cannot be revoked.

7.2 Creation of Catalog roles

The catalog role is the runtime role.

In the SQL Console, execute below SQL query to create the catalog role,

CREATE ROLE <role_name>;

7.3 Creation of Repository/Design Time roles

For repository/design role creation, change the perspective to SAP HANA Development from “Open perspective” as below.

Before working on Repository, we must create a new workspace as below.

Select Default and click on Create Repository workspace

Select the system and click on ‘Finish’ and later you can see right tick mark on “Default workspace” like this.

Right click on the package–>New –>Other–> General–> File–> Enter the role name as TEST_ROLE_DISPLAY1.hdbrole–>Finish

Enter role ABCD::<role_name>, where ABCD denotes path/package role being created and role_name should be repository role name, given in the previous step i.e. TEST_ROLE_DISPLAY_1(without .hdbrole at the end)

NOTE: For all the Design-time/Repository roles, File name should end with .hdbrole

Add the necessary database objects such us tables, views, schema, procedure, etc with desired privileges and activate the role.

8. HANA Privileges

In HANA there are multiple types of privileges and below are some of them

System privileges: Authorize execution of administrative actions for the entire SAP HANA database.

USER ADMIN – Authorizes the user creation and modification.

ROLE ADMIN – Authorizes the role creation and modification of roles.

TRACE ADMIN – Monitoring and tracing of users

EXPORT – Authorizes EXPORT a file on the SAP HANA server.

IMPORT – Authorizes the import activity in the database.

AUDIT ADMIN – Managing Audit

REPO.READ – Read access on all Packages.

DATA ADMIN – Authorizes reading all data in the system views.

REPO.EXPORT – Authorizes the export of delivery units.

REPO.IMPORT – Authorizes the import of delivery units.

REPO.MAINTAIN_DELIVERY_UNITS – Authorizes the maintenance of delivery units.

- Object privileges: Authorize access to data and operations on database objects. To access database objects user needs object privileges on database objects or on the schema in which database object exists.

CREATE ANY, UPDATE, INSERT, SELECT, DELETE, DROP, ALTER, EXECUTE, INDEX, TRIGGER, DEBUG, REFERENCES

- Analytic privileges: Authorize read access on analytic views at run-time, provide row-level access control based on dimensions of the respective view.

- Package privileges: Authorize access in the repository (modeling environment) at design time.

- Application privileges: Authorize access to SAP HANA XS application functions.

8.1 Analytic Privileges

Authorizes read access on analytic views at run-time, provide row-level access control based on dimensions of the respective view. Users assigned with this privilege can access restricted of certain views/fields. Users without this privilege with same table access cannot read restricted views/fields.

There are two types of Analytic Privileges

8.1.1 Classical Analytic Privileges

The classical analytic privilege is the simple XML-based privilege which use SAP HANA Studio UI to create and assign basic privileges to users. Some features of classical analytic privileges in SAP HANA are:

• Applicable on attribute views, analytic views, and calculation view.
• It is based on design-time modeling.
• Transportable.
• Do not offer HDI support.
• Do not offer complex filtering.

As you can see in the below screenshot, whenever a view is selected with Classical Analytic

Privileges, the view should be reference with a XML based Classical Analytic privileges.

8.1.2 SQL Analytic Privilege

SQL analytic privilege is SQL-based and allows users to create more complex restriction conditions than classic privileges. Users can apply complex filtering and restriction conditions based on information models and procedures.

Some important features of SQL analytic privilege are:

• Applicable on attribute views, analytic views, and calculation view.
• It is transportable.
• Offers HDI support.
• Offers complex filtering.

8.1.3 Standard Analytic Privilege – _SYS_BI_CP_ALL

SAP standards Analytic privileges SYS_BI_CP_ALL gives Full data access to all information view and it will override any data restrictions i.e Classical or Analytical Privileges.

9. Troubleshooting – How to Trace

1. Right Click on HANA System –> configuration and Monitoring -> Open Administration–>Trace Configuration–>User Specific Trace–> fill in details as shown below.

2. Select Component–>INDEXSERVER –> select System Trace Level as INFO for authentication and authorization.

3. Once Trace is activated on a user id,let user execute the query/view for which user having issues

4. To read the trace log go to Diagnosis File and Filter on the Context Name

5. Following is the Trace file log where we can see the missing privilege for the user

10. Auditing

The auditing feature of the SAP HANA database allows you to monitor and record selected actions performed in your system.

An audit policy defines the actions to be audited, as well as the conditions under which the action must be performed to be relevant for auditing. When an action occurs, the policy is triggered and an audit event is written to the audit trail.

10.1 Privileges Required

To Enable Audit Policy, user must have System Privilege AUDIT ADMIN.

Following are the steps to Enable:

Expand Security Folder –> Double Click on Security–> Select Auditing Tab

Following Audit Trail targets are possible.

Syslog (default) − Logging system of Linux Operating System.

Database Table − Internal database table, user who has Audit admin or Audit operator system privilege he can only run select operation on this table.

CSV text − This type of audit trail is only used for test purpose in a non-production environment.

10.2 Audit Policy Creation

Create a new Audit policy in the Audit Policies area → choose Create New Policy. Enter Policy name and actions to be audited.

10.3 How to Read Audit Logs

In order to Read the audit log, go to Catalog–>Public Synonyms–>Filter on AUDIT_LOG–> open Content in order to read the logs

Tips &Tricks

• Create roles in Design-time (repository roles).
• Ensure you are in the repository when working with the HANA Studio.
• Transfer ownership of all the objects created in the repository to _SYS_REPO to avoid issues if your user is deleted.
• Never assign privileges directly to user profile, instead assign via roles.
• SAP template roles are having wider access, so create custom roles instead.
• Do not provide change access in Production environment.