SAP Datasphere – Data Access Controls on hierarchy nodes
SAP Datasphere offers a very simple way to manage data permissions via Data Access Controls. This controls who can see which data content. In projects, in addition to simple authorizations on values of a dimension, authorizations on nodes of a hierarchy are often required. Until now, this has not been possible directly in SAP Datasphere. The blog post show a possibility how to assign data permissions on hierarchy nodes.
Starting point: a simple example data model
The diagram shows the very simple data model. There is a table with transaction data and an Organizational Unit dimension that contains a hierarchy. The dimension is associated with the Analytical Dataset. Finally, an Analytic Model has been created.
The Analytic Model contains key figures from the personnel area per organizational unit. Personnel data is a very sensitive area where it is important that not all employees are allowed to see all data. Therefore, the data authorizations have a special significance.
Of course, in a simple example like this, you could assign permissions for each individual organizational unit. For example, if you want to see all the data of the IT department, you would have to manually authorize the IT department and all subordinate organizational units. However, this is not very practical.
On the one hand, there are usually much larger organizations with many more organizational units. Here, maintenance is simply too time-consuming. On the other hand, organizations are subject to change. If, for example, new organizations are subordinated or even some are removed, this would have to be maintained manually. Hardly feasible.
Therefore, a solution is sought in which nodes of a hierarchy can be authorized.
The solution architecture uses the standard Data Access Controls feature and restrict permissions to organizational units. The diagram shows the structure in the simple example:
The Authorization Values table contains the actual authorization values. However, users do not want to maintain individual organizational units in the table, but would like all subordinate organizational units to be automatically authorized as well.
This can be achieved via the SQL View Authorized OrgUnits (SQL Script). Here, all subordinate organizational units are determined. So you search for all children of the hierarchy. This is made possible by the following coding:
return WITH LT_HIER_DATA AS ( select * from HIERARCHY_DESCENDANTS ( SOURCE HIERARCHY ( source ( select "OrgUnit" as node_id, "ParentOrgUnit" as parent_id from "AuthDemoHierarchy" ) ) ) ) SELECT C."UserMailAdress", A.NODE_ID AS "OrgUnit" FROM LT_HIER_DATA AS A INNER JOIN LT_HIER_DATA AS B ON a."START_RANK" = B."START_RANK" INNER JOIN "AuthValues" AS C ON B.NODE_ID = C."OrgUnit" AND B.HIERARCHY_DISTANCE = '0' AND C."Valid_From" <= current_Date AND C."Valid_To" >= current_Date;
The first SQL statement returns all “children” organizational units to an organizational unit of a hierarchy. Via Start rank the respective children can be assigned to an original parent node. So a standard HANA hierarchy function is used. More about this here: Link
Now if we restrict the permission value to the IT Department (number 14), all children are automatically returned and the Data Access Controls are restricted. In the picture you see the result of the SQL Code:
In SAP Analytics Cloud, you only see the FTE for the authorized organizational units.
With SAP Datasphere, customers get more than graphical views and simple SQL capabilities. Due to the basis of HANA Cloud, many functions of SAP HANA can be used. In the example of this Blog post I show that authorizations on hierarchy nodes can be easily created.
Nice blog, thanks, we were waiting for this which was planned for Q1 but it disappeared from the roadmap.
Just for my understanding, the SQL view is just the combination of the of single values and their parent nodes?
the result in the SQL view Authorized_OrgUnits can also be to maintain only IT Department (number 14)?
the table Authorized Values only contain the value for the IT Department. In the table AuthDemoHierarchy the Organisation hierarchy is stored.
The SQL View Authorized OrgUnits then read all subordinate OrgUnits for the value in the Authorized Values (children, grandchildren, etc.). The result of it is a list of OrgUnits which a user is allowed to see. The Data Access Control is then based on the SQL View Authorized OrgUnits.
In the scenario of the post every time all subordinate OrgUnits are selected. However for sure you can add an additional column (IS_HIERARCHY -> Boolean) in the table Authorized Values.
Then a SQL logic can handle this flag to read out the subordinate OrgUnits or not.
thank you for the detailled answer.
my question is would this also work if i only have the Email and NodeID in the View?
there was also a roadmap item where we cloud also maintain ranges, do you know if this also available?
thank you & best regards
in the end you can build almost anything if the Auth Values table is built appropriately and a SQL View processes the table correctly.
Ranges are also no problem in principle.
SAP Datasphere offers a high degree of freedom for many different solutions. However, you no longer have (until now) the predefined UI of SAP BW analytical priviledges where everything is already built in.
Just for my understanding, is this a workaround or the actual implementation row level security supporting hierarchies?
This was planned for Q1 as mentioned in a previous reply?
I do not know how the final solution will look like if the roadmap item return to the Roadmap Explorer.
However, in Q3 2023 a general update for the hierarchy implementation in DSP is planned.
Both topics may affect the solution of this blog post. I believe by now it is too early to say, if the solution of this post will still work or need to be adapted.
I'm trying out your example, with some of our own data, just using the parent and child as parent_id and node_id.
It seems that I'm missing something, since I'm not getting any data out, and my view has data.
Could you share the format of your AuthDemoHierarchy table, does it have all the columns described in HANA hierarchy link like ( hierarchy_tree_size, hierarchy_parent_rank, hierarchy_level, parent_id, node_id, hierarchy_distance, start_rank, start_id etc)
My code so far (haven't got the authorization values yet, but i should still get something out)
WITH LT_HIER_DATA AS (
FROM HIERARCHY_DESCENDANTS (
SOURCE HIERARCHY (
"NODEVALUE" AS node_id,
"PARNODE" AS parent_id
FROM LT_HIER_DATA AS A
INNER JOIN LT_HIER_DATA AS B
ON A."START_RANK" = B."START_RANK"
somehow I can't add images in my reply.
The table AuthDemoHierarchy is really easy with only two string 100 columns. At my customer it is also working for a organisation hierarchy.
The additional columns of the hierarchy function are generated by the function and should be visible in LT_HIER_DATA.
Is the LT_HIER_DATA working?
Is the SQL View switched to SQL Script?