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.

http://service.sap.com/swdc → Support Packages and Patches → Browse our Download Catalog → SAP In-Memory (SAP HANA) → SAP HANA … EDITION → SAP HANA … EDIT. 1.0 → Comprised Software Component Versions → SAP HANA CLIENT 1.00 → Windows Server on IA32 32bit → IMDB_CLIENT100_32-10009665.SAR – Revision 32 for SAP HANA CLIENT 1.00

Run the installation using either hdbinst or hdbsetup.

2.2.) Register new data source

Run Microsoft Office Excel and open new workbook.

In menu Data choose option PivotTable and PivotChart Report.

Choose External data source and click Next.

Click on Get Data.

In case the you will have request to install Microsoft Query feature choose Yes. Wait for installation to finish.

From the list choose <New Data Source> item and click Ok.

Choose name for data source and choose driver HDBODBC32. Click Connect.

Insert server name (or IP address) and port (in format 3<system number>15). Also provide valid credentials – see chapter 1 about choosing proper user. Click Ok.

Leave other options empty (you might choose to save credentials as part of data source definition however this options is not recommended for security reasons). Click Ok.

2.3.) Create new Query

Choose newly defined data source and click Ok.

In new dialogue click on button Options.

Adjust filter to see only views from schema SYS. Click Ok.

Choose all fields of view GRANTED_ROLES. Click Next.

Choose filter GRANTEE_TYPE = ‘USER’. Click Next.

Do not define sorting – we will define this as part of pivot table. Click Next.

Choose option View data or edit query in Microsoft Query. Click Finish.

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:

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