Skip to Content

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.

/wp-content/uploads/2012/07/snap000001_117807.png

In menu Data choose option PivotTable and PivotChart Report.

/wp-content/uploads/2012/07/snap000002_117808.png

Choose External data source and click Next.

/wp-content/uploads/2012/07/snap000003_117857.png

Click on Get Data.

/wp-content/uploads/2012/07/snap000011_117858.png

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

/wp-content/uploads/2012/07/snap000013_117859.png

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

/wp-content/uploads/2012/07/snap000014_117863.png

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

/wp-content/uploads/2012/07/snap000015_117864.png

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.

/wp-content/uploads/2012/07/snap000016_117865.png

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

/wp-content/uploads/2012/07/snap000017_117866.png

Choose newly defined data source and click Ok.

/wp-content/uploads/2012/07/snap000018_117868.png

In new dialogue click on button Options.

/wp-content/uploads/2012/07/snap000019_117869.png

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

/wp-content/uploads/2012/07/snap000020_117871.png

Choose all fields of view GRANTED_ROLES. Click Next.

/wp-content/uploads/2012/07/snap000021_117872.png

Choose filter GRANTEE_TYPE = ‘USER’. Click Next.

/wp-content/uploads/2012/07/snap000022_117873.png

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

/wp-content/uploads/2012/07/snap000024_117874.png

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

2.4.) Adjust query to our needs

/wp-content/uploads/2012/07/snap000025_117903.png

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.

/wp-content/uploads/2012/07/snap000029_117902.png

Then from menu View choose option SQL.

/wp-content/uploads/2012/07/snap000030_117882.png

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

/wp-content/uploads/2012/07/snap000031_117886.png

/wp-content/uploads/2012/07/snap000032_117885.png

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)

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply