Authorization of User Privilege on SAP HANA Modeler Development

[Former Member]

Introduction

Modeler, including attribute view, Analytic view and Calculation view, plays a very important part in SAP HANA. Develop with modelers can not only delivers business knowledge to the developers better but also accelerates application’s performance.

Many readers should have understanding in modeler’s concept and also know how to create modelers with Administrator user to preview and analysis data. While in real develop environment, this kind of user has too much privilege and is strictly limited to use. Now the question is which privileges should be granted to the normal user for modeler development.

In the following part, I’ll show how to realize it step by step.

Create User

First, we create a new user called “REPOUSER”. SAP HANA will grant PUBLIC privilege to every new creating user. Connecting the user via SAP HANA Studio, it shows the initial content. SAP HANA assigns a homonymic schema to this user and the user has all the privilege to access and modify the schema.

Grant privilege to package

Create a new package “repo” with “SYSTEM” user. When you open content folder first, it warns that “execute on repository_rest” privilege   is missing.

Execute following sql command with “SYSTEM” user:

GRANT EXECUTE ON REPOSITORY_REST to REPOUSER;

Now the folder is opened but the content is still invisible, execute flowing commands with “SYSTEM” user then:

GRANT REPO.READ ON _SYS_REPO."repo" TO REPOUSER;

GRANT REPO.EDIT_NATIVE_OBJECTS ON _SYS_REPO."repo" TO REPOUSER;

GRANT REPO.ACTIVATE_NATIVE_OBJECTS ON _SYS_REPO."repo" TO REPOUSER;

GRANT REPO.MAINTAIN_NATIVE_PACKAGES ON _SYS_REPO."repo" TO REPOUSER;

Now, “REPOUSER” user could access “repo” package and creating/editing modelers under it.

Creating modelers

Here I take an analytic view for example. First, we create an analytic named “SALES_BY_REGION”, since the used tables consist in schema “FURNITURE”, user “REPOUSER” should have the select privilege on this schema. Execute the sql command with SYSTEM user.

GRANT SELECT ON SCHEMA "FURNITURE" TO REPOUSER;

After the modeler is created, it needs validation and activation. In SAP HANA, all the existing modelers are managed by user “_SYS_REPO”, so the user needs select privilege on all used tables in modelers. If error occurs during validation and activation, we should grant select privilege on related schema to user “_SYS_REPO” with this command:

GRANT SELECT ON SCHEMA FURNITURE TO _SYS_REPO WITH GRANT OPTION;

Now, the modeler has been successfully validated and activated, data could be previewed. SAP HANA provides two ways in data preview: executing sql and “Data Preview” in SAP HANA Studio.

For the first one, all existing modelers leave in schema “_SYS_BIC” by “package_name/view_name” name format. Give select privilege on this schema to “_SYS_REPO”, we could query data of “SALES_BY_REGION” directly.

GRANT SELECT ON SCHEMA _SYS_BIC TO REPOUSER;

If you would preview data in SAP HANA Studio, some additional privilege should be granted. Just execute these two commands with “SYSTEM” user and then we could preview data in Studio.

GRANT SELECT ON SCHEMA _SYS_BI TO REPOUSER;

CALL GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE('_SYS_BI_CP_ALL','REPOUSER')