A step-by-step guide to create design-time (script based) Roles in SAP HANA

Introduction

Roles in SAP HANA are defined using the SAP HANA Studio. Roles defined in one environment –development, cannot be migrated to another. They have to be recreated manually in other environments – test, production etc. Manually creating and maintaining roles is viable if the numbers of roles required is minimal. If lot of roles are required manually creating and maintaining them across multiple servers in the landscape is tedious and error prone process.  The best way to create and maintain HANA roles irrespective of number of role, is using design-time (script based) roles. In this article I will be covering step by step process in creating HANA roles using scripts, which I have used in my recent client engagement.  Design-time role script is stored in a file with extension  .hdbrole

NOTE: This post is for XS Classic and  “Web-based Development Workbench” can be also be used.

1. Create Project

Log into your SAP HANA System/Studio.

Open SAP HANA Development perspective. This perspective will  show Project Explorer, Repositories and Systems tabs on the left.

Go  to File-New-Project…

Select  General-Project in Select a wizard window and click Next.

Enter Project Name  (rolesdemo) and click Next.

In the Project Reference window leave default selection and click Finish.

The created project will show up in the Project Explorer tab as shown below.

2. Create folders in project

Create folders under the project as given below. Folders give a package structure to the roles.

Rolesdemo-com-mycompany-security-common-roles.

3. Define Roles

Next step is to define the roles that are required.  The following table gives core roles that I will be creating. With this as guideline other roles can be developed.

4. Domain Specific Language (DSL) Syntax

The design-time roles are defined using  DSL keywords . The following table summarizes those key words. For details refer to Role Domain-Specific  Language Syntax – SAP HANA Developer Guide for SAP HANA Studio.

5. Create Design-time roles

The next step is to develop design-time roles using domain specific language. Switch to SAP HANA Development perspective if not already there.  I created a demo schema named ROLEDEMO in HANA database that will be used in the design-time role scripts.  You can use an existing schema on which you want the role to operate on.

6. _SYS_REPO on ROLEDEMO schema

Grant rights – SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, EXECUTE, TRIGGER, DEBUG, INDEX and CREATE ANY, to _SYS_REPO user with grant option.  (_SYS_REPO user  must have at the least SELECT right WITH GRANT OPTION to any new schema).

7. Role SR_ROLEDEMO_FULL

User with this role can create any database object and has read, write, update access to data in the ROLEDEMO schema.

SR prefix indicates this is a role in schema. FULL suffix indicates this role gives full access to the schema ROLEDEMO.

Code

catalog schema “ROLEDEMO “: CREATE ANY;
catalog schema  “ROLEDEMO “: SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, EXECUTE, INDEX, TRIGGER, DEBUG;

Open the Role editor by navigating to File-New-Other-Database Development-Role. In the New Role window the container will be the package /rolesdemo/com/mycompany/security/common/roles . Enter Role Name as SR_ROLEDEMO_FULL  and click Finish

The role editor opens up with code

role rolesdemo.com.mycompany.security.common.roles::SR_ROLEDEMO_FULL {

}

Now enter the two lines of code given below (also give above under “Code” paragraph) and save.

role rolesdemo.com.mycompany.security.common.roles::SR_ROLEDEMO_FULL {

catalog schema  “ROLEDEMO”: SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, EXECUTE, INDEX, TRIGGER, DEBUG;
}

Go to Project Explorer. Right click on the role SR_ROLEDEMO_FULL.hdbrole and validate.

Validation should pass as shown below.

Next step is to activate the design-time role SR_ROLEDEMO_FULL. To do this the project “rolesdemo” created in the begining  need to be added to a repository.  From the Project Explorer tab, right click on the “rolesdemo” package, select Team – Share Project.  In the Share Project window select a Repository workspace, select a  repository workspace.  Make sure to check the Add Project Folder as Subpackage check box as shown below.

In the Repositories tab the rolesdemo package should appear as shown below.

8. Role Activation

Right click on the role SR_ROLEDEMO_FULL.hdbrole. Select Activate from the popup menu. “Activation Repository Content” window will show up. After few seconds if any error occurs it will be displayed.  Otherwise “Activation Repository Content” window will close.  A P will be placed on the successfully activated object.

After activation navigate to System tab-Security-Roles. The activated role SR_ROLEDEMO_FULL must be listed there.

9. Testing the design-time role

Create table

Create a test table using the script below in ROLEDEMO schema

CREATE COLUMN TABLE “ROLEDEMO”.”DEMO1″ (“ID” INTEGER CS_INT,
“FIRST_NAME” VARCHAR(50),
“MI” VARCHAR(1),
“LAST_NAME” VARCHAR(50),
“GENDER” VARCHAR(1),
“DOB” DAYDATE CS_DAYDATE) UNLOAD PRIORITY 5 AUTO MERGE

Create User and Login

create user DT_TEST_USER password Password12345;

NOTE: You need Create User right or HANA administrator help to create a user

Login as DT_TEST_USER and open SQL editor with DT_TEST_USER connection.

Test DT_TEST_USER’s select rights on ROLEDEMO schema

At this point the user DT_TEST_USER does not have any rights on the ROLEDEMO schema. In the SQL editor enter the statement , Select * from “ROLEDEMO”.”DEMO1”;  and execute it.  Since the DT_TEST_USER does not have any privilege on ROLEDEMO schema, error message will be shown as given below.

Assign SR_ROLEDEMO_FULL to DT_TEST_USER

The SR_ROLEDEMO_FULL role created can be assigned to the user DT_TEST_USER  by a call to stored procedure GRANT_ACTIVATED_ROLE as given in the script below.

Call PUBLIC.GRANT_ACTIVATED_ROLE(‘rolesdemo.com.mycompany.security.common.roles::SR_ROLEDEMO_FULL, ‘DT_TEST_USER’);

Note that the first parameter is the role name with full path.  Second parameter is user name to whom the role is assigned.  The procedure call should run successfully. Refer below.

NOTE:  Appropriate Right to call the GRANT_ACTIVATED_ROLE is required to call the stored procedure.

Navigate to USER and open up the DT_TEST_USER .  In Granted Roles Tab rolesdemo.com.mycompany.security.common.roles::SR_ROLEDEMO_FULL should be listed, granted by _SYS_REPO (roles granted using stored procedure –PUBLIC.GRANT_ACTIVATED_ROLE, call).

Now run the script Select  * from ROLEDEMO.DEMO1 and check the result set in the Results tab.  Since the user has the SR_ROLEDEMO_FULL rights which allows the user to “Select” from ROLEDEMO schema the SQL will execute without any error returning a result set as shown in the following two screenshots.

10. Other Roles

The design-time scripts  for other roles is given below.  Following the above steps these roles can be created, assigned to users and tested.

Catalog Schema READ – SR_ROLEDEMO_READ

This role allows only ready access (Select) right to the user  on the schema ROLEDEMO

role rolesdemo.com.mycompany.security.common.roles::SR_ROLEDEMO_READ {

catalog schema “ROLEDEMO”: SELECT; –Run “Select” statements against ROLEDEMO Schema

}

HANA Developer-HANA_MODELER

This role allows the user to create, modify, delete, preview HANA Models. This user must have ability

• to access Content folder (to create models)
• to access ROLEDEMO schema tables
• to create models
• to modify models
• to activate models

The following script achieves this.

role rolesdemo.com.mycompany.security.common.roles::HANA_MODELER

{

catalog sql object “SYS”.”REPOSITORY_REST”: EXECUTE; – to access Content folder in HANA
catalog schema “ROLEDEMO”: SELECT;– to select tables
package com.rolesdemo: REPO.READ;  – to read com.rolesdemo package to create models
package com.rolesdemo: REPO.EDIT_NATIVE_OBJECTS; – create models
package com.rolesdemo: REPO.ACTIVATE_NATIVE_OBJECTS; – Activate models
package com.rolesdemo: REPO.MAINTAIN_NATIVE_PACKAGES; – Maintain com.rolesdemo                                                                                                                     package

}

Since there is a separate role to read ROLEDEMO schema SR_ROLEDEMO_READ the above code can extend this role as given below and include ability to read ROLEDEMO schema

role rolesdemo.com.mycompany.security.common.roles::HANA_MODELER extends role rolesdemo.com.mycompany.security.common.roles::SR_ROLEDEMO_READ

{

catalog sql object “SYS”.”REPOSITORY_REST”: EXECUTE;
package com.rolesdemo: REPO.READ;
package com.rolesdemo: REPO.EDIT_NATIVE_OBJECTS;
package com.rolesdemo: REPO.ACTIVATE_NATIVE_OBJECTS;
package com.rolesdemo: REPO.MAINTAIN_NATIVE_PACKAGES;

}

Content Transporter-RR_CONTENT_TRANSPORT

This role allows the user to create delivery units, export and import them.

role rolesdemo.com.mycompany.security.common.roles::RR_CONTENT_TRANSPORT {
catalog sql object “SYS”.”REPOSITORY_REST”: EXECUTE;
package com.rolesdemo: REPO.READ; — Needed to access the package to create delivery units
package com.rolesdemo: REPO.MAINTAIN_NATIVE_PACKAGES; –to add,delete packages to delivery units
package com.rolesdemo: REPO.MAINTAIN_IMPORTED_PACKAGES;–to manage imported packages
system privilege: REPO.MAINTAIN_DELIVERY_UNITS; – Create, Delete, Alter Delivery Units
system privilege: REPO.EXPORT; – Export Delivery units
system privilege: REPO.IMPORT; – Import Delivery units
}

Note that the first 4 lines are same from RR_ROLESDEMO_PACKAGE_FULL_ACCESS. The difference between this role and HANA_MODELER role is that this role does not have access to the schema tables of the model.  This role only allows to view packages, create delivery units on the packages, export/import them.

Front End User-RR_FRONT_END_USER

Access HANA only from front end tools like BO, LUMIRA etc.  (Not via SAP HANA Studio).  When accessing using front end tools access only to _SYS_BIC schema is given, where colum views of the activated models are created.  So the front end user need access to _SYS_BIC schema.

role rolesdemo.com.mycompany.security.common.roles::RR_FRONT_END_USER extends catalog role “RESTRICTED_USER_JDBC_ACCESS”, “RESTRICTED_USER_ODBC_ACCESS” {

catalog schema “_SYS_BI”: SELECT;   – Needed to preview _SYS_BIC Views.
catalog schema “_SYS_BI”: EXECUTE;  – Needed to execute stored procedures if any
catalog analytic privilege: “_SYS_BI_CP_ALL”; – Needed to preview model views

}

• Notice that this role extends two catalog roles:
  • RESTRICTED_USER_JDBC_ACCESS – access HANA using JDBC
  • RESTRICTED_USER_ODBC_ACCESS-access HANA using ODBC
• NOTE: This role only to be assigned to restricted users.

User Admin-RR_USER_ADMIN

Create, manage, delete users. Assign roles to the created user.

role rolesdemo.com.mycompany.security.common.roles::RR_USER_ADMIN {

system privilege: USER ADMIN; – Create HANA users
catalog sql object “PUBLIC”.”GRANT_ACTIVATED_ROLE”: EXECUTE; – Grant ROLES
catalog sql object “PUBLIC”.”REVOKE_ACTIVATED_ROLE”: EXECUTE; – Revoke ROLES

}

After creating and activating all the roles, they must be listed in Schema-Security-Roles. Navigate to Security-Roles. Right click on Roles and select  Filters.. and enter  rolesdemo  (no enclosing characters ). All the roles created will be listed as in the screenshot below.

11. Granting & Revoking Roles

As mentioned before in the earlier section the roles can be granted and revoked by the procedure calls

• GRANT_ACTIVATED_ROLE(‘role’,’user’);
• REVOKE_ACTIVATED_ROLE(‘role’,’user’);

Multiple roles can be assigned to a single user if required. For example user admin and content transport roles can be assigned to one user by calling granting procedure for each role assignment.  For example the following procedure calls assigns RR_USER_ADMIN and RR_CONTENT_TRANSPORT roles to USER1.

• call PUBLIC.GRANT_ACTIVATED_ROLE(‘rolesdemo.com.mycompany.security.common.roles::RR_USER_ADMIN’,’USER1’); – Grant User Admin role to USER1
• call PUBLIC.GRANT_ACTIVATED_ROLE(‘rolesdemo.com.mycompany.security.common.roles::RR_CONTENT_TRANSPORT,’USER1’); – Grant Content Admin to USER1

Now USER1 will be able to create, delete, manage users and also will be able to create, delete, manage delivery units.

12. Migrating Roles

Migrating design time roles to other environments is similar to migrating HANA models using delivery units.

Go to SAP HANA Modeler perspective.
Go to Help – Quick Launch. In the Welcome to Modeler page, click Delivery Units.

Create Delivery Unit named ROLES_DEMO  and select it. Click the Add.. button and assign rolesdemo package which contains all the design-time roles.  Refer to the screen shot  below.

The roles can be migrated to other environment by exporting the delivery unit from the source environment.

• Go to File- Export-SAP HANA Content-Delivery Unit-Next
• Select the source system-Next
• Select the Delivery Unit – ROLES_DEMO, which was defined above. For
  • Export location – select Export to Client
  • Location: – C: emp
  • File Name: Leave the default name generated.
  • Click Next
  • Review the information and finish.

A .tgz file – REPO_YYYYMMDD-HHMMSS-HANASYSTEMNAME-ROLES_DEMO.tgz file will be created in the folder location specified. – For example C:Temp

Now to import the roles to target environment,

• Go to File-Import-SAP HANA Content-Delivery Unit-Next
• Select the Target System-Next
• Select File – Client – C: emp REPO_YYYYMMDD-HHMMSS-HANASYSTEMNAME-ROLES_DEMO.tgz

Once the file is selected, in the Object Import Simulation window, all the roles in the .tgz file (delivery unit ) will be displayed with Status of the roles as shown below.

Under Actions

Select  Overwrite inactive versions and Activate objects  .

Click Finish

The roles must be imported and activated successfully in the target system and can be granted to the users in the target system.

13. Conclusion

Hope this article gave an insight on how design-time roles (script based roles) save substantial time by defining roles in one system (development) and migrating it to other systems (QA, INTEGRATION and PRODUCTION) by exporting and importing delivery units, avoiding repeated process of manually creating roles in each system and difficulty in synchronizing the changes in roles across multiple systems.  I have tried to keep this article minimal and addressed only the core steps.

14. References

• Role Domain-Specific Language Syntax – SAP HANA Developer Guide for SAP HANA Studio
• How To…Define Standard Roles for Administrator and Developers in SAP HANA By Richard Bremer  System Privilege Reference
• SAP HANA Administration Guide
• SAP HANA Developers Guide

Author:

Pals Nagaraj, PMP, CMC is a Technology/Management consultant with extensive experience in providing Business Analytics Solutions using in SAP BI, ETL and Analytics platforms to federal and commercial clients. He is certified in SAP BI and SAP HANA.  He can be reached at [email protected]