HANA Analytical Privileges in SAP DWC - Part 1

[olaf_fischer]

Introduction

SAP DWC comes with build-in data access controls (DAC). They are used to enforce a row-level security and are fully integrated within SAP DWC. One example of integration is that a view persistence run is DAC-aware and ensures that the filtering properly works on the persisted result.

SAP HANA natively supports row-level security as well by offering analytical privileges. Compared to SAP DWC, they offer a different set of capabilities. Depending on your scenario, you might have specific interest in using some of these capabilities or re-use analytical privilege definitions you have created before.

This blog post sketches a configuration that allows you to use the native SAP HANA row-level security in the context of SAP DWC. Please note that these definitions are not surfaced to SAP DWC and hence are not integrated in the standard authorization concept.

The second part of the blog can be found here: HANA Analytical Privileges in SAP DWC - Part 2

Initial Scenario

As a starting point we assume the following configuration in SAP DWC: One space holds the data and exposes it via sharing to second space for external consumption.

With the view names we will use through the blog post, it reads like this:

We have one space named Consumption with a view exposing data for reporting, e.g. SAP SAC and we have a separate space Inbound hosting the data itself. Cross space sharing permits the view_CONSUMPTION the access to view_DATA.

The Problem Statement

Without any row-level security in place, the filters of the data request are propagated directly via the consumption view to the data view. The direction of arrows follows the request direction.

What we need is a way to merge the filters from the SAC Story request with the filters, reflecting the authorizations of the current application user. The picture below illustrates this goal: the SAC filters and the authorization filter is merged into a new effective filter. This merged filter is sent to the data view. As a result, the use sees only the portion of data that fits to both filter definitions.

Please note that merging with the authorization filter should be done close or even better directly at the level of the consumption view – and not far below in the data layer. This avoids stacking of authorization definitions and in addition, allows all views below the consumption to benefit from the complete set of filters.

Note: I used the term “Story Filter”. This should refer to the effective set of filters that are found in the data requests to SAP DWC. Sometimes filters defined in the SAP SAC UI are not included in the requests to SAP DWC.

Solution Concept

The solution described in the blog post uses the HANA Analytical Privileges to perform the merge. We extend our sample configuration with a HANA view (incl. Analytical Privileges) and re-route the requests to pass through this new entity.

How to create a HANA view with Analytical Privileges

Creating an open SQL user in the Inbound space creates a new schema on HANA with the name e.g., Inbound#Filter. Within this new schema the SQL user has sufficient permissions to create a view – and here is main trick – with analytical privileges!


You can think of this HANA view to consist of two parts:

• An authorization filter definition describing the subset of data the application user can access


• A merging capability that combines the authorization filter with any filter used to select data. In our scenario this would merge the SAC Story filters with the authorization filter.

The picture illustrates the new request flow: The consumption view requests the data from the view View_DATA_AP (with analytical privileges). After merging of filters, the request is forwarded to the view View_DATA (without analytical privileges).

Solution – Step-by-Step

After we understood the overall concept, we can start with the real implementation. As you might expect there are several little details we have to consider. If you feel lost in the details – please go back to the overview, go back on track and then again continue with the step-by-step.

We start in creating all relevant entities without the analytical privileges. After testing the data flow and configuration we enable the analytical privileges.

Prepare Space Inbound and Test Data

The sample will use the following company data you can download here:
https://github.com/SAP-samples/data-warehouse-cloud-content/blob/main/Sample_Bikes_Sales_content/CSV...

Space Configuration

1. Create a space Inbound


2. Create an open SQL user with name „filter“ and assign read/write privileges and the grant option


3. Create an Analysis User. We will need it later for testing and to determine the list of technical users we need to grant access.

View with Test Data

1. Upload the test data into a table named DATA


2. Create a view View_DATA with a 1:1 mapping of all columns.


3. Expose the view View_DATA for consumption. Via this option you allow all open SQL user of the space Inbound to access the data.

Note: The view with the test data is not shared with the consumption space. We don’t want to expose unprotected data.

Get List of Technical Users

For every space SAP DWC creates a set of HANA users. Each of these users is used for specific tasks. As an example, all requests via the Ina/MDS interface (e.g. SAP SAC) are executed with a user following the naming convention <space name>#INA_<uid>.

As we like to allow access from the Inbound space to our new HANA view, we need to grant rights to the HANA users created by SAP DWC. This is similar to the cross space sharing concept of SAP DWC. Let us prepare the granting step by identifying them.

We will grant rights to users for data preview in INBOUND and CONSUMPTION and for INA access in the CONSUMPTION space. This is sufficient for reporting purposes.

Get List of Users

1. Open the DB Explorer


2. Log on with the analysis user


3. Open an SQL console
   Hint: As we will use with different users in the DB Explorer, you might want to rename the window tab with something like analysis user.

Run the following statement:

select * from sys.users where 

usergroup_name = 'DWC_SPACE_USERS'

and user_name like 'CONSUMPTION#INA%' ;

Keep this window open or note done the list users.

Create HANA view View_DATA_AP

This section is about creating the HANA view and granting the execution rights for the INBOUND users.

Create View

1. Open the DB Explorer


2. Log on with the open SQL user Inbound#Filter


3. Open an SQL console

Run the following statement

create or replace view View_DATA_AP

as select * from "INBOUND"."View_DATA";

with STRUCTURED PRIVILEGE CHECK;

This creates the view with Analytical Privileges.

Note: If you get an error message about insufficient privileges, you have missed the expose for consumption option on view_DATA.

Create Condition Provider

The condition provider is an sql procedure that returns the authorization filter. For our initial run-through, we will return a dummy filter that allows everything. For a test validation of the filter and merging, change the return value from '1=1' to '"PRODUCTID"=''BX-1011'' '.

Later, in part 2 of this blog, this will be re-implemented to return the authorization filter for a given application user.

CREATE or replace procedure APP_USER_DEPENDENT_FILTER_VIEW_HANA (OUT OUT_FILTER VARCHAR(5000))

LANGUAGE SQLSCRIPT SQL SECURITY DEFINER READS SQL DATA AS

BEGIN

 -- Dummy filter to return all data

    OUT_FILTER = '1=1';

 -- Static test filter for a single product 

 -- OUT_FILTER = ' "PRODUCTID"=''BX-1011'' ';

END;

Create Analytical Privilege 

Create an structured privilege that links the condition provider and the HANA view we just created.

create structured privilege View_HANA_Analytical_Priv

for select

on View_DATA_AP

CONDITION PROVIDER APP_USER_DEPENDENT_FILTER_VIEW_HANA;

Assign Analytical Privilege to User

Assign this privilege to the relevant HANA users. Here we restrict us to the ones for data preview in both spaces and ina/MDS in the consumption space.

The user required for data preview has the same name as the space.

GRANT STRUCTURED PRIVILEGE View_HANA_Analytical_Priv TO INBOUND;

GRANT STRUCTURED PRIVILEGE View_HANA_Analytical_Priv TO CONSUMPTION;

The user required for Ina/MDS access ( e.g. Analytical Model or SAC Stories ) has been derived in the previous steps:

GRANT STRUCTURED PRIVILEGE View_HANA_Analytical_Priv TO CONSUMPTION#INA<uid>

Consume view_DATA_AP

Now we need to import the definition of our HANA view into the INBOUND space. As the Import => Remote Tables in the repository explorer doesn’t expose SQL Schemas as source, we use the following approach:

Import View

1. Navigate to the Data Builder


2. Select the option New Graphical View


3. In the selection pane for views and table, select the tab Sources and expand the schema INBOUND#FILTER
   
   


4. Drag the table into the view building section.


5. The system will ask you if you like to import the table. Say yes.


6. Cancel the creation of Graphical View. It is not needed.

Share Table


Next step is to share the table (although in fact it is a view) with the consumption space.

1. Navigate to the Data Builder


2. Open Table VIEW_DATA_AP


3. Share the table with space CONSUMPTION

Within Consumption Space

Create a view View_CONSUMPTION based on the shared table View_DATA_AP. This view exposes data which is filtered by the Analytical Privileges we defined in the condition provider procedure.

Testing

Lets us do some tests to validate our configuration. If this works as expected, start with the second part of this blog post. It describes a sample implementation for Authorizations.

SQL Access

1. The data preview on view_DATA_AP should display all test data.


2. The data preview on View_CONSUMPTION should display all test data.

Ina/MDS Access

There are two ways of creating data requests for the Ina/MDS interface.

1. SAC Story based on an Analytical DataSet


2. The new Analytical Model

Choose one of them and check if you see the same test data as via the SQL access. If this is failing, and SQL works as expected, it is likely that the grant of privileges for the Ina/MDS User in Space Consumption was incorrect.

Note: The sql statement CALL SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS (‘<guid of error message>', ?) provides with addition details in case you see an insufficient privilege error. Execute the statement with the analysis user

Filter and Merging

Adjust the filter definition returned by the condition provider function. Instead of '1=1' change it to return '"PRODUCTID"=''BX-1011'' '. As a result you should see a restricted data set. This proves that merging of filters work as expected.

Conclusion

Congratulations, we have now the whole setup for using HANA Analytical Privileges in place. The next step is a more sophisticated implementation that can be used for real projects. The part 2 of this blog will cover this: HANA Analytical Privileges in SAP DWC - Part 2.

You might wonder what else could be done having a HANA view as part of the view definitions in SAP DWC. Here I would like to recommend the following blogs. They deal with the HANA features of data masking and data anonymization.

Data Masking – a walk in the park with SAP Data Warehouse Cloud

Compliant Data Access – A key Self-Service Requirement in 2020

I’d be happy to hear your thoughts, ideas and comments on this blog post. Let me know in the comments!