Skip to Content

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

Caution:

Only owner (data owner user) should grant SQL privileges. Granting privileges via other user (that has proper grant privilege) is not recommended because all granted privileges are being automatically revoked at the moment when this user is deleted or when his authorizations were removed.

Caution:

Every data owner must grant select privilege with grant option for his schema to technical user _SYS_REPO. Without this step queries to all dependent views will end up with following error: “invalidated view: package/VIEW”. See SAP Note 1612696 – “Invalidated view” error when using analytical views for more information.

Comment:

Of course it is possible to grant access on object level (tables, indexes and views) instead of schema level but this should be done in exceptional cases only. Object level security would cause increased implementation and maintenance effort and low transparency of security concept.

9.2 Elementary roles – Analytic privileges

For each defined analytic privilege(s) within each package we should define separate role. In our example we are having only two packages having analytic privileges therefore we will have following roles:

  • role ANALYTIC_HR_USA
  • role ANALYTIC_HR_CANADA
  • role ANALYTIC_HR_EMEA

These roles will be required for selecting corresponding subsets of data from HR package.

We will also create role for ANALYTIC_TESTING_ALL privilege:

  • role ANALYTIC_TESTING

Caution:

Having analytic privilege is not enough to read the data. Role also must contain SELECT privilege for corresponding automatically generated objects in schema _SYS_BIC.

9.3 Elementary roles – Package privileges

To separate developers based on projects and activities we will create following roles for each defined package:

  • role PACKAGE_*_READ having privilege REPO.READ
  • role PACKAGE_*_EDIT having privilege REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS, REPO.MAINTAIN_NATIVE_PACKAGES, REPO.EDIT_IMPORTED_OBJECTS, REPO.ACTIVATE_IMPORTED_OBJECTS, REPO.MAINTAIN_IMPORTED_PACKAGES

This will result in following roles:

  • role PACKAGE_TESTING_READ
  • role PACKAGE_TESTING_EDIT
  • role PACKAGE_COPA_READ
  • role PACKAGE_COPA_EDIT
  • role PACKAGE_HR_READ
  • role PACKAGE_HR_EDIT

9.4 Elementary roles – System privileges

System privileges are predefined internal authorizations that should not be assigned to regular and application users.

These privileges are intended to create different types of administrative users.

Following administrative roles can be designed (examples):

  • role ADMIN_USER having privilege USER ADMIN, ROLE ADMIN, CREATE SCHEMA
  • role ADMIN_AUDIT having privilege AUDIT ADMIN
  • role ADMIN_BACKUP having privilege BACKUP ADMIN
  • role ADMIN_MONITOR having privilege MONITOR ADMIN
  • role ADMIN_SYSTEM having privilege BACKUP ADMIN, MONITOR ADMIN, SERVICE ADMIN, SESSION ADMIN, TRACE ADMIN

Consideration:

Biggest issue in HANA is that only grantor can revoke the privilege. Problem will arise in case that company will have employee working in HANA security who will grant a lot of roles and then he will leave. In such case his work has to be redone. Same issue happens when such user is on vacation and some role needs to be revoked. Such action is currently not possible. Conclusion of this “feature” is my recommendation to have one security user that is shared between very few individuals. Of course this is making whole security concept vulnerable.

9.5 Special roles

You will also need special roles. For example modeler will need to access list of packages, he will need to activate objects, etc.

Package roles are controlling who can see and edit objects in package. However during object activation there are internal objects created in schema _SYS_BIC. Without proper authorization against this schema you cannot activate objects.

We will create new role having all required privileges for working with packages and analytical objects.

  • role TECHNICAL_PACKAGES

To allow listing all defined packages you need to grant following privilege:

     GRANT EXECUTE ON REPOSITORY_REST TO TECHNICAL_PACKAGES

To enable modelers to activate objects you need to grant them create privileges:

     GRANT CREATE ANY ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES

To allow them to re-active objects they need to be able to drop objects:

     GRANT DROP ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES

To enable creation of calculation scenarios and cubes you need to grant following system privilege:

     GRANT CREATE SCENARIO TO TECHNICAL_PACKAGES

Modelers will have authorization to create new privilege but not to activate it or assign to any role (which is very good idea otherwise user can grant himself any authorization).

In case you wish to grant them authorization to activate you need to grant them system privilege CREATE STRUCTURED PRIVILEGE and STRUCTUREDPRIVILEGE ADMIN.

9.6 Composite roles

Based on your needs you can combine elementary roles into bigger logical units. For example we can design role for modelers to give them appropriate privileges into testing schema and package for testing.

  • role COMPOSITE_TESTING

This role will contain following elementary roles:

  • DATA_BODS_TESTING_READ
  • DATA_MAN_TESTING_READ
  • DATA_MAN_TESTING_WRITE
  • DATA_MAN_TESTING_DDL
  • PACKAGE_TESTING_READ
  • PACKAGE_TESTING_EDIT
  • TECHNICAL_PACKAGES
  • ANALYTIC_TESTING

Note: Notice that we intentionally grant only READ access into schema DATA_BODS_TESTING as objects in this schema should not be created manually but exclusively from SAP BusinessObjects Data Services.

10 Deleting objects

Be extremely careful when you delete object from HANA database. I suggest never using CASCADE option because important objects might be deleted. But be careful because during some delete operations (like deleting schema) cascade is always used.

In case you will try to delete user with dependent objects you will have following error:

hdbsql HDB=> DROP USER U_KROJZL

* 417: can’t drop without CASCADE specification: U_KROJZL: line 1 col 11 (at pos 10) SQLSTATE: HY000

In such case you should check view “SYS”.”OWNERSHIP” to list objects owned by this user.

     SELECT * FROM “SYS”.”OWNERSHIP” WHERE OWNER_NAME=’U_KROJZL’

Same way other objects can be dependent on these objects. To identify them execute select statement against view “SYS”.”OBJECT_DEPENDENCIES”.

Unfortunately there is nothing you can do – I am not aware about possibility to transfer ownership.

11 Using SQL

For creating just few users and roles you might use HANA studio. But in case you will need to create more users it is more convenient to use SQL interface.

Below you can see example how to create part of our configuration.

First manually create package prj-testing using user SYSTEM

/wp-content/uploads/2011/10/package_99890.png

Then create analytic privilege ANALYTIC_TESTING_ALL still using user SYSTEM.

/wp-content/uploads/2011/10/analytic1_99891.png

Click Finish (no need to add objects).

/wp-content/uploads/2011/10/analytic2_99893.png

Check option “Applicable to all Information Models” (be sure to fully understand impact of this adjustment) and click Save. Activate object.

Launch SQL interface.

vhost0479:~ # su – hdbadm

vhost0479:/usr/sap/HDB/HDB00> which hdbsql

/usr/sap/HDB/HDB00/exe/hdbsql

vhost0479:/usr/sap/HDB/HDB00> hdbsql

Connect as user SYSTEM.

Note: I would recommend using user SYSTEM (or specifically created user) to create and grant all security related objects (except SQL privileges where owner should grant them). Reason for this rule is fact that privileges are removed if user who granted them is deleted or if corresponding privilege is removed for this user.

Connect as user SYSTEM and execute SQL commands:

hdbsql=> \c -i 00 -u SYSTEM -p <password>

Connected to HDB@vhost0479:30015

CREATE USER DATA_MAN_TESTING IDENTIFIED BY Temp1234

CREATE USER DATA_BODS_TESTING IDENTIFIED BY Temp1234

CREATE USER U_KROJZL IDENTIFIED BY Temp1234

DROP SCHEMA U_KROJZL

CREATE USER A_BODS_TESTING IDENTIFIED BY Temp1234

DROP SCHEMA A_BODS_TESTING

CREATE ROLE DATA_BODS_TESTING_READ

CREATE ROLE DATA_BODS_TESTING_WRITE

CREATE ROLE DATA_BODS_TESTING_DDL

CREATE ROLE DATA_MAN_TESTING_READ

CREATE ROLE DATA_MAN_TESTING_WRITE

CREATE ROLE DATA_MAN_TESTING_DDL

CREATE ROLE PACKAGE_TESTING_READ

CREATE ROLE PACKAGE_TESTING_EDIT

CREATE ROLE TECHNICAL_PACKAGES

CREATE ROLE ANALYTIC_TESTING

CREATE ROLE COMPOSITE_TESTING

GRANT DATA_BODS_TESTING_READ TO COMPOSITE_TESTING

GRANT DATA_MAN_TESTING_READ TO COMPOSITE_TESTING

GRANT DATA_MAN_TESTING_WRITE TO COMPOSITE_TESTING

GRANT DATA_MAN_TESTING_DDL TO COMPOSITE_TESTING

GRANT PACKAGE_TESTING_READ TO COMPOSITE_TESTING

GRANT PACKAGE_TESTING_EDIT TO COMPOSITE_TESTING

GRANT TECHNICAL_PACKAGES TO COMPOSITE_TESTING

GRANT ANALYTIC_TESTING TO COMPOSITE_TESTING

GRANT REPO.READ ON “prj-testing” TO PACKAGE_TESTING_READ

GRANT REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS, REPO.MAINTAIN_NATIVE_PACKAGES, REPO.EDIT_IMPORTED_OBJECTS, REPO.ACTIVATE_IMPORTED_OBJECTS, REPO.MAINTAIN_IMPORTED_PACKAGES ON “prj-testing” TO PACKAGE_TESTING_EDIT

GRANT EXECUTE ON REPOSITORY_REST TO TECHNICAL_PACKAGES

GRANT CREATE ANY ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES

GRANT DROP ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES

GRANT CREATE SCENARIO TO TECHNICAL_PACKAGES

GRANT SELECT ON SCHEMA _SYS_BIC TO ANALYTIC_TESTING

GRANT COMPOSITE_TESTING TO U_KROJZL

Also create required roles for few power users:

CREATE ROLE ADMIN_BACKUP

GRANT BACKUP ADMIN TO ADMIN_BACKUP

GRANT ADMIN_BACKUP TO U_KROJZL

Now connect as data owner user and finish the security setup:

hdbsql HDB=> \c -i 00 -u DATA_BODS_TESTING -p Temp1234

Connected to HDB@vhost0479:30015

ALTER USER DATA_BODS_TESTING IDENTIFIED BY <new password>

GRANT SELECT ON SCHEMA DATA_BODS_TESTING TO DATA_BODS_TESTING_READ

GRANT INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA DATA_BODS_TESTING TO DATA_BODS_TESTING_WRITE

GRANT CREATE ANY, ALTER, DROP, INDEX ON SCHEMA DATA_BODS_TESTING TO DATA_BODS_TESTING_DDL

GRANT SELECT ON SCHEMA DATA_BODS_TESTING TO _SYS_REPO WITH GRANT OPTION

Repeat same for second data owner:

hdbsql HDB=> \c -i 00 -u DATA_MAN_TESTING -p Temp1234

Connected to HDB@vhost0479:30015

ALTER USER DATA_MAN_TESTING IDENTIFIED BY <new password>

GRANT SELECT ON SCHEMA DATA_MAN_TESTING TO DATA_MAN_TESTING_READ

GRANT INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA DATA_MAN_TESTING TO DATA_MAN_TESTING_WRITE

GRANT CREATE ANY ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES

GRANT SELECT ON SCHEMA DATA_MAN_TESTING TO _SYS_REPO WITH GRANT OPTION

If required then after creation of objects in package you can define custom-tailored analytic privileges.

It should be possible to add privileges to role using following SQL syntax but it seems that SQL interpreter is having issue executing the command.

hdbsql HDB=> CALL _SYS_REPO.GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE (‘”prj-testing/ANALYTIC_TESTING_ALL”‘, ‘ANALYTIC_TESTING’)

* 292: wrong number of arguments: GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE must have three parameters SQLSTATE: 07001

Add privilege to the role manually (using user SYSTEM):

/wp-content/uploads/2011/10/analytic3_99899.png

Save.

To report this post you need to login first.

10 Comments

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

  1. Kiran Musunuru
    Tom ,

    Good level of detail in security concepts. I have also implemented security in my project which went live last week . I have initially struggled a lot as there was no documentaion ( i wish u have written this blog 1 month back 🙂 . I had to learn all this in a hard way ie trail & error ) & also there is no concept of schemas having passwords ( which makes more painful). Also , when we create public synonyms they are created under system . By any chance, do u know how to recompile procedures without dropping them . In oracle, we have create or replace which takes care without  dropping & re executing the grants

    Nice blog 🙂

    Thanks,
    Kiran .

    (0) 
    1. Tomas Krojzl Post author
      Hello,

      thank you for your feedback.

      “Schemas with password” can be implemented by creating dedicated users – data owners (if this is what you had in mind). These users will be owners of this schema and will be able grant privileges. Important to note is that schema owner will not automatically be owner of all objects within given schema. Unfortunately this feature can cause some unexpected behavior.

      Regarding the stored procedures in SAP HANA – you can create them using SQL inside any schema or using studio inside package (this stored procedure is then located in _SYS_BIC schema). In case you need to change definition of stored procedure you need to drop it and create again. Same thing happens when you reactivate stored procedure via studio. Therefore I guess that there is no way how to update procedure definition without dropping it as part of the process.

      If you need to address security on object level (to grant execute privilege only for specific procedure) and if you expect that this procedure will be updated – then I would suggest following. Define new role for each stored procedure or group of stored procedures and grant it with appropriate privileges. This role can be granted to other roles or users instead of privilege itself. Advantage of this configuration is that after recreation of procedure you need to do just one grant statement against this statement.

      I hope it helped.

      Tomas

      (0) 
    1. Tomas Krojzl Post author

      Hello,

      some time ago I wrote stored procedure doing copy of a user – however it has it’s own limits – only some of roles will be copied (only those that were granted by used doing copy)…

      Here is the “old” code – it will need some rework since it was designed for old revision on SP02:

      CREATE PROCEDURE SYSTEM.COPY_USER

        (IN V_SOURCE_USER NVARCHAR (256),

         IN V_TARGET_USER NVARCHAR (256),

         IN V_TARGET_PWD NVARCHAR (256)

        )

      LANGUAGE SQLSCRIPT AS

        V_FOUND INT := 1;

        CURSOR C_ROLE_LIST (V_SRC_USER NVARCHAR (256)) FOR

          SELECT ROLE_NAME, IS_GRANTABLE FROM “SYS”.”GRANTED_ROLES”

          WHERE GRANTEE=:V_SRC_USER AND GRANTEE_TYPE=’USER’ ANDGRANTOR=CURRENT_USER;

      BEGIN

        SELECT COUNT(*) INTO V_FOUND FROM “SYS”.”USERS” WHERE USER_NAME = :V_SOURCE_USER;

        IF :V_FOUND = 1 THEN

          EXEC ‘CREATE USER ‘ || :V_TARGET_USER || ‘ IDENTIFIED BY ‘ || :V_TARGET_PWD;

          FOR V_LIST_ROW AS C_ROLE_LIST(:V_SOURCE_USER) DO

            IF V_LIST_ROW.IS_GRANTABLE=’FALSE’ THEN

              EXEC ‘GRANT ‘ || V_LIST_ROW.ROLE_NAME || ‘ TO ‘ || :V_TARGET_USER;

            ELSE

              EXEC ‘GRANT ‘ || V_LIST_ROW.ROLE_NAME || ‘ TO ‘ || :V_TARGET_USER || ‘ WITH ADMIN OPTION’;

            END IF;

          END FOR;

        END IF;

      END

      (0) 
      1. Tomas Krojzl Post author

        Hello,

        here is new (updated) code – tested on revision 32:

        DROP PROCEDURE SYSTEM.COPY_USER;

        CREATE PROCEDURE SYSTEM.COPY_USER

          (IN V_SOURCE_USER NVARCHAR (256),

           IN V_TARGET_USER NVARCHAR (256),

           IN V_TARGET_PWD NVARCHAR (256)

          )

        LANGUAGE SQLSCRIPT AS

          V_FOUND INT := 1;

          CURSOR C_ROLE_LIST (V_SRC_USER NVARCHAR (256)) FOR

            SELECT ROLE_NAME, IS_GRANTABLE FROM “SYS”.”GRANTED_ROLES”

            WHERE GRANTEE=:V_SRC_USER AND GRANTEE_TYPE=’USER’ AND GRANTOR=CURRENT_USER;

        BEGIN

          SELECT COUNT(*) INTO V_FOUND FROM “SYS”.”USERS” WHERE USER_NAME = :V_SOURCE_USER;

          IF :V_FOUND = 1 THEN

            EXEC ‘CREATE USER ‘ || :V_TARGET_USER || ‘ PASSWORD ‘ || :V_TARGET_PWD;

            FOR V_LIST_ROW AS C_ROLE_LIST(:V_SOURCE_USER) DO

              IF V_LIST_ROW.IS_GRANTABLE=’FALSE’ THEN

                EXEC ‘GRANT ‘ || V_LIST_ROW.ROLE_NAME || ‘ TO ‘ || :V_TARGET_USER;

              ELSE

                EXEC ‘GRANT ‘ || V_LIST_ROW.ROLE_NAME || ‘ TO ‘ || :V_TARGET_USER || ‘ WITH ADMIN OPTION’;

              END IF;

            END FOR;

          END IF;

        END;

        You can then call copy operation using following statement:

        CALL SYSTEM.COPY_USER (‘U_TOMAS’, ‘U_TOMAS2’, ‘Init1234’);

        Note: This script will copy only roles granted by user who created the procedure. If you want to change behaviour to copy roles granted by call of the procedure add “SQL SECURITY INVOKER” into procedure definition.

        You might also consider extending the script to cover more then just roles…

        Tomas

        (0) 
  2. Gareth Martin

    Hi Tomas

    Very useful blog, many thanks.  I had a question around somehow implementing table based security.  In our project we wish to grant authorizations for 2000 users based on a table managed in the ECC system which indicates the employee number (which is also the user name) and the Org Unit for which they have access.  This then grants access all the way down the hierarchy to information for those Org Units.  We would want to do the same for controlling data access across several schemas to control all data accessed by these users.

    At this point is this possible using a variation of the commands you use above and a loop at that user table from the source system?

    Regards

    Gareth

    (0) 
    1. Tomas Krojzl Post author

      Hello Gareth,

      I am not sure I understand… you wish to grant access to 2000 developers? I am not sure that all those users really need access to HANA tables. Or maybe you have 2000 users defined in BOBJ and you are reusing BOBJ credentials – is this your case?

      If yes and you would like to keep it this way then I would probably create role for every org.unit with proper privileges (manual process because each role will have probably different set of tables/privileges) and then synchronize the users with roles based on the ECC tables you mentioned.

      You can replicate that table into SAP HANA and then code custom stored procedure to adjust role assignments (some clever SQL can find differences between org.unit assignments and role assignments and then grant/revoke what is wrong).

      However bear in mind that such setup will be very restrictive and not suitable for development environment…

      Alternative to your approach is to use fixed HANA users in universe definition for given BOBJ dashboard/report (or set of dashboards/reports) and then grant them only tables they need for proper processing of the dashboard/report. Security then needs to be addressed on BOBJ level – however it will never happen that you have access to BOBJ dashboard/report but not to the underlying HANA tables.

      Tomas

      (0) 
      1. Robert LIGHTFOOT

        Hi Tomas,

        With regard to SLT, I am aware that the SLT initial connection in LTR creates the user for the specific schema and the 3 roles related to that schema. However, can you assign the privileges for the SLT schema owner to SYSTEM without having to stop the replication, change the SLT user password and use the SLT User account to assign after creating the connection? This appears to not be possible.

        Many thanks

        Rob

        (0) 
  3. Tim Korba

    Tomas,  It seems that you can now delete users without the cascade option?  It seems like you have 2 options now, restricted and cascade options when deleting end users within HANA studio.  Does this mean that we won’t lose all of the objects and the associated granted rights with them?  And does this mean that it is best practices to review the objects and then delete as required.  Also, not sure the impact of granting future rights to those objects or would it be a best practice to grant all rights to SYSTEM at promotion of those objects from DEV to QA or PRD?  Let me know your thoughts.

    (0) 
  4. Manoj Amin

    Hana is asking to provide authorization to all system geenerated column view under _SYS_BIC. There are so many dependent objects which is access via role.

    1. Why hana is checking such authorization when view level authorization is already provided ?
    2.  How to provide correct access without providing full SELECT access to _SYS_BIC OR Listing each generated objects ?. _SYS_BIC contains too many system generated views and providing full select option would be too much access. Providing access to each generated objects seems to be over killing as well.
    (0) 

Leave a Reply