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:
|
| 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)
- SAP Managed Tags:
2 Comments
