Analytical scenarios without proper data authorization is as useless as a fifth wheel on the car. If you search for a good documentation for that topic, you’ll find a lot of good stuff but nothing based on a single business scenario. I personally faced that situation several times and got mad of that. Out of that experience i tried to set the scene for myself clear based on a single scenario.

The scenario is based on the well-known ABAP SFLIGHT tables. If you are an ABAP geek, you are well aware of them.

Scenario 1 & 2 is handling the data authorisation with analytical privileges. Scenario 3 is handling the data authorisation based on CDS objects with DCL language. The goal is to see in all scenarios the same authorized data independent of the technology.

Prerequisites:

The authorised user data is stored in two tables. One table is defined as .hdbtable and a CDS-compliant .hdbdd document. The Values in the tables are same.

Image 1: sfligh.hdbtable::UserAuthorisation ( Used for scenario 1 & 2 )

Image 2: sfligh.cds::sflight.UserAuthorisation ( Used for scenario 3 )

Depending on the scenario, the above tables will be the source of the authorized values.

Scenario 1: Dynamic Value Filters in the Attribute Restriction of XML-Based Analytic Privileges (Classical Analytical Privilege)

Situation: User is allowed to see only Carrier data based on table “sfligh.cds::sflight.UserAuthorisation”

1.    Create a CDS .hdbdd file with the definition of the output parameter “Carrid”

Image 3: sfligh.cds CDS document

2.    Create a procedure to retrieve the authorized values from the table “sfligh.cds::sflight.UserAuthorisation”

Image 4: Procedure “sfligh.auth::PR_AUTH_CARRID”

3.    Create the calculation view used secured by Classical Analytical Privilege check based on the table SFLIGHT.

Image 5: Calc View for SFLIGHT reporting secured by Classical Analytical Privilege

4.    Create the Classical Analytical Privilege

Image 6: Classical Analytical Privilege AP_CLASSIC_CARRID

5.    Create and assign the role to the test user.

Image 7: role sfligh.auth::carrier

Image 8: Grant role with the standard procedure

6.    Lets run the query for the calc view “CL_SFLIGHT_CLASSIC_AP” with the test user KLAUS and check the Sum of the key figures. KLAUS should see only values for Carrier ID LH & SQ.

Image 9: Result for Calc View CL_SFLIGHT_CLASSIC_AP

Required artifacts for scenario 1

Image 10: Required artifacts for scenario 1

More information to the Scenario 1 can be found in the official SAP HANA Help, here

Scenario 2: Dynamic Value Filters in the Attribute Restriction of SQL-Based Analytic Privileges (SQL Analytical Privilege)

Situation: User is allowed to see only Carrier data based on table ““sfligh.cds::sflight.UserAuthorisation””

1.    Create the calculation view used secured by SQL based Analytical Privilege check based on the table SFLIGHT.

Image 11: Calc View for SFLIGHT reporting secured by SQL Analytical Privilege

2.    Create a sql analytical privilege retrieving the value from the table ““sfligh.cds::sflight.UserAuthorisation””

Image 12: SQL based analytical privilege with dummy value

3.    Create and assign the role to the test user.

Image 13: role sfligh.auth::carrier

Abbildung 14: Grant role with the standard procedure to testuser KLAUS

4.    Lets run the query for the calc view “CL_SFLIGHT_SQL_AP”  with the test user KLAUS and check the Sum of the key figures. KLAUS should see only values for Carrier ID LH & SQ.

Image 15: Result for Calc View CL_SFLIGHT_SQL_AP

Required artifacts for scenario 2

Image 16: Required artifacts for scenario 2

More information to the Scenario 2 can be found in the official SAP HANA Help, here

Scenario 3: Dynamic Value Filters in the Calc View

Situation: User is allowed to see only Carrier data based on table “sfligh.cds::sflight.UserAuthorisation”

Attention!! Technically this approach is working but it is NOT recommended. With this approach you cannot provide full access based on a static Analytical Privilege for Data Validation purpose. Like having full data access for certain users. The consequence is ALL test user need to have explicitly full access in the authorization value table instead of using a AP with full data access.

1.    Create the calculation view with no Analytical Privilege check active based on the table SFLIGHT. Utilize a join in the select on the user data table directly.

Image 17: Calc View for SFLIGHT reporting secured by SESSION_USER

2.    Create and assign the role to the test user.

Image 18: role sfligh.auth::carrier

Image 19: Grant role with the standard procedure

3.    Lets run the query for the calc view “CL_SFLIGHT_SQL_AP”  with the test user KLAUS and check the Sum of the key figures. KLAUS should see only values for Carrier ID LH & SQ.

Image 20: Result for Calc View CL_SFLIGHT_SESSION_USER

Required artifacts for scenario 3

Image 21: Required artifacts for scenario 3

Scenario 4: DCL – for SAP HANA Authorizations for CDS

Situation: User is allowed to see only allowed Carrier data based on view “sfligh.cds::sflightView.sflight” which is filtered on the table “sfligh.cds::sflight.UserAuthorisation”.

1.    Create View with activated Structured privilege check

Image 22: View sfligh.cds::sflightView.sflight

2.    Create Access Policy with Aspect and Role to filter the authorized values

Image 23: Definition of the roles

3.     Assign the role sflight.cds::userAuth.user_CARRID.

Do Not forget to assign SELECT privilege to the test user on the securedview “SAP_HANA_DA”.”sfligh.cds::sflightView.sflight” additionally

4.     Lets run the query for the view “sfligh.cds::sflightView.sflight”  with the test user KLAUS and check the Sum of the key figures. KLAUS should see only values.

Image 24: Result for DB View sfligh.cds::sflightView.sflight

Where can user data be stored?

1.   Table / View (eg. AGR_1251, USR* etc…)

2.   On the user master as PARAMETER

3.   Active Directory

Ressources

Feel free to download the content from GitHub.