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

[tomas-krojzl]

First part of this blog can be found here:

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

2.5.) Create pivot table

After closing Microsoft Query editor you should see following dialogue:

Click Next.

Click Layout.

Arrange fields according to image above. Double click on ROLE_NAME.

Choose None as Subtotals and click on Advanced.

Choose to sort type Ascending and confirm all dialogues until you reach the PivotTable layout dialogue.

Repeat same operation with GRANTOR, IS_GRANTABLE and GRANTEE. Once all fields have defined sorting then confirm the layout dialogue by clicking Ok.

Confirm dialogue by clicking Finish.

Enter credentials and confirm.

2.6.) Fine tune formatting of pivot table

Result of our exercise should look like on image above. It is not very user friendly because columns are taking too much space.

Select lines 3 and 4 - it is very important to format whole lines and not only existing fields of pivot table to accommodate new columns that might be added in future. Then click on menu Format item Cells and change orientation to vertical.

Then repeat same process with first three columns (A-C) and return orientation back to horizontal.

As last step select whole sheet (all cells) and choose menu Format, sub-menu Column and option AutoFit Selection.

Result should look similar to picture above.

Congratulations you created your first security pivot table!

3.) Create all other security pivot tables

Now you can repeat the process for other scenarios we defined. I will not repeat whole procedure – just important milestones.

3.1.) Pivot table GRANTED_ROLES – Role assignments

This pivot will be almost identical as previous however it will be showing only roles.

On new sheet create new PivotTable. Use the same data source but define new query – similar to query above but using filter GRANTEE_TYPE = 'ROLE'. Save query as ODBC_HD1_GRANTED_ROLES_ROLE.dqy.

Define same layout as above. Do not forget to disable subtotals and to configure proper sorting. As last step format the pivot table to be user friendly.

3.2.) Pivot table GRANTED_PRIVILEGES – User assignments (except SYS)

Now more difficult part of this blog will come. We will need to configure new pivot table based on view GRANTED_PRIVILEGES. We will intentionally exclude privileges granted by user SYS as these are internal and cannot be influenced by users.

Again repeat whole procedure. New sheet, new pivot table, same data source, new query – this time against view GRANTED_PRIVILEGES.

Set filter to GRANTEE_TYPE = 'USER' and GRANTOR <> 'SYS'.

In the query definition remove column GRANTEE_TYPE, drag again OBJECT_TYPE and place it as duplicate column next to first occurrence of OBJECT_TYPE and edit SQL query to add 1 as last column (see example above).

Query should look like below.

Save the query as  ODBC_HD1_GRANTED_PRIVILEGES_USER.dqy

Define layout for pivot table – place one OBJECT_TYPE as page selector and second OBJECT_TYPE as row, disable subtotals and define sorting.

Adjust final pivot table to look nice and tidy. If you think that pivot table is too busy – feel free to remove some columns – number we defined as measure will then represent amount of records that were grouped together.

Note: Not all direct assignments are real deviations. Tools like SAP Information Composer are working with direct privilege assignments. Also some internal procedures are have privileges directly assigned to SYSTEM user. And to enable reporting user _SYS_REPO must have SELECT privilege with grant option. All these situations are considered to be normal.

3.3.) Pivot table GRANTED_PRIVILEGES – Role assignments (except SYS)

This pivot will be almost identical as previous but focused on roles.

Repeat same steps as in previous example but define filter as GRANTEE_TYPE = 'ROLE'.Save query as ODBC_HD1_GRANTED_PRIVILEGES_ROLE.dqy.

Define same layout as above. Do not forget to disable subtotals and to configure proper sorting. As last step format the pivot table to be user friendly.

Technically you can use GRANTEE_TYPE as another selector and reduce amount of queries and pivot tables – however I prefer to have these two use cases separated because I can customize them based on my needs.

Congratulations you just created your own security monitoring tool.

In next blog I will share some database queries that can be very useful in analysing the security. Next part can be found here:

SAP HANA - How to analyze who has access to particular object