Skip to Content

A few months ago I was given a task to implement content security in SAP HANA. The main purpose for this task was to provide Business user access to information models created in SAP HANA. For example Finance user should only view finance package and can access information models in that package via BI tools such as Analysis for excel.

So, after a research and few discussions with various people I came up with following security model.

12-03-2014 9-57-57 PM.jpg

Let’s assume that content is maintained in following structure:

12-03-2014 10-02-11 PM.jpg

So based on each type of privilege I created the roles as shown below:

System Privilege Roles

These roles are mainly needed for System admin tasks (technical role)

X_HNS = S for System Privilege role

                                                                            

Role  

Name

Privilege

Type

Assigned

Privileges

X_HNS_USERADMIN

This role  can create users, change their password and delete users

System

Privilege

USER ADMIN

X_HNS_ROLEADMIN

This role can  create roles, alter roles and drop roles with SQL commands1

System

Privilege

ROLE ADMIN

X_HNS_SYSADMIN

This roles  can administer HANA system, alter system parameters and execute ALTER  commands to change system

System

Privilege

INIFILE ADMIN

LICENSE ADMIN

LOG  ADMIN

SERVICE ADMIN

SESSION ADMIN

TRACE ADMIN

AUDIT ADMIN   

X_HNS_SYSMON

This role can  change alert, enable logging and view logs to monitor system

System

Privilege

CATALOG READ

MONITOR ADMIN

X_HNS_CONTENTADMIN

This role can  create, alter, import, export and drop content.

System

Privilege

CREATE SCENARIO

CREATE STRUCTURED PRIVILEGE

  1. REPO.EXPORT
  2. REPO.IMPORT
  3. REPO.MAINTAIN_DELIVERY_UNITS
  4. REPO.WORK_IN_FOREIGN_WORKSPACE

STUCTUREDPRIVILEGE ADMIN

X_HNS_DATAADMIN

This role can  create schema, import and export tables and drop tables

System

Privilege

CATALOG READ

CREATE REMOTE SOURCE

CREATE SCHEMA

IMPORT

EXPORT

Object Privilege Roles

X_HNO = O for Object Privilege Role

                                                                                                                                                                    

Role  

Name

Privilege

Type

Assigned

Privileges

X_HNO_CONTENT_READ

This role  give read access to activated views

Object

Privilege

_SYS_BI (SELECT, EXECUTE)

You would only need this _SYS_BIC (SELECT, EXECUTE) if you are using HANA studio to access views. Not using this for BI tools provides more security in terms of displaying activated views. Access to SYS_BIC will provide access to all activated views and therefore this model will be invalid. We can create separate role for this privilege

X_HNO_CONTENT_WRITE

This role  give write access for activated views and read access to schema

Object

Privilege

_SYS_BI (EXECUTE, SELECT, INSERT, UPDATE, DELETE)

_SYS_BIC (CREATE ANY, ALTER, DROP, EXECUTE, SELECT, INSERT, UPDATE,  DELETE, INDEX)

X_HNO_CONTENT_LIST

Object

Privilege

REPOSITORY_REST (EXECUTE)

X_HNO_SCHEMA_READ

Where SCHEMA can be changed with required SCHEMA name

Object

Privilege

SCHEMA (SELECT)

X_HNO_SCHEMA_WRITE

Where SCHEMA can be changed with required SCHEMA name

Object

Privilege

SCHEMA (CREATE ANY, ALTER, DROP, EXECUTE, SELECT,  INSERT, UPDATE, DELETE, INDEX)

X_HNO_FI_CONTENT

Object

Privilege

_SYS_BIC.FI Column Views

X_HNO_CO_CONTENT

Object

Privilege

_SYS_BIC.CO Column Views

X_HNO_IM_CONTENT

Object

Privilege

_SYS_BIC.IM Column Views

X_HNO_LE_CONTENT

Object

Privilege

_SYS_BIC.LE Column Views

X_HNO_MM_CONTENT

Object

Privilege

_SYS_BIC.MM Column Views

X_HNO_PA_CONTENT

Object Privilege

_SYS_BIC.PA Column Views

X_HNO_PU_CONTENT

Object Privilege

_SYS_BIC.PU Column Views

X_HNO_SD_CONTENT

Object Privilege

_SYS_BIC.SD Column Views

X_HNO_SP_CONTENT

Object Privilege

_SYS_BIC.SP Column Views

Package Privilege Roles

                                                                                                                                                         

Role  

Name

Privilege

Type

Assigned

Privileges

X_HNP_FI_READ

This role  give read access to Package FI

Package

Privilege

  1. REPO.READ on FI

X_HNP_IM_READ

This role  give read access to Package IM

Package

Privilege

  1. REPO.READ on IM

X_HNP_LE_READ

This role  give read access to Package LE

Package

Privilege

  1. REPO.READ on LE

X_HNP_MM_READ

This role  give read access to Package MM

Package

Privilege

  1. REPO.READ on MM

X_HNP_PP_READ

This role  give read access to Package PP

Package

Privilege

  1. REPO.READ on PP

X_HNP_PU_READ

This role  give read access to Package PU

Package

Privilege

  1. REPO.READ on PU

X_HNP_SD_READ

This role  give read access to Package SD

Package

Privilege

  1. REPO.READ on SD

X_HNP_SP_READ

This role  give read access to Package SP

Package

Privilege

  1. REPO.READ on SP

X_HNP_CO_READ

This role  give read access to Package CO

Package

Privilege

  1. REPO.READ on CO

X_HNP_PA_READ

This role  give read access to Package PA

Package

Privilege

  1. REPO.READ on PA

X_HNP_ROOT_WRITE

This role  give edit access to ALL Packages

Package

Privilege

  1. REPO.READ
  1. REPO.EDIT_NATIVE_OBJECTS
  1. REPO.ACTIVATE_NATIVE_OBJECTS
  1. REPO.MAINTAIN_NATIVE_PACKAGES

on ROOT

Analytic Privilege Roles

There can be many analytic privileges assigned to a role. For example: I am creating one single analytic privilege first and then create a role for department with this analytic privilege. In future, more analytic privileges can be added to it. In our case, we are not using analytic privileges which means no attribute restrictions

X_HND = D for Data level restriction

                                                                                                                                                                             

Analytic

Privilege

Package  

Content

Attributes

Restrictions

X_HND_CO_AP1

CO

column views under

_SYS_BIC.CO/

NA

X_HND_FI_AP1

FI

All column views under __SYS_BIC.FI/

NA

X_HND_IM _AP1

IM

column views under __SYS_BIC.IM/

NA

X_HND_LE _AP1

LE

column views under _SYS_BIC.LE/

NA

X_HND_MM _AP1

MM  

column views under _SYS_BIC.MM/

NA

X_HND_PP _AP1

PP

column views under _SYS_BIC.PP/

NA

X_HND_PA _AP1

PA

column views under __SYS_BIC.PA/

NA

X_HND_PU _AP1

  PU

column views under _SYS_BIC.PU/

NA

X_HND_SD _AP1

SD

column views under _SYS_BIC.SD/

NA

_SYS_BI_CP_ALL

ROOT

All column views

under  _SYS_BIC

No Restrictions.  Currently being used

Now the Analytic Roles

X_HNA = A for Analytic Privilege roles

                                                                                                           

Role

Name

Analytic

Privilege

X_HNA_FI

X_HND_FI_AP1

X_HNA_IM

X_HND_IM_AP1

X_HNA_LE

X_HND_LE_AP1

X_HNA_CO

X_HND_CO_AP1

X_HNA_MM

X_HND_MM_AP1

X_HNA_PU

X_HND_PU_AP1

X_HNA_PP

X_HND_PP_AP1

X_HNA_PA

X_HND_PA_AP1

X_HNA_SD

X_HND_SD _AP1

X_HNA_ALL

_SYS_BI_CP_ALL (This one is being used only)

Let’s take a look at how we can use system privilege roles to create technical roles:

Technical Roles

                                     

Role  

Name

Granted  Roles

Y_HNT_SECURTY

Add/delete/edit users and assign other roles

X_HNS_USERADMIN

X_HNS_ROLEADMIN

Y_HNT_ADMINS

Perform admin tasks and security tasks

X_HNS_USERADMIN

X_HNS_ROLEADMIN

X_HNS_SYSADMIN

X_HNS_SYSMON

X_HNS_CONTENTADMIN

X_HNS_DATAADMIN

Y_HNT_CONTENT_DEVS

Create and activate information models in packages

X_HNS_CONTENTADMIN

X_HNO_SCHEMA_READ

X_HNO_CONTENT_WRITE

X_HNO_CONTENT_LIST

X_HNP_ROOT_WRITE

X_HNA_ALL

Now, lets take a look at functional role example. In this example, Finance user A need access to FI package and it’s information views. So, in this case create a functional role for Finance department and add user A into it.

                 

Role  

Name

Granted  Roles

Y_HNF_FI

X_HNO_CONTENT_READ

X_HNO_FI_CONTENT

X_HNP_FI_READ

X_HNA_ALL

In the same way we can create other functional roles depending upon our requirements then assign them to user. Now, it is not mandatory that everyone follow this way to setup rule but it can be used as reference.

References

  1. 1.  SAP Hana Platform SPS6 Security Guide, 03rd September 2013, SAP HANA Security Guide, SAP Help Portal, http://help.sap.com/hana/SAP_HANA_Security_Guide_en.pdf
  2. 2.   Tomas Krojzl 2013, ‘SAP HANA – Security Concept and Data Architecture’, SAP Community Network – Tomas Krojzl’s Blog, 24 October 2011, viewed 20 October, 2013
To report this post you need to login first.

13 Comments

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

  1. Elke Zietlow

    Hi,

    let me point to some parts in your blog, I do not agree with.

    DATA ADMIN in role X_HNS_USERADMIN is NOT needed and is definitely a BAD
    idea.

        

    In X_HNS_ROLEADMIN you explicitly mention that role handling can be done
    with SQL commands.

    Where do you see the difference between this duty and the corresponding
    privileges and other roles, the duties concerned and its privileges?

    All privilege checks are done for SQL commands. And all duties are done
    using SQL commands.

       

    With the system privileges in X_HNS_SYSMON you can NOT enable auditing.

    And the system privilege CATALOG READ is needed for which task you mention
    in this role?

    I do not see the need to grant it to the role.

    And which tables (not created by you) can be dropped with X_HNS_DATAADMIN?

        

    Which schema is meant to be handled in X_HNO_SCHEMA_READ and
    X_HNO_SCHEMA_WRITE?

        

    Y_HNT_SECURTY: do you mean Y_HNT_SECURITY?

    Regards,

      Elke

    (0) 
    1. Angad Singh Post author

      Hi,

      Thanks for your comments and feedback. I have tried to answer your queries but please note answers may depend on use case. Our use case reflects the main requirement is to implement appropriate access to CONTENT (developed by developers) for business users. If I have missed the anything from HANA system administration point of view then I will update my blog after I have tested your suggestions.

      “DATA ADMIN in role X_HNS_USERADMIN is NOT needed and is definitely a BAD
      idea.”

      We use SQL stored procedures and SQL statements to manage users and roles sometimes. When I created this guide we were on SPS 06 Rev 66. At that time, we were not able to perform DDL SQL statements without “DATA ADMIN” privilege. Therefore, I added this privilege. We are now on SPS 07 and I successfully tested DDL statements without this  privilege. So you are right, we do not need this privilege. I have edited the blog.

         

       

      DATA ADMIN

       

       

      Authorizes reading all data in the system views. It also enables
        execution of any Data Definition Language (DDL) commands in the SAP HANA
        database

       

       

      A user having this privilege cannot select or change data stored
        tables for which they do not have access privileges, but they can drop tables
        or modify table definitions.

       

       

      “In X_HNS_ROLEADMIN you explicitly mention that role handling can be done
      with SQL commands.”

      Here, I meant to say that we (as mentioned earlier) use stored procedures (custom built) and SQL statements to manage users and roles. For example:
      create role X_HNO_MGC_PA_CONTENT;
      call GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT(‘SELECT’, ‘”_SYS_BIC”.”TEST”‘, ‘X_HNO_MGC_PA_CONTENT’);

      “With the system privileges in X_HNS_SYSMON you can NOT enable auditing.

      And the system privilege CATALOG READ is needed for which task you mention
      in this role?”

      I have edited the blog to replace auditing with logging. MONITOR ADMIN is used to  enable or disable events/alerts (untick/tick check IDs). We can also run ALTER SYSTEM commands for events/alerts

      Following is taken from HANA Studio. I hope this will help you understand why do we need CATALOG READ.

      31-03-2014 10-07-32 PM.png

      “And which tables (not created by you) can be dropped with X_HNS_DATAADMIN?”

      As you mentioned we can only drop tables created/imported by you. It will depend on scenario/use case where developer will need to create schema and then import tables into it. Also, they can drop the tables owned by them.

      “Which schema is meant to be handled in X_HNO_SCHEMA_READ and
      X_HNO_SCHEMA_WRITE?”

      This will depend on scenario/use case. For example, there is schema DSTEST which have tables and data is coming from data services. So role can be renamed to X_HNO_DSTEST_READ and users can be added to this role who needs read access to DSTEST

      I hope I have been able to answer your queries.

      Regards

      Angad

      (0) 
      1. Venkat B

        Hi! Angad,

          I was going through your blog about Security Role set up.  First of all thank you for posting  such detail and clear document.  In the document you mentioned that you developed or used Custom procedure to Control access to the specific schema activated views to Front end users. Would you able to share that procedure. We have similar requirement to control report user read access to specific schema.

        Appreciate your input /help

        Thanks

        Venkat

        (0) 
        1. Angad Singh Post author

          Hi Venkat,

          Thanks for the reply.

          I called inbuilt stored procedure in SQL statements. One of the example is as follows:

          create role X_HNO_XXX_CO_CONTENT;

          call GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT(‘SELECT’, ‘”_SYS_BIC”.”PACKAGENAME.FOLDERNAME/CV_COST_CENTER_ACTUAL_COST_ITEM_QUERY_ADHOC”‘, ‘X_HNO_XXX_CO_CONTENT’)

          These procedures are under schema “_SYS_REPO”.

          Regards

          Angad

          (0) 
          1. Venkat B

            Hi! Angad,

            Thank you very much for your response.

            I was able to grant privileges on each view one by one, but I was looking for to grant  Privilege  on whole Package name like ‘“_SYS_BIC”.”PACKAGENAME.FOLDERNAME” so that all the views/ activated content  under that package would be automatically accessed..

            Any of your suggestions are greatly appreciated.

            Thanks

            Venkat

            (0) 
            1. Angad Singh Post author

              Venkat,

              In that case, you would do following (please note this is as per our use case and we have HANA SPS07)

              > Allow users (via BI client) to browse to package which has content. In this example XXX is the package name.

              create role X_HNP_XXX_READ;

              grant REPO.READ on “XXX” to X_HNP_MGC_MGC_READ;

              if XXX has another package (folder) under it then your should use “XXX”.”FI” but if you give access to XXX then every package under it will be accessible by user.

              > Allow users (via BI client) to read activated content which resides in _SYS_BIC schema

              create role X_HNO_XXX_CONTENT_READ;

              call GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT (‘EXECUTE,SELECT’,’_SYS_BI’,’X_HNO_MGC_CONTENT_READ’);

              call GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT (‘EXECUTE,SELECT’,’_SYS_BIC’,’X_HNO_MGC_CONTENT_READ’);

              I hope this will help to achieve what you need.You can change the role name as per your need.

              Regards

              Angad

              (0) 
              1. Venkat B

                Hi! Angad,

                Thanks for the brisk response, I understand what  you explained.

                Yes, we have SP7

                Example :The activated views paths are

                “_SYS_BIC”.”HD22/CV_TEST1″

                “_SYS_BIC”.”HD22/CV_TEST2″

                Allow users (via BI client) to read activated content which is under _SYS_BIC.HD22

                When I try the following  the following, it gives access to all the views.

                call GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT (‘EXECUTE,SELECT’,’_SYS_BIC’, ‘X_CONTENT_DEV1’)

                I need to limit to the access to _SYS_BIC.HD22 ,  that means they can have access to CV_TEST1 and CV_TEST2.

                As you mentioned in your earlier response we can add one by one but that is a ongoing maintenance.

                I’m looking for somehting like the following which would work but the  syntax is incorrect.

                call GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT(‘SELECT’, ‘”_SYS_BIC”.”HD22/*” ‘, ‘X_CONTENT_DEV1’);

                I hope, I clarified the scenario.

                Thanks

                Venkat

                (0) 
                1. Angad Singh Post author

                  Hi Venkat,

                  I dont think you can use wild character in there (procedure call). Check the create statement of this procedure.

                  May I ask which BI client are you using? and how are you trying to connect to HANA?

                  If all the views that you require are in same package then giving just access to particular package should respect the security. It should not matter if user has SELECT & EXECUTE on _SYS_BIC

                  I know it is ongoing maintenance but if you manage it on role level then it can reduce some efforts.

                  Regards

                  Angad

                  (0) 
  2. Hari Putumbaca

    Hi Angad
    Can you please suggest how to setup Security model in case of SAP BO with SAP HANA.

    In BO with BW, Role are built in BW and synced to BO and can be used in BO by linking to the BO Groups.
    Do we have such option in the HANA roles too ? kindly suggest.

    (1) 

Leave a Reply