CDS View Row Level Authorizations with Data Control Language (DCL)
In this blog, I plan to go through the steps and pre-requisites of creating a simple DCL (Data Control Language) to restrict at the row level, a custom CDS view built for analytics/reporting.
Firstly, I created a few CDS Views in HANA studio. A CDS view of type dimension on table MARA, with an association to MAKT to get the material descriptions.
I then built a simple CDS view of type cube on table VBAP with very few fields, associating to the Material CDS View dimension above. I won’t go through that process and what each annotation means. There are many blogs out there explaining the concepts of CDS Views.
For this example, I want to restrict the data based on field Material Group (VBAP-MATKL).
Below is my Cube CDS View, selecting data from VBAP. A few things to note:
- We need to have the annotation @AccessControl.authorizationCheck: #CHECK. This will allow this CDS view to be checked for any authorizations.
- I’ve created an alias for MATKL, renaming it as MaterialGroup. This is the field that we will use in the DCL.
Below is my source table, which includes multiple values for Material Group:
Because CDS views exist at the application layer, we can apply the traditional PFCG security to it, which is one of the main benefits, since we don’t have to also maintain HANA DB security.
So the first step is to create an Authorization Object in TCODE SU21. I also created a new Object Class for this example:
Within the Create Authorization Object window, provide the Object technical name and Text:
Add the field ACTVT and then hit the save button:
You will be prompted for a package. Chose your package or save as local object, according to your requirements:
You should get a message similar to the one below:
The screen should have changed, and you should be able to maintain the Permitted Activities:
In the Permitted Activities screen, select 03 – Display, since this is a CDS View for analytics purposes, and we only need Display access.
Then add the field that needs to be authorized, in our case, Material Group (MATKL) and follow the same steps above to grant Display access. Your authorization object should look like this:
We are done creating the Authorization Object. Now we need to create roles to assign it to. So we go to PFCG and create the role, by giving it a name, and clicking Single Role. The first role I’ll create will be to restrict Material Group YBF02:
Within the role editor, I clicked on the Authorizations tab and then Change Authorization Data at the bottom:
I did not select any templates:
In the next screen I selected Manually to add the Authorization Object previously created:
I add the authorization object and hit the green tick:
You should get a screen that looks like this:
We need to edit the authorizations, by clicking on the pencil next to each field. We’ll start with Activity. Make sure the check box next to Display is checked, then hit save:
Then click the pencil next to Material Group and maintain the permitted value then hit save:
Your role should look like this (ignore the top two authorization object BC_A and RS):
Likewise, I built another role restricting data by Material Group YBR01 following the same steps above:
With my roles created, I created two users to test each role, user YBR01_DLC and YBF02_DCL, each with the corresponding roles:
We are now finally ready to create the DCL in HANA Studio. So open it up and go to the ABAP perspective. Go to your desired package (I did it in the $TMP package) and right click -> New -> Access Control:
Give it a name and description and hit next:
Assign a transport or just hit next:
Keep the selected template and hit finish:
You should have the following screen as per the template:
Modify it as follows:
Define Role: provide a role name
Grant Select on: this is the CDS View which we are restricting the data. Use the DDL Source name and NOT the SQL view name
Where: is the field that we are restricting, and if there’s an alias in the CDS view, use that
ZMATGRP is the name of the authorization object defined earlier
MATKL is the field in the authorization group
ACTVT is the activity, and value 03 is display
Activate the DCL, and we can now test the users we created earlier.
Log in to ECC with those users, and navigate to tcode RSRTS_ODP_DIS. Because this CDS View is of VDM type Basic, we cannot preview it in RSRT. If we had built a CDS view of VDM Type Consumption and @Analytics.Query: True, this would allow us to use RSRT.
In tcode RSRTS_ODP_DIS, we leave the ODP Context ABAP Core Data Services, and in the ODP Name, we enter the SQL View name of the CDS View. Then hit execute:
You will be presented with the layout of the CDS View, with all the key, data, unit and key figures that were previously defined. You will also notice any associations or semantics by seeing the green icons at the far right of a field.
Click on Standard Query to take you to a data preview of the CDS View, taking into account any authorizations previously defined:
Once in the data preview, add the Material Group to the rows section to validate if the DCL worked.
We can see that for user YBR01_DCL we can only see material group YBR01.
Likewise, for user YBF02_DCL we can only see YBF02:
And that’s it. I hope this was informative and that it can help you in securing your data in your CDS views.