SAP HANA Content Security Roles Setup
A few months ago I was given a task to implement content security in SAP HANA. The main purpose for this task was to provide Business user access to information models created in SAP HANA. For example Finance user should only view finance package and can access information models in that package via BI tools such as Analysis for excel.
So, after a research and few discussions with various people I came up with following security model.
Let’s assume that content is maintained in following structure:
So based on each type of privilege I created the roles as shown below:
System Privilege Roles
These roles are mainly needed for System admin tasks (technical role)
X_HNS = S for System Privilege role
|
Role Name |
Privilege Type |
Assigned Privileges |
|
X_HNS_USERADMIN This role can create users, change their password and delete users |
System Privilege |
USER ADMIN |
|
X_HNS_ROLEADMIN This role can create roles, alter roles and drop roles with SQL commands1 |
System Privilege |
ROLE ADMIN |
|
X_HNS_SYSADMIN This roles can administer HANA system, alter system parameters and execute ALTER commands to change system |
System Privilege |
INIFILE ADMIN LICENSE ADMIN LOG ADMIN SERVICE ADMIN SESSION ADMIN TRACE ADMIN AUDIT ADMIN |
|
X_HNS_SYSMON This role can change alert, enable logging and view logs to monitor system |
System Privilege |
CATALOG READ MONITOR ADMIN |
|
X_HNS_CONTENTADMIN This role can create, alter, import, export and drop content. |
System Privilege |
CREATE SCENARIO CREATE STRUCTURED PRIVILEGE
STUCTUREDPRIVILEGE ADMIN |
|
X_HNS_DATAADMIN This role can create schema, import and export tables and drop tables |
System Privilege |
CATALOG READ CREATE REMOTE SOURCE CREATE SCHEMA IMPORT EXPORT |
Object Privilege Roles
X_HNO = O for Object Privilege Role
|
Role Name |
Privilege Type |
Assigned Privileges |
|
X_HNO_CONTENT_READ This role give read access to activated views |
Object Privilege |
_SYS_BI (SELECT, EXECUTE) You would only need this _SYS_BIC (SELECT, EXECUTE) if you are using HANA studio to access views. Not using this for BI tools provides more security in terms of displaying activated views. Access to SYS_BIC will provide access to all activated views and therefore this model will be invalid. We can create separate role for this privilege |
|
X_HNO_CONTENT_WRITE This role give write access for activated views and read access to schema |
Object Privilege |
_SYS_BI (EXECUTE, SELECT, INSERT, UPDATE, DELETE) _SYS_BIC (CREATE ANY, ALTER, DROP, EXECUTE, SELECT, INSERT, UPDATE, DELETE, INDEX) |
|
X_HNO_CONTENT_LIST |
Object Privilege |
REPOSITORY_REST (EXECUTE) |
|
X_HNO_SCHEMA_READ Where SCHEMA can be changed with required SCHEMA name |
Object Privilege |
SCHEMA (SELECT) |
|
X_HNO_SCHEMA_WRITE Where SCHEMA can be changed with required SCHEMA name |
Object Privilege |
SCHEMA (CREATE ANY, ALTER, DROP, EXECUTE, SELECT, INSERT, UPDATE, DELETE, INDEX) |
|
X_HNO_FI_CONTENT |
Object Privilege |
_SYS_BIC.FI Column Views |
|
X_HNO_CO_CONTENT |
Object Privilege |
_SYS_BIC.CO Column Views |
|
X_HNO_IM_CONTENT |
Object Privilege |
_SYS_BIC.IM Column Views |
|
X_HNO_LE_CONTENT |
Object Privilege |
_SYS_BIC.LE Column Views |
|
X_HNO_MM_CONTENT |
Object Privilege |
_SYS_BIC.MM Column Views |
|
X_HNO_PA_CONTENT |
Object Privilege |
_SYS_BIC.PA Column Views |
|
X_HNO_PU_CONTENT |
Object Privilege |
_SYS_BIC.PU Column Views |
|
X_HNO_SD_CONTENT |
Object Privilege |
_SYS_BIC.SD Column Views |
|
X_HNO_SP_CONTENT |
Object Privilege |
_SYS_BIC.SP Column Views |
Package Privilege Roles
|
Role Name |
Privilege Type |
Assigned Privileges |
|
X_HNP_FI_READ This role give read access to Package FI |
Package Privilege |
|
|
X_HNP_IM_READ This role give read access to Package IM |
Package Privilege |
|
|
X_HNP_LE_READ This role give read access to Package LE |
Package Privilege |
|
|
X_HNP_MM_READ This role give read access to Package MM |
Package Privilege |
|
|
X_HNP_PP_READ This role give read access to Package PP |
Package Privilege |
|
|
X_HNP_PU_READ This role give read access to Package PU |
Package Privilege |
|
|
X_HNP_SD_READ This role give read access to Package SD |
Package Privilege |
|
|
X_HNP_SP_READ This role give read access to Package SP |
Package Privilege |
|
|
X_HNP_CO_READ This role give read access to Package CO |
Package Privilege |
|
|
X_HNP_PA_READ This role give read access to Package PA |
Package Privilege |
|
|
X_HNP_ROOT_WRITE This role give edit access to ALL Packages |
Package Privilege |
on ROOT |
Analytic Privilege Roles
There can be many analytic privileges assigned to a role. For example: I am creating one single analytic privilege first and then create a role for department with this analytic privilege. In future, more analytic privileges can be added to it. In our case, we are not using analytic privileges which means no attribute restrictions
X_HND = D for Data level restriction
|
Analytic Privilege |
Package |
Content |
Attributes Restrictions |
|
X_HND_CO_AP1 |
CO |
column views under _SYS_BIC.CO/ |
NA |
|
X_HND_FI_AP1 |
FI |
All column views under __SYS_BIC.FI/ |
NA |
|
X_HND_IM _AP1 |
IM |
column views under __SYS_BIC.IM/ |
NA |
|
X_HND_LE _AP1 |
LE |
column views under _SYS_BIC.LE/ |
NA |
|
X_HND_MM _AP1 |
MM |
column views under _SYS_BIC.MM/ |
NA |
|
X_HND_PP _AP1 |
PP |
column views under _SYS_BIC.PP/ |
NA |
|
X_HND_PA _AP1 |
PA |
column views under __SYS_BIC.PA/ |
NA |
|
X_HND_PU _AP1 |
PU |
column views under _SYS_BIC.PU/ |
NA |
|
X_HND_SD _AP1 |
SD |
column views under _SYS_BIC.SD/ |
NA |
|
_SYS_BI_CP_ALL |
ROOT |
All column views under _SYS_BIC |
No Restrictions. Currently being used |
Now the Analytic Roles
X_HNA = A for Analytic Privilege roles
|
Role Name |
Analytic Privilege |
|
X_HNA_FI |
X_HND_FI_AP1 |
|
X_HNA_IM |
X_HND_IM_AP1 |
|
X_HNA_LE |
X_HND_LE_AP1 |
|
X_HNA_CO |
X_HND_CO_AP1 |
|
X_HNA_MM |
X_HND_MM_AP1 |
|
X_HNA_PU |
X_HND_PU_AP1 |
|
X_HNA_PP |
X_HND_PP_AP1 |
|
X_HNA_PA |
X_HND_PA_AP1 |
|
X_HNA_SD |
X_HND_SD _AP1 |
|
X_HNA_ALL |
_SYS_BI_CP_ALL (This one is being used only) |
Let’s take a look at how we can use system privilege roles to create technical roles:
Technical Roles
|
Role Name |
Granted Roles |
|
Y_HNT_SECURTY Add/delete/edit users and assign other roles |
X_HNS_USERADMIN X_HNS_ROLEADMIN |
|
Y_HNT_ADMINS Perform admin tasks and security tasks |
X_HNS_USERADMIN X_HNS_ROLEADMIN X_HNS_SYSADMIN X_HNS_SYSMON X_HNS_CONTENTADMIN X_HNS_DATAADMIN |
|
Y_HNT_CONTENT_DEVS Create and activate information models in packages |
X_HNS_CONTENTADMIN X_HNO_SCHEMA_READ X_HNO_CONTENT_WRITE X_HNO_CONTENT_LIST X_HNP_ROOT_WRITE X_HNA_ALL |
Now, lets take a look at functional role example. In this example, Finance user A need access to FI package and it’s information views. So, in this case create a functional role for Finance department and add user A into it.
|
Role Name |
Granted Roles |
|
Y_HNF_FI |
X_HNO_CONTENT_READ X_HNO_FI_CONTENT X_HNP_FI_READ X_HNA_ALL |
In the same way we can create other functional roles depending upon our requirements then assign them to user. Now, it is not mandatory that everyone follow this way to setup rule but it can be used as reference.
References
- 1. SAP Hana Platform SPS6 Security Guide, 03rd September 2013, SAP HANA Security Guide, SAP Help Portal, http://help.sap.com/hana/SAP_HANA_Security_Guide_en.pdf
- 2. Tomas Krojzl 2013, ‘SAP HANA – Security Concept and Data Architecture’, SAP Community Network – Tomas Krojzl’s Blog, 24 October 2011, viewed 20 October, 2013
Hi,
let me point to some parts in your blog, I do not agree with.
DATA ADMIN in role X_HNS_USERADMIN is NOT needed and is definitely a BAD
idea.
In X_HNS_ROLEADMIN you explicitly mention that role handling can be done
with SQL commands.
Where do you see the difference between this duty and the corresponding
privileges and other roles, the duties concerned and its privileges?
All privilege checks are done for SQL commands. And all duties are done
using SQL commands.
With the system privileges in X_HNS_SYSMON you can NOT enable auditing.
And the system privilege CATALOG READ is needed for which task you mention
in this role?
I do not see the need to grant it to the role.
And which tables (not created by you) can be dropped with X_HNS_DATAADMIN?
Which schema is meant to be handled in X_HNO_SCHEMA_READ and
X_HNO_SCHEMA_WRITE?
Y_HNT_SECURTY: do you mean Y_HNT_SECURITY?
Regards,
Elke
Hi,
Thanks for your comments and feedback. I have tried to answer your queries but please note answers may depend on use case. Our use case reflects the main requirement is to implement appropriate access to CONTENT (developed by developers) for business users. If I have missed the anything from HANA system administration point of view then I will update my blog after I have tested your suggestions.
"DATA ADMIN in role X_HNS_USERADMIN is NOT needed and is definitely a BAD
idea."
We use SQL stored procedures and SQL statements to manage users and roles sometimes. When I created this guide we were on SPS 06 Rev 66. At that time, we were not able to perform DDL SQL statements without "DATA ADMIN" privilege. Therefore, I added this privilege. We are now on SPS 07 and I successfully tested DDL statements without this privilege. So you are right, we do not need this privilege. I have edited the blog.
DATA ADMIN
Authorizes reading all data in the system views. It also enables
execution of any Data Definition Language (DDL) commands in the SAP HANA
database
A user having this privilege cannot select or change data stored
tables for which they do not have access privileges, but they can drop tables
or modify table definitions.
"In X_HNS_ROLEADMIN you explicitly mention that role handling can be done
with SQL commands."
Here, I meant to say that we (as mentioned earlier) use stored procedures (custom built) and SQL statements to manage users and roles. For example:
create role X_HNO_MGC_PA_CONTENT;
call GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT('SELECT', '"_SYS_BIC"."TEST"', 'X_HNO_MGC_PA_CONTENT');
"With the system privileges in X_HNS_SYSMON you can NOT enable auditing.
And the system privilege CATALOG READ is needed for which task you mention
in this role?"
I have edited the blog to replace auditing with logging. MONITOR ADMIN is used to enable or disable events/alerts (untick/tick check IDs). We can also run ALTER SYSTEM commands for events/alerts
Following is taken from HANA Studio. I hope this will help you understand why do we need CATALOG READ.
"And which tables (not created by you) can be dropped with X_HNS_DATAADMIN?"
As you mentioned we can only drop tables created/imported by you. It will depend on scenario/use case where developer will need to create schema and then import tables into it. Also, they can drop the tables owned by them.
"Which schema is meant to be handled in X_HNO_SCHEMA_READ and
X_HNO_SCHEMA_WRITE?"
This will depend on scenario/use case. For example, there is schema DSTEST which have tables and data is coming from data services. So role can be renamed to X_HNO_DSTEST_READ and users can be added to this role who needs read access to DSTEST
I hope I have been able to answer your queries.
Regards
Angad
Hi! Angad,
I was going through your blog about Security Role set up. First of all thank you for posting such detail and clear document. In the document you mentioned that you developed or used Custom procedure to Control access to the specific schema activated views to Front end users. Would you able to share that procedure. We have similar requirement to control report user read access to specific schema.
Appreciate your input /help
Thanks
Venkat
Hi Venkat,
Thanks for the reply.
I called inbuilt stored procedure in SQL statements. One of the example is as follows:
create role X_HNO_XXX_CO_CONTENT;
call GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT('SELECT', '"_SYS_BIC"."PACKAGENAME.FOLDERNAME/CV_COST_CENTER_ACTUAL_COST_ITEM_QUERY_ADHOC"', 'X_HNO_XXX_CO_CONTENT')
These procedures are under schema "_SYS_REPO".
Regards
Angad
Hi! Angad,
Thank you very much for your response.
I was able to grant privileges on each view one by one, but I was looking for to grant Privilege on whole Package name like '"_SYS_BIC"."PACKAGENAME.FOLDERNAME" so that all the views/ activated content under that package would be automatically accessed..
Any of your suggestions are greatly appreciated.
Thanks
Venkat
Venkat,
In that case, you would do following (please note this is as per our use case and we have HANA SPS07)
> Allow users (via BI client) to browse to package which has content. In this example XXX is the package name.
create role X_HNP_XXX_READ;
grant REPO.READ on "XXX" to X_HNP_MGC_MGC_READ;
if XXX has another package (folder) under it then your should use "XXX"."FI" but if you give access to XXX then every package under it will be accessible by user.
> Allow users (via BI client) to read activated content which resides in _SYS_BIC schema
create role X_HNO_XXX_CONTENT_READ;
call GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT ('EXECUTE,SELECT','_SYS_BI','X_HNO_MGC_CONTENT_READ');
call GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT ('EXECUTE,SELECT','_SYS_BIC','X_HNO_MGC_CONTENT_READ');
I hope this will help to achieve what you need.You can change the role name as per your need.
Regards
Angad
Hi! Angad,
Thanks for the brisk response, I understand what you explained.
Yes, we have SP7
Example :The activated views paths are
"_SYS_BIC"."HD22/CV_TEST1"
"_SYS_BIC"."HD22/CV_TEST2"
Allow users (via BI client) to read activated content which is under _SYS_BIC.HD22
When I try the following the following, it gives access to all the views.
call GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT ('EXECUTE,SELECT','_SYS_BIC', 'X_CONTENT_DEV1')
I need to limit to the access to _SYS_BIC.HD22 , that means they can have access to CV_TEST1 and CV_TEST2.
As you mentioned in your earlier response we can add one by one but that is a ongoing maintenance.
I'm looking for somehting like the following which would work but the syntax is incorrect.
call GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT('SELECT', '"_SYS_BIC"."HD22/*" ', 'X_CONTENT_DEV1');
I hope, I clarified the scenario.
Thanks
Venkat
Hi Venkat,
I dont think you can use wild character in there (procedure call). Check the create statement of this procedure.
May I ask which BI client are you using? and how are you trying to connect to HANA?
If all the views that you require are in same package then giving just access to particular package should respect the security. It should not matter if user has SELECT & EXECUTE on _SYS_BIC
I know it is ongoing maintenance but if you manage it on role level then it can reduce some efforts.
Regards
Angad
Hi! Angad,
My apologizes for the delayed response..
Thank you for your suggestions.
Regards
Venkat
Hello Angad,
The inheritence is nicely explained in this blog. Thanks for sharing your security model ℹ .
~Pp
Thanks Papil,
Regards
Angad
Thanks Angad, your effort to describe security model is really appreciable...
Hi Angad
Can you please suggest how to setup Security model in case of SAP BO with SAP HANA.
In BO with BW, Role are built in BW and synced to BO and can be used in BO by linking to the BO Groups.
Do we have such option in the HANA roles too ? kindly suggest.