Creating an SAP HANA user with just enough rights to use SAP Lumira
I was digging through the SAP Lumira and SAP HANA documentation and found in the SAP HANA Modeling Guide a list of required permissions in section 3.1 for granting permissions for HANA Analytic Views. The problem is that the permissions defined were for both accessing the data associated with an Analytic View and being able to model views as well. In my case, I wanted to create a HANA user that could only access the data using Lumira. With some help from Lars Breddemann, I came up with the following SQL based solution in HANA Studio for creating the user login and granting access to run Analytic Views for use with Lumira. Here is the code that creates a LUMIRA user, grants SELECT rights to the HANA database schemas that contain the tables and then grants rights to view and execute the Analytic Views. You just need to tweak the script to meet your needs and run it in SAP HANA Studio’s SQL Console window.
— Create the low priviledged user for Lumira
CREATE USER LUMIRA PASSWORD Hana1234;
— Grant SELECT permissions on the SCHEMAs where the
— data resides for your Analytic Views. In this example, the WIKIDATA schema is
— where the main tables reside.
GRANT SELECT ON SCHEMA “WIKIDATA” TO LUMIRA;
— These are the schemas where there are modeling specific tables. In my case
— the _SYS_BI schema has a time dimension table I used for my star schema.
GRANT SELECT ON SCHEMA “_SYS_BI” TO LUMIRA;
GRANT SELECT ON SCHEMA “_SYS_BIC” TO LUMIRA;
— Grant remaining rights needed to view and execute
— Analytic Packages
GRANT EXECUTE ON “SYS”.”REPOSITORY_REST” TO LUMIRA;
CALL GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE(‘_SYS_BI_CP_ALL’,’LUMIRA’);
— Check to see that the permissions were set correctly
SELECT * FROM “SYS”.”GRANTED_PRIVILEGES” WHERE GRANTEE = ‘LUMIRA’;
To see how I used this user account to analyze Wikipedia data that I processed with Hadoop on AWS and with HANA One, check out my recent blog post “Analyzing Wikipedia Data with SAP Lumira“
Regards,
Bill Ramos
Hi Bill,
hope you are doing fine.
Excellent tip for the Hana community.
May I add this as a section (credited to you) in the SAP Hana Reference for NetWeaver Basis Administrators ?
All the best,
Andy.
Hi Andy,
I'm glad you liked it. By all means - feel free to add this to your superb list.
Regards,
Bill
Hi Bill,
thanks, will do.
What about the book, have you written it yet ?
I've prepared you the skeleton, just fill in the gaps and get it published 🙂
Andy.
Hi Bill,
Analytic Privileges are a tricky business. For example here you have granted _SYS_BI_CP_ALL which is handy because it gives access to everything and also dangerous because it gives access to everything.
In the HANA Security Guide this is discouraged with the statement "This analytic privilege potentially allows a user to access all the data in all activated views, regardless of any other analytic privileges that apply. Although the user must also have the SELECT object privilege on the views to actually be able to access data, the _SYS_BI_CP_ALL analytic privilege should not be granted to users, particularly in production systems".
In this example the additional grant of SELECT on _SYS_BIC gives access to all views.
When multiple analytic privileges are in play the outcome is whatever is least restrictive so for example if user LUMIRA is granted an analytic privilege that restricts data to country CANADA for a particular view and also granted _SYS_BI_CP_ALL then user LUMIRA will see data for all countries.
Granting _SYS_BI_CP_ALL is great for development system and when moving into test and production this needs to be replaced with analytic privileges that grant only the intended access to the intended users.
James Anderson
SAP Active Global Support
Crystal Reports and Lumira Nexus