×

Sap Support

- Sap Support - Dump files for support

Log & Trace file

- Log and trace directories - Investigate backup log content
<

Tools : Hana Studio

- Installation - Using hana studio - Using Studio SQL Console

Tools : hdbsql

- Using hdbsql - hdbsql parameters - Executing SQL - Hiding header welcome message - Hiding column header - Update column delimiter - Formating output result - hdbsql and shell script

Tools : hdbrsutil

- hdbrsutil tips & tricks

Tools : Hana Cockpit

- Install hana cockpit 2 complete example - Un-Install hana cockpit 2
<

Disk

- Disk space used by log segments - Reclaim space from log segments - Retrieve table index space used on disk

Specific Operations

- Adding physical memory

Sap Hana User Privileges

- Role and privileges - Grant read access to a schema

Stored procedures

- Creating a procedure - Dropping a procedure - Compiling a procedure - Executing a procedure - Working with procedures - Using anonymous block

Sap Hana functions

- DateTime : ADD_YEARS - DateTime : EXTRACT

Parameters

- Configuring sap hana - Kill long queries automatically

SQL

- Sql guide line - How to use SQL -Sql references - Sql tips & tricks for tables

SQL Query

- SQL & Tables - SQL & syntaxes - How to practice SQL
<

Sap Hana Tables

- Managing tables - Various ways to create Tables - Sap Hana Tables - Sap Hana Temporary Tables

Table description & DDL

- Retrieve table description - Table description using table-columns-view - Table ddl with hana studio - Table ddl using 'get_object_definition' procedure - Table ddl using hdbsql

SELECT data

- Various ways to select data

INSERT data

- Various ways to insert data

Sap Hana Users

- Managing Users - Create user with hana studio - Create user with sql Script - Create multiple users in one go - Copy a user - Create User with SQL script - Create User via Hana Studio - Update User - Drop User

Create Standard User

- Create standard & personal User

Create Technical User

- Create a technical User - Create backup users - Create administrator users - Create Modeler User - Create Data provisioning User - Create Cockpit User - Create a user to unlock any users
<

How to manage Sap Hana roles and privileges?

An existing Role is a particular container made of several sap hana database privileges. It is a good idea to group any new authorizations into a role instead of giving privileges directly to a user. The hana administrator is responsible for managing sap hana authorizations via sap hana user profile or roles. The task is to assign privileges to users via roles. It is very useful as you only have to grant a unique sap hana role to each new hana user instead of looking at granting multitude privileges.

Predefined Sap Hana roles are available for specific users such as hana backup user or hana modeller user. When it comes to application users, particular hana roles are created according to user task requirements.

Sap Doc: Setting up roles and Privileges

How to create new roles in Sap Hana?

Sap Hana already provides predefined ROLES but for specific reasons you might want to manage privileges your own way and create user profile via you own set of ROLES. The easier way to create a sap hana role is via an SQL command. It is very straight forwards with a sinple SQL command:
"CREATE ROLE < ROLE_NAME >";
Do no forget, a new role is just a new empty box. There is no privileges assigned to the role. Giving privileges is the next step.

About Type LinkTo

Create Role

Sql Syntax

CREATE ROLE A_ROLE_BACKUP_RESTORE;

SQL syntax

Document

Overview-Role-Privileges

Document

Create

video

Create Roles

Sap Doc Guide

How to grant sap hana privileges and roles ?

A new role has been created. It is now the time to give certain rights to that role in order to assign this particular group of privileges to users. It is a very good practice to work with intermediates groups of privileges. Apart from the time it takes to prepare it, the gain in security management is high. Assigning privileges directly to users is easier in the first place but it becomes a nightmare where security is concerned. A group of standard users ends up with all sort of unnecessary privileges. With Roles, users are assigned to a specific profiles with the exactly the same privileges.

Different type of roles exist in SAP HANA.

- Standard Role.
There are many standard roles already available by default. Those roles will be particulary assigned to technical users. Roles that would be newly created will be managed by yourself.

# # -- Create standard role # SQL> CREATE ROLE MY_COCKPIT_ROLE; # # -- Assign sap hana default role to new role # SQL> GRANT MONITORING TO MY_COCKPIT_ROLE;

- Repository Roles.
Another type of hana role is also available. It can be find in a Sap Hana repository under the technical user _SYS_REPO. It is adviced to used hana repository roles as they can be easily transported to other hana Systems.

In order to assign a sap hana repository role the following syntax has to be used :

In this example, the aim is to grant a Sap hana repository ROLE to a standard new created ROLE

SQL> call GRANT_ACTIVATED_ROLE('sap.hana.admin.cockpit.sysdb.roles::SysDBAdmin','MY_COCKPIT_ROLE'); or SQL> call "_SYS_REPO"."GRANT_ACTIVATED_ROLE"('sap.hana.admin.cockpit.sysdb.roles::SysDBAdmin','MY_COCKPIT_ROLE');

There are also different types of privileges in Sap hana which can be assigned to a role or directly to a users. Here are the different hana privilege categories : - System privileges. They are in relation to Sap Hana system envrionment administration.
Hana System privileges are reserved to high level operations. It is accreditted especially to hana database administrators but have to been also granted to other users such as Hana Modeller users.

- Object Privileges. They concerne what can be done with object such as tables. ex : SELECT, INSERT, DELETE, ... etc
Object privileges will be appointed to hana application developpers but also to standard application users.

- Analytic Privileges. It is used to control read-only access to SAP HANA data models.
Analytic privileges will be set in parcitular to hana application users in order to filter the dataview ability.

- Package Privileges. They give the right to access and work with packages.
Package privileges are normaly targetted and granted to hana developers.

- Application Privileges. It give access right to work with an application.
Application Privileges will be assign to end user application.

In this example, the aim is to grant a Sap hana system privilege to a standard new created ROLE
SQL> GRANT BACKUP ADMIN TO A_ROLE_BACKUP_RESTORE; ;

About Type LinkTo

Assign privileges to role

Document

Please Note : SAP HANA SQL command line is case sensitive in particularly when using functions and procedures. Unrelated errors messages could occur when certains commands are not recognized.

How to drop a role in Sap Hana?

In the case of home made role, you may want to get rid of one of them for x reasons. Before going ahead dropping a Role makes sure the role is no longer in use. Dropping a role may have important impact especially if technical users are attached to it. Batch or backup may stop working.

About Type LinkTo

Drop multiple Roles

Sql Script

SELECT 'DROP ROLE ' || ROLE_NAME ||';' FROM ROLES where ROLE_NAME like 'MY_%'

Result :

SQL> DROP ROLE MY_ROLE_ADMINISTRATOR;
SQL> DROP ROLE MY_ROLE_ALL_SYSTEM_ROLES;
SQL> DROP ROLE MY_ROLE_ALL_SYSTEM_PRIVILEGES;

How to find role name and role privileges in Sap Hana?

Looking for roles and privileges is an important requirement especially when security is concerned. Sap hana studio is an elaborated tool to retrieve role and all associated privileges. SQL script is another way but specific queries have to be written to produce meaningful report.

About Type LinkTo

Show Granted Role

Sql Syntax

SELECT * FROM "PUBLIC"."EFFECTIVE_ROLES" where USER_NAME = 'SYSTEM';

SELECT * from GRANTED_ROLES where GRANTOR = 'SYSTEM' and GRANTEE_TYPE = 'ROLE';

SELECT * from GRANTED_ROLES where GRANTOR = 'SYSTEM' and GRANTEE_TYPE = 'USER';

Show All Granted Privileges

Sql Syntax

SELECT * FROM "PUBLIC"."EFFECTIVE_PRIVILEGES" where USER_NAME = 'SYSTEM';

SELECT * from GRANTED_PRIVILEGES where GRANTOR = 'SYSTEM' and GRANTEE_TYPE = 'USER';

SELECT * from GRANTED_PRIVILEGES where GRANTOR = 'SYSTEM' and GRANTEE_TYPE = 'ROLE'

Show Granted object Privileges

Sql Syntax

SELECT DISTINCT PRIVILEGE, grantee, grantee_type, object_type,is_valid from GRANTED_PRIVILEGES where grantee = 'SYSTEM' and object_type <> 'SYSTEMPRIVILEGE';

Different syntaxes to grant a role in Sap Hana?

There are different ways and different syntaxes to assign a role. It is depending on the Role type.

About Type LinkTo

Grant standard Role to Role

Sql syntax

GRANT BACKUP ADMIN, CATALOG READ TO A_ROLE_BACKUP_RESTORE;

Grant repository Role

Sql Syntax

call GRANT_ACTIVATED_ROLE('sap.hana.admin.cockpit.sysdb.roles::SysDBAdmin','PE_ROLE_ALL_SYSTEM_ROLES');

Grant standard Privilege

Sql syntax

GRANT BACKUP ADMIN TO A_ROLE_BACKUP_RESTORE;

Grant Application Privilege

Sql syntax

call "_SYS_REPO"."GRANT_APPLICATION_PRIVILEGE"('"com.acme.Appli01::Execute"','PE_ROLE_ALL_SYSTEM_ROLES')

SQL syntax

Document

SQL syntax

Sap Reference

How to use Sap Hana Roles?

The use of sap hana role is to be able to manage and group privileges under different and specific pots. Sap Hana Default Roles exists but home made roles should also be present.


About Type LinkTo

Using Analytic Privilege

Video

Using Analytic Privilege

Document

Object-Privileges

Sap Document

System-Privileges

Sap Document

Backup-Privileges

Sap Document

Predefined Roles

Sap Document

Stored Procedures to Grant

Document

Privileges

Document

Privileges

Document

Description

Document

How to find user privileges?

This is an important subject when the sap hana administrator wants to know whether a user has individual privileges assigned to it instead of roles.

About Type LinkTo

Analytic

Document

User-System

Sap Document

Sap hana SQL

Sap hana tools

- - -