SAP HANA Data Architecture and Security Concept (Part: 2)

First part of this blog can be found here:

SAP HANA - Security Concept and Data Architecture (part 1)

This second part is describing concept of roles and how to use SQL interface to define security objects.

9 Role definitions

Roles should be designed using role hierarchy ability of HANA database. First you should create elementary roles for each schema/package. These can be then directly assigned to users or can be orchestrated together to form higher level roles based on type of the user.

Role can be created using simple syntax:

     CREATE ROLE <role name>

9.1 Elementary roles - SQL privileges

We will design following roles addressing SQL privileges:

• role DATA_*_READ having privilege to SELECT
• role DATA_*_WRITE having privilege to INSERT, UPDATE, DELETE and EXECUTE
• role DATA_*_DDL having privilege to CREATE ANY, ALTER, DROP, INDEX

As result we will need to crate these roles:

• role DATA_SLT_IDD800_READ
• role DATA_SLT_IDD800_WRITE
• role DATA_SLT_IDD800_DDL
• role DATA_SLT_IDE800_READ
• role DATA_SLT_IDE800_WRITE
• role DATA_SLT_IDE800_DDL
• role DATA_BODS_TESTING_READ
• role DATA_BODS_TESTING_WRITE
• role DATA_BODS_TESTING_DDL
• role DATA_MAN_TESTING_READ
• role DATA_MAN_TESTING_WRITE
• role DATA_MAN_TESTING_DDL
• role DATA_MAN_COPA_READ
• role DATA_MAN_COPA_WRITE
• role DATA_MAN_COPA_DDL

SAP HANA Data Architecture and Security Concept (Part: 1)

1 Introduction

This blog is written against HANA database revision 16.

Security concept in HANA database is slightly different from other enterprise databases. These differences are introducing certain considerations that must be taken into account when creating data architecture of HANA database.

Goal of this blog is to share approach how HANA database can be designed to separate different users from each other without impacting their ability to collaborate.

The reader is expected to download and read at least following SAP guide before reading this document.

SAP HANA 1.0 - SAP In-Memory Database - Security Guide

http://help.sap.com/hana/hana1_imdb_sec_en.pdf

Note: This design does not cover considerations related to SLT replication. This will be addressed by another blog.

2 Security limitations

As mentioned before, the HANA security model differs from other enterprise databases like DB2 or Oracle, making it a special case indeed.

First “problem” is that HANA is extremely rigid in SQL privileges. You need to have corresponding grant privilege for any activity you wish to grant. While this concept is very logical it might cause some problems. Initially only the schema creator is having required privileges and therefore only he can grant them (including granting “privilege to grant it further”). Unfortunately there is no “power user” that would be exception to this rule. This leads to unpleasant situation that only owner can delegate his own SQL privileges which might be big problem in case he is not aware how to do this.

This design feature can be addressed by separating data owners from application users and from modelers. Dedicated technical users (owned by system administrator) will be holding the data and all other users (modelers and application users) will have corresponding privileges to access the data. Modelers should not create any content within their own schema - they should always work inside data owner’s schema. (This can be achieved by deleting user’s own schema.)

Second consideration is common practice to not grant any privileges directly to users but to grant them via roles. Unfortunately without ROLE ADMIN system privilege you cannot adjust the role (because you will not be able to see it) and grant it SQL privilege as explained above.

This point is also solved by having separate data owner users that you can control yourself. You can grant them ROLE ADMIN privilege, do required changes and then again revoke ROLE ADMIN system privilege. Alternatively you can use SQL interface where this problem does not exist.