Skip to Content
Product Information
Author's profile photo Xavier Polo

Data Access Control in SAP Data Warehouse Cloud (beta)

In the last update of the DWC beta version, a new feature has been added: Data Access Control, which allows implementing row level security when accessing data.

This allows us to define what data can be viewed by each user when accessing a DWC view. To show this functionality we are going to use an example of sales analysis, where we want each sales agent to be able to see only the customers in a certain geographical area.

This system is composed of three elements:

  • A table containing the list of fields to be filtered, in this case the sales agent and the state/province.
  • A Data Access Control (DAC), where it is specified how to interpret the data from the previous table
  • A view to which we want to apply the DAC to filter the information it displays according to the user.

 

Table with filters

The requirements are simple, you need a table that has at least two fields. One to identify the user, which must contain the email of the DWC user, and a second field containing the field to be filtered. In our example state/province, and the table is TBL_DAC_STATE.

 

Table%20with%20users%20and%20state/province

Table with users and state/province

 

Data Access Control (DAC)

A new section has been added in DWC to manage the DACs.

Data%20Access%20Control

Data Access Controls

Creating a new DAC

Create%20a%20new%20DAC

Create a new DAC

 

In “Source Data Object” we select the table with the filters (TBL_DAC_STATE) , and identify the fields that we will use to filter, in our example only the STATE_PROVINCE

Select fields to be used as filters

After this, in “Main Name Column” we select the field that contains the email of the DWC user. In our case USER_ID.

We will be able to see in the OUTPUT what fields will be available to filter in our views.

Save and our DAC is ready to use.

 

Applying security in the view

We select the view where we want to apply the security and filter at row level.
This view must have published the fields we want to filter by, in our case STATE_PROVINCE.

Sales%20view

Sales view

 

To apply the DAC to the view we have to use the new option “Data Access Control” that we can find in the properties of the view.

DAC%20in%20View

DAC in View

 

To add a DAC (you can add multiple DACS) click on the “+” icon and select the DAC that we have created previously.

Select%20DAC

Select DAC

 

To configure it, you will be asked to join the output field (or fields) of your DAC with the fields of the view.
In our example the output field of the DAC STATE_PROVINCE we relate it to the field of the view with the same name.

Define%20Join%20%28View%20-%20DAC%29

Define Join (View – DAC)

 

Check%20DACs%20on%20View

Check DACs on View

 

Checking results on Story

We have created a story to see how the data are filtered.

Entering with my user and without activating the DAC, I can see all sales data.

Full%20access%20to%20sales%20data%20%28without%20DAC%29

Full access to sales data (without DAC)

But if the DAC is activated on the view, it only allows me to see the sales of the 2 zones registered in the TBL_DAC_STATE table for my user.

Restricted%20access%20with%20DAC%20activated

Restricted access with DAC activated

 

This post is based on tests on the latest beta version of SAP DWC. This functionality may or may not be included in the final version of the product.

 

Bonus Track 🙂

There are some more new features in this latest version:

  • New option to manage the list of IPs that have access to DWC directly from the application, without having to open an incident to SAP as before

  • The management of users and their permissions to access DWC externally has been remodeled, making it simpler and clearer.

 

You can see my other post about DWC Beta: SAP Data Warehouse Data Flows

Update: At this blog there is an example how to use DACs with authorizations from SAP ECC system

Assigned Tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Enio Terra
      Enio Terra

      This is getting better!! Thanks for keep us updated Xavier Polo

      Author's profile photo Tony Hayes
      Tony Hayes

      Thanks Xavier , you address one of the gaps between DWC and BO Universes with the DACs.

       

      Tony

      Author's profile photo Ching-Wei Cai
      Ching-Wei Cai

      Hi Xavier Polo,

      This article is very useful. However, I want to ask you is there any ways to do data access control by self define roles? So, I can give several users the same authorization by their roles and don't need to update the file every time when new user need to be added in. Thanks!

      Author's profile photo Xavier Polo
      Xavier Polo
      Blog Post Author

      No, DACs only works as explained. Roles works at application level, defining what things the user can do, but not at row level.

      Best regards

      Author's profile photo Tracy Cai
      Tracy Cai

      Thanks for immediately replying! Your response is helpful and makes me much clear with this DAC.

      Best

      Author's profile photo Boris Traikoff
      Boris Traikoff

      Hey Xavier,

       

      Thanks for sharing.

      I wanted to ask you if there is a way to grant full access without listing each and every value in the DAC table?

       

      Regards

      Author's profile photo Xavier Polo
      Xavier Polo
      Blog Post Author

      No, the output field must have all the cases that the user can see.

      What we do is to have a view that generates the data for the DAC.
      For the "normal" users we get the data from the security table, and for the "special" users what we do is a cross join with the master data, generating all the possible combinations for that group of users. Then we make a union of the 2 sets of data.

      Regards,

      Author's profile photo Boris Traikoff
      Boris Traikoff

      Thanks for your answer!

      I already managed to solve the problem with a view.

      Best regards

      Author's profile photo Henry Banks
      Henry Banks

      Hi Xavier Polo  Hi Boris Traikoff I saw a new article over here by Jens Braun which i thought very helpful on this topic https://blogs.sap.com/2021/08/06/integrate-your-authorizations-like-your-data/

      regards, H

      Author's profile photo Xavier Polo
      Xavier Polo
      Blog Post Author

      Thanks for the link

      Author's profile photo Keshvi Sapankhambhati
      Keshvi Sapankhambhati

      Hello Xavier,

      If I have added DAC on a view in SAP data warehouse cloud and while accessing the same view in SAP analytics cloud(not the embedded one in SAP data warehouse cloud but the individual one), the same DAC will be applied in SAP analytics cloud story too or some additional restrictions needed to be applied?

       

      Regards,

      Keshvi Khambhati

      Author's profile photo Xavier Polo
      Xavier Polo
      Blog Post Author

      When you access to a DWC view (from SAC or from another tool) you need to log in with a DWC user and if that view has a DAC it will filter the data for that DWC user.

      The nice thing is that in SAC you can use SSO with DWC, allowing you to use the same account in SAC and DWC. In practice the SAC user is used to filter in DAC (because both match thanks to SSO). So you don't need anything extra to make the security work.

      Best regards.

      Author's profile photo Keshvi Sapankhambhati
      Keshvi Sapankhambhati

      Thanks Xavier ..!

      Author's profile photo Jagan Reddy Dasari
      Jagan Reddy Dasari

      Hi Xavier Polo ,

      Thank you for the detailed blog.

      However, I have a query, When we create a DAC for any specific space, does it get applied to all the views by default in the Space or do we need to manually add DAC into the specific View under DATA ACCESS CONTROL section of the view.

      Reason being we can see that in our final views which are consumable in SAC, the DAC is not applied in SAC and DAC is only applied at space level and not in any view but still it is working somehow. Please provide your thoughts on this.

      Thanks in Advance!

       

      Regards,

      Author's profile photo Xavier Polo
      Xavier Polo
      Blog Post Author

      DAC are not applied by default, you must add it to each view and define the join.

      Regards,

      Author's profile photo Jagan Reddy Dasari
      Jagan Reddy Dasari

      Hi Xavier Polo

      Thanks for the answer.

      Can you suggest something on this-

      Reason being we can see that in our final views which are consumable in SAC, the DAC is not applied in SAC and DAC is only applied at space level and not in any view but still it is working somehow. Please provide your thoughts on this.

      Thanks in Advance!

      Regards,

      Author's profile photo Xavier Polo
      Xavier Polo
      Blog Post Author

      Well, something strange is going on.

      Once a DAC is assigned to a view, it's applied every time the view is accessed, either from another view, from another view in another space (shared), from SAC or from the HANA SQL layer.

      In fact one of the most common mistakes is with the HANA layer, which uses its own users and this makes that when you try to access an exposed view with a DAC you can't see anything, because the SQL user is not present in the DAC.

      If you have applied a DAC to an exposed view and you access from SAC with a user that is not in the DAC you should not see anything. I would open an incident in SAP support.