Skip to Content

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)

To report this post you need to login first.

9 Comments

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

  1. anil sasidharan

    Hi Tomas,

    The blog is certainly helpful and helped me in HANA security concepts. I have a clarification with the schema privilege authorizations. The blog says only

    schema creator is having required privileges and therefore

    only he can grant them (including granting “privilege to grant it further”).

    I suppose only schema owner has the rights to grant it further initially. I created one schema XYZ  using ‘SYSTEM’ id and tried to assign to other user where it said insufficient privilege. I logged in as schema owner XYZ and then could assign to other ids. So in this case ‘SYSTEM’ is my schema creator , but it din’t has the right to grant further.

    Can you please clarify?

    Thank you,

    Anil


    (0) 
    1. Tomas Krojzl Post author

      Hello,

      the blog was written against very old revision – at that time schema creator was always schema owner. With recent revisions you can create schema and set other user as owner:

      CREATE SCHEMA <schema_name> [OWNED BY <user_name>]

      So yes – you are right – only object owner is having full privileges. (I intentionally used the word creator to underline the need to create the object as given user that was valid at that time.)

      Tomas

      (0) 
      1. anil sasidharan

        Hello Tomas,

        I have a query about data owner concept.As you suggested, I have separated responsibility to system admin, application users and modelers.  In system admin,

        Now I’m trying to divide the responsibility of system admin to user admin and role admin. But as I tried, ROLE ADMIN system privilege is required for assigning roles. So to become user administrator , both USER ADMIN and ROLE ADMIN privileges are required. This will give the access to create roles as well .

        With USER ADMIN system privilege alone, I can only create/delete users and not modify their access.

        Can’t we exactly divide the responsibility like we do in R/3 using S_USER_GRP and S_USER_AGR ?

        (0) 
        1. Tomas Krojzl Post author

          Hello,

          In order to grant role – all your user needs is:

          – to have ROLE ADMIN privilege, or

          – to be granted given role “WITH GRANT OPTION” (then the user can grant given role further)…

          However be aware that user will be able to see only role that he granted. This can be changed by granting privilege CATALOG READ – with this privilege the user will see all roles that were granted.

          One point I would like to highlight in relation to above mentioned blog. In the blog I am suggesting that SYSTEM user can be user to crate all roles. However in recent revisions SAP changed standard behaviour and now role creator is automatically having given role (this was not true before). This will cause the fact that SYSTEM user would have access to all data which is not desired. Therefore I would recommend to create new user to address security tasks.

          Tomas

          (0) 
  2. Sandip Maiti

    Hi Thomas,

    Thanks for providing all the details. Can you please provide the deblow details:

    1. What is the syntax to check the monitoring views, e.g, ROLES or USERS list in HANA database.

    2. How to add authorization in a new created role ? Is there any object level or field level parameters to add authorization in a role ?

    (0) 
    1. Tomas Krojzl Post author

      Hello,

      thank you for nice feedback however be aware this blog is probably very outdated – it was written in 2011 (four years ago) which is like 1000 years in SAP HANA space…

      Tomas

      (0) 

Leave a Reply