SAP HANA – Create your own security monitoring tool (part 1)

In case you are having SAP HANA system containing dozens of roles and dozens of users you might be very frustrated by not having any clear view about who is assigned what roles or privileges.

Approach below can help you on how to easily get overview about user-role assignments. We will create our own tool that will help you to oversee current security definition in your system.

This blog can be seen as another walk-though on how to configure SAP HANA query consumption from MS Excel – however this was not the purpose.

1.) Exploring SAP HANA database

Goal is to make minimal changes to SAP HANA – we will be therefore using default views that are visible to every user.

Note: Unless you are using system user SYSTEM – you must be aware that you will be able to see only subset of all available role assignments and privilege assignments.

SYS.GRANTED_ROLES

This view is listing all role relations – role that was granted, who granted the role, if the target was another role or user, if the role can be granted further.

Table is having following definition:

View Column

SQL Data Type

Dim

Comment

GRANTEE

NVARCHAR

256

User or role the role is granted to

GRANTEE_TYPE

VARCHAR

5000

‘USER’ or ‘ROLE’

ROLE_NAME

NVARCHAR

256

Name of the granted role

GRANTOR

NVARCHAR

256

User who granted the role

IS_GRANTABLE

VARCHAR

5000

Role was granted ‘WITH ADMIN OPTION’: ‘TRUE’/’FALSE’

Below you can see example of the view content.

GRANTEE

GRANTEE_TYPE

ROLE_NAME

GRANTOR

IS_GRANTABLE

USER_1

USER

PUBLIC

SYS

FALSE

USER_1

USER

IC_MODELER

A_INFOCOMP

TRUE

ROLE_1

ROLE

PACKAGE_1

SYSTEM

FALSE

ROLE_1

ROLE

PACKAGE_2

SYSTEM

FALSE

ROLE_2

ROLE

PACKAGE_1

SYSTEM

TRUE

From this view we will be extracting GRANTEE and ROLE_NAME relation in two separate scenarios – first where GRANTEE_TYPE is USER (to list roles directly assigned to users) and where GRANTEE_TYPE is ROLE (to list role inter-dependencies).

SYS.GRANTED_PRIVILEGES

This view is listing all privileges that were assigned either to users directly or to roles.

Table is having following definition:

View Column

SQL Data Type

Dim

Comment

GRANTEE

NVARCHAR

256

User or role the privilege is granted to

GRANTEE_TYPE

VARCHAR

5000

‘USER’ or ‘ROLE’

GRANTOR

NVARCHAR

256

User who granted the privilege

OBJECT_TYPE

NVARCHAR

256

Type of the granted object like:

ANALYTICALPRIVILEGE

MONITORVIEW

PROCEDURE

REPO

SCHEMA

SEQUENCE

SYSTEMPRIVILEGE

TABLE

VIEW

SCHEMA_NAME

NVARCHAR

256

Schema name the object belongs to

OBJECT_NAME

NVARCHAR

5000

Object name of granted object

COLUMN_NAME

NVARCHAR

5000

Column name

PRIVILEGE

NVARCHAR

256

Privilege granted

(same as you can see in user administration dialog)

IS_GRANTABLE

VARCHAR

5000

Privilege was granted ‘WITH GRANT OPTION’ or

‘WITH ADMIN OPTION’: ‘TRUE’/’FALSE’

IS_VALID

VARCHAR

5000

Privilege is valid or it became invalid because of implicit

revoking: ‘TRUE’/’FALSE’

Below you can see example of the view content (separated into two tables to ensure proper formatting).

GRANTEE

GRANTEE_TYPE

GRANTOR

OBJECT_TYPE

SCHEMA_NAME

USER_1

USER

SYSTEM

SYSTEMPRIVILEGE

?

USER_1

USER

A_INFOCOMP

PROCEDURE

SYS

ROLE_1

ROLE

SYSTEM

SYSTEMPRIVILEGE

?

ROLE_1

ROLE

SAP_IC

TABLE

SAP_IC

OBJECT_NAME

COLUMN_NAME

PRIVILEGE

IS_GRANTABLE

IS_VALID

?

?

USER ADMIN

FALSE

TRUE

REPOSITORY_REST

?

EXECUTE

TRUE

TRUE

?

?

ROLE ADMIN

FALSE

TRUE

IC_T1

?

SELECT

FALSE

TRUE

From this view we will be extracting following cases – where GRANTEE_TYPE is USER with possibility to filter by OBJECT_TYPE (we should be having minimum of these – because it means that privilege was directly assigned to user) and where GRANTEE_TYPE is ROLE with possibility to filter by OBJECT_TYPE.

2.) Create your first security pivot table

In order to consume anything from SAP HANA you need to meet certain prerequisites.

MS Excel is communicating with SAP HANA using ODBO (OLE DB for OLAP) interface. So we will need to have this installed. ODBO is being distributed as SAP HANA client.

Keep in mind that you will need to have same “bit-version” of client as application that is using the client. Since the MS Excel we are using in this example is 32-bit version (Microsoft Office Excel 2003 SP3) – we will also need to install 32-bit version of SAP HANA Client.

2.1.) Download and install SAP HANA Client

Since our SAP HANA Database is running on revision 32 – we will also need to download revision 32 Client.

2.4.) Adjust query to our needs

This should be initial view after you defined the query. Now we can remove column GRANTEE_TYPE and we will add new column with fixed value 1 (to act as measure).

Click on column GRANTEE_TYPE and delete it.

Then from menu View choose option SQL.

Add highlighted text ‘, 1’ to the SQL query. Confirm Ok.

Save the query as ODBC_HD1_GRANTED_ROLES_USER.dqy

Close the Query Editor.

I decided to use a lot of images to make this blog very clear and self-explanatory. Because of that the blog is too long and therefore I decided to split it into two parts.. Second part can be found here: