Integrate your authorizations like your data
When talking about SAP Data Warehouse Cloud it is usually about harmonizing data across different sources, providing the semantic layer. Once you have built your data models somebody is going to ask you “But what about authorizations?”.
Now I know that authorizations are often not everybody’s favorite subject, but for me it has been a continuous topic in most of my data warehousing projects and so I wanted to describe one approach to integrate authorizations from source systems into SAP Data Warehouse Cloud.
Authorizations in SAP Data Warehouse Cloud are implemented using Data Access Controls. This blog post gives a great overview of the functionality and how to create and apply authorizations.
Data Access Controls are a very generic concept, and this is good, because SAP Data Warehouse Cloud integrates data from different kinds of system, which all have their own authorization concept. Generic also means open and that’s the idea behind it.
Does this mean I have to implement all my authorizations again? How can I ensure that changes to authorizations in the sources are updated to SAP Data Warehouse Cloud?
In this blog post I describe an approach for an SAP ECC system, which should answer those questions.
The general approach is simple:
- Integrate the tables/views that contain authorizations/privileges as a remote table
- Use views to combine, harmonize and enrich the authorization values
- Build data access objects on top of the view
- Assign the data access objects to the relevant views
It’s obvious that step 1 is the relevant and very likely most complex one, because this must be done for each system type. For SAP BW/4HANA authorizations such a connector is already on the roadmap.
The integration using remote tables helps fulfil the requirement to have an automatic synchronization of the authorizations.
Example use case
My example is about integrating the authorizations for sales organizations from an SAP ECC system for usage in SAP Data Warehouse Cloud. This approach is also valid when you want to integrate the authorizations from several source systems
The first step is to define how the authorization can be derived from the existing SAP ECC authorizations. For this you need to define which authorization object(s) are relevant for your scenario.
Integrate authorization values
To integrate the authorization values and user assignments into SAP Data Warehouse Cloud we need to combine the following three objects from the source system:
- role assignments (table AGR_USERS)
- assigned authorization objects (table AGR_1251)
- authorization values (table AGR_1252 as sales organization is an organizational level)
This can be achieved in a graphical view that uses joins to build a structure with the username and the corresponding authorization values:
Since authorization values can contain wildcards, we need to convert the wildcards into individual values as the Data Access Controls currently do not support wildcards.
For this we need to do two things:
- translate the “*” wildcards from the authorization values into “%” (as a calculated column in the view with the authorization values:
- also integrate the master data table for sales organizations (TVKO), which we build as a dimension view:
Combine, harmonize & enrich authorization values
Finally, we combined the two views with a join. To allow for the wildcards we need to use the LIKE predicate and apply all relevant filters (role, auth. object, field name, validity, …).
We do this in a scripted SQL view:
The result of this view gives a list of all users and the sales organizations they are currently authorized for:
And you can easily imagine that this approach can also be used to combine authorization data from several source systems by combining the authorization values and master data from those systems.
Create Data Access Control
Now we are ready to create the Data Access Control based on the scripted SQL view and sales organization column as data entity:
The identifier column must be set to the username and you are ready to go:
The Data Access Control is now ready and provides real-time authorizations from the source system.
This approach is a real-time access to the SAP ECC system. And if you are worried about putting more load on your source system or the impact on the response times then you can of course also use the data replication functionality for remote tables offered in the Data Integration Monitor in SAP Data Warehouse Cloud.
Assigning Data Access Control
The final step is of course to assign the newly created Data Access Control to the relevant views. Relevant meaning all views that shall be protected via the authorizations for sales organization.
The SAP Data Warehouse Cloud help documentation contains a good example on how it is possible how the space concept can be used to ensure a segregation of application data from the authorizations if you wonder how this could be set up.
What have we achieved?
- Real-time integration of authorization vales from SAP ECC into SAP Data Warehouse Cloud
- Transformation of the authorization values into the format for Data Access Controls
- Translation of the wildcards into individual values
- Definition of a central Data Access Control that can be used to protect any sales organization related data
What does this mean for you? Hopefully I was able to demonstrate how a flexible authorization integration can be achieved and that you can use this approach in your SAP Data Warehouse Cloud projects.