Authorization of User Privilege on SAP HANA Modeler Development
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’) |
Hi Miller,
Excellent Information.
Best Regards
Chenna Koyyagura
Great blog Miller.
Hi Miller,
Thanks for sharing.
I have one concern regarding the _SYS_BIC and _SYS_BI permission:
Once we grant SELECT permission to _SYS_BI and _SYS_BIC to Modeling Users, they can list and view all tables and views, even if they are not supposed to.
Even if we restrict the views that each user has permission to work, in order to see the preview data, we need the SELECT permission on those two tables, meaning the users can activate/modify only their views/models, but still can open HANA Studio to list other users tables and views.
Have you ever handled or noticed such situation?
Cheers,
Daniel
Hi Miller,
While I try to validate my attribute view in package , I am getting below error. Can you please address it once.
Repository: User is not authorized to execute specified operation;User 'XXXX' has no authorization, privilege: 'REPO.MODIFY_CHANGE'!
Regards,
Khagendra
Hi Miller
How can we avoid to assign _SYS_BI_CP_ALL to REPOUSER? Imagine that we are in a production system and we want to protect it
Thanks in advanced
Analytic Privileges:
Lots of good questions in the comments here. It is sad to see no responses from SAP 🙁
how i can assing this privilege to user SYSTEM
Hi Martin,
SYSTEM user, by default, has all the privileges available.
If not, you should have another system admin user or even the ndbadmin in order to grant the required permissions.
Br,
Edgar
hey, thks, but my problem solve when i add the schema to SYS_REPO and get the select privileges, it's diferent problem that the theme, thanks.