Skip to Content
Author's profile photo Marcelo Berger

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:

YBR01_DCL:

YBF02_DCL:

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

Aspect pfcg_auth:

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.

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi marcelo berger,

      Thank you for sharing such a beautiful information,

       

      2 questions i have, Plz bear with me since i am a learner.

       

      1. why we need to create authorization object and some cool stuff in PFCG, when we have DCL in hana studio, where we can give authorizations directly. When we can apply the traditional PFCG security???
        EX : @EndUserText.label: 'role_label'
        @MappingRole: true
        define role ZROLE {
        grant select on Zbc_sales_items
        where Material group = 'YBF02';
        }
      2. In the above view zbc_sales_items u have applied annotation authorization check and created corresponding DCL. when i execute that DDL view, in out put i can see notification "Result set is filtered by DCL". how could i find DCL name which we applied for DDL???

      Regards,

      Sunny.

      Author's profile photo Kris Claes
      Kris Claes

      Hello,

      we are trying this new way of applying authorisations, but now we encounter a strange problem.

      I have a CDS-view with a corresponding DCL. I made a little program to test it and that seems to work. But when we put exactly the same statement in the productive program, the authoraisation check does not seem to work.

      We actually see a difference in the generated statement in the SQL trace:

      This is the wrong one:

      WHERE  "MANDT" = '300' AND "KREDIET" LIKE 'ZT%' AND "STATUS" IN ( 'A' , 'I'  )

      And this is the correct one:

      WHERE  "MANDT" = '300' AND "KREDIET" LIKE 'ZT%' AND ( "FISTL" LIKE '1ID-MBTK%' OR "FISTL" LIKE  '1ID-MPRK%' OR "FISTL" LIKE '5DP-MCMA%' OR "FISTL" LIKE '5DP-MPRK%' OR "FISTL" LIKE '5PC-MPCS%' OR "FISTL" LIKE '5PC-MPRK%' OR "FISTL" LIKE '5TS-MPRK%' OR "FISTL" LIKE '5TS-MUPL%' OR "FISTL"  LIKE 'MGD-MCDE%' OR "FISTL" LIKE 'MGD-MPRK%' OR "FISTL" LIKE 'MLF-MCSP%' OR "FISTL" LIKE  'MLF-MPRK%'  )

      Any idea how this in possible? The program source is exactly the same in both case. Can the generation of the statement in Open SQL be influenced by some properties of an ABAP-program?

      Thanks for helping.

      Kris

      Author's profile photo Kris Claes
      Kris Claes

      I can even reply myself:

      these additions only work if in the properties of the program the 'fixed point arithmetic' flag is activated. Apparently this flag can 'be there' without being active.

      So, if these additions don't have any effect: switch off, (and try to activate, this won't work) and on this flag in the program and try again.

      Bizarre, but it works.

      Kris

      Author's profile photo Former Member
      Former Member

      Can you clarify which BW version you are on, as well as HANA DB version?  We are not on S4, we are on ECC 6 with MS SQL... We also have BW 7.4 SP12 on HANA 1.0 SP10, so we don't have out of the box CDS views.  To make this work, it seems we need to build custom CDS views, but even after doing that, we are not sure this DCL is available in BW 7.4 SP12.

      Author's profile photo Vasudev Srinivasan
      Vasudev Srinivasan

       

      is it possible to code DCL's based on a custom table as of 7.5?

      Author's profile photo Ahmed Riyaz
      Ahmed Riyaz

      Hello Marcelo,

      Thanks for your post. It has been very helpful to us. However we would like to know the other elementary roles which are required for end user to run BEx query based on CDS view.

      Like for example RFC role, S_RFCACL object, etc which you have added as foundation role. Can you share info on such auth objects/roles as well ? We would be indeed grateful of you if you share the same.

      Also after following this post can the authorization variable in BEx automatically populate with authorized values ? My BEx query is based on CDS view of annotation type cube.

       

      Author's profile photo David, Choong Poey Yee
      David, Choong Poey Yee

      Hi All,

      I would like to know if it is possible to have authorization setup based on measures. E.g. I have a generic CDS View that is shared by 2 different users, User 1 and User 2.

      User 1 is only allowed to see Measure 1 and User 2 is allowed to see Measure 1 & Measure 2. Is this achievable?

       

      Best Regards

      David Yee

      Author's profile photo Andrei Dorfman
      Andrei Dorfman

      Probably, different views were needed for this scenario to be accessed by different users?

      Author's profile photo Vincenzo Cappelluti
      Vincenzo Cappelluti

      Hi David,

      nice question. Have you find the solution?