Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
tomas-krojzl
Active Contributor

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.

3 Project definitions

Complexity of the resulting security setup will depend on the number of separate development activities (let’s call them projects) that you need to setup.

Therefore first step should be definition of projects.

In our example we will define three projects:

  • project copa
  • project hr

Requirement is to separate these three projects from each other without restricting collaboration of several modelers on one project.

Purpose of project testing is to provide separate area where users can do their experiments or where they can explore mechanics of HANA modeling.

4 User types

In your HANA database you will need following users:

Regular users (examples):

  • modelers (with ability to work in foreign schema)
  • replication administrators (in case of SLT replication)
  • other administrators (database, backup, etc.)

Technical users:

  • data owners (you should have separate schema for each data source - for example client in SAP system, non-SAP data source, manually created tables, etc.)
  • application users (you should have separate user for each external application connecting to HANA to retrieve the data)

5 Database schemas

As already mentioned you must have separate schema (and therefore separate user) for each data source. Reason is to avoid name collisions between the systems (different tables using same name in several systems). You should create some naming concept that you will follow.

In our example we will have two SAP systems replicated via SLT as source systems:

  • schema DATA_SLT_IDD800 - SAP system IDD client 800
  • schema DATA_SLT_IDE800 - SAP system IDE client 800

Some tables will be created by SAP BusinessObjects Data Services (as part of project testing):

  • schema DATA_BODS_TESTING

And we will also create some tables manually (for projects testing and copa):

  • schema DATA_MAN_TESTING
  • schema DATA_MAN_COPA

Schema will be automatically created when corresponding user is created.

Caution:

Creator of object is always owner of this object even if it is in foreign schema. Be very careful about consequences of this rule (especially when deleting users).

6 Database users

6.1 Data owners

Separate database user is required for each schema. Only system/security admin will have access to all these users as he will need this access for setting security privileges.

Following data owners are required (one for each required schema):

  • user DATA_SLT_IDD800
  • user DATA_SLT_IDE800
  • user DATA_BODS_TESTING
  • user DATA_MAN_TESTING
  • user DATA_MAN_COPA

6.2 Human users

We will need some regular users (including modelers). We will use this naming convention:

  • user U_NAME1
  • user U_NAME2
  • user U_NAME3

6.3 Application users

At least one application user should be created for each application that is connecting into the HANA system. In case that same application is used in multiple projects then you should define several application users - one for each project.

We will define following application user:

  • user A_BODS_TESTING - user for SAP BusinessObjects Data Services (for testing project)

7 Packages

Classical database objects like Tables, Indexes and Views are stored in database schemas.

All analytical objects like Attribute Views (Dimensions), Analytical Views (Cubes), Calculation Views are stored in packages.

Analytical objects should be separated into packages forming independent areas of development (one for each project). We will define following packages:

  • package prj-testing
  • package prj-copa
  • package prj-hr

Note: Package name can contain only small letters (a-z), digits (0-9), dash ( -) and up to 9 dots (.).

You might also use additional packages to separate development experiments or for shared objects. It is important to keep database content organized.

Objects cannot be copied between packages but new object can be created using other object as template. This functionality can be used to “move” successful experiments into productive package.

Packages cannot be created via script. Create them manually.

8 Analytic Privileges

Analytic privileges are allowing you to define row level security by specifying affected analytical object, corresponding field and acceptable value. Only rows meeting specified criteria are then being retrieved.

This functionality can be used to separate users into smaller groups based on field values in table rows.

We will define following analytic privileges for prj-hr package:

  • analytic privilege PRJ_HR_USA
  • analytic privilege PRJ_HR_CANADA
  • analytic privilege PRJ_HR_EMEA

These privileges will be ensuring that only relevant subset of sensitive HR records will be accessible to given user (for example external application).

Note: Analytic privileges are defined against content of corresponding package. Therefore usually you need to first create this content before creating analytic privileges (unless you wish to define analytic privilege for all objects).

In example below we will define generic privilege ANALYTIC_TESTING_ALL allowing users to access all objects in ALL packages.

Warning:

If you will define analytic privilege with option “Applicable to all Information Models” this privilege will be effective for all objects in all packages. Currently it is not possible to define analytic privilege against one package.

Analytic privileges need to be created manually via HANA studio.

Second part (describing concept of roles and how to use SQL interface to define security objects) can be found here:

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

9 Comments