Skip to Content
Author's profile photo Maik Toth

Mastering Data Authorization in SAP HANA

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.

/wp-content/uploads/2016/05/1_964676.png

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

 

/wp-content/uploads/2016/05/2_964677.png

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”

/wp-content/uploads/2016/05/3_964691.png

Image 3: sfligh.cds CDS document

 

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

/wp-content/uploads/2016/05/4_964692.png

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.

/wp-content/uploads/2016/05/5_964693.png

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

 

4.    Create the Classical Analytical Privilege

/wp-content/uploads/2016/05/6_964697.png

Image 6: Classical Analytical Privilege AP_CLASSIC_CARRID

 

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

/wp-content/uploads/2016/05/7_964698.png

Image 7: role sfligh.auth::carrier

 

/wp-content/uploads/2016/05/8_964699.png

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.

/wp-content/uploads/2016/05/9_964703.png

Image 9: Result for Calc View CL_SFLIGHT_CLASSIC_AP

 

Required artifacts for scenario 1

/wp-content/uploads/2016/05/10_964704.png

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.

/wp-content/uploads/2016/05/11_964705.png

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””

/wp-content/uploads/2016/05/12_964763.png

Image 12: SQL based analytical privilege with dummy value

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

/wp-content/uploads/2016/05/13_964764.png

Image 13: role sfligh.auth::carrier

/wp-content/uploads/2016/05/14_964765.png

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.

/wp-content/uploads/2016/05/15_964766.png

Image 15: Result for Calc View CL_SFLIGHT_SQL_AP

Required artifacts for scenario 2

/wp-content/uploads/2016/05/16_964767.png

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.

/wp-content/uploads/2016/05/20_964784.png
Image 17: Calc View for SFLIGHT reporting secured by SESSION_USER

 

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

/wp-content/uploads/2016/05/21_964785.png

Image 18: role sfligh.auth::carrier

 

/wp-content/uploads/2016/05/22_964786.png

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.

/wp-content/uploads/2016/05/23_964787.png

Image 20: Result for Calc View CL_SFLIGHT_SESSION_USER

 

Required artifacts for scenario 3

/wp-content/uploads/2016/05/24_964788.png

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

/wp-content/uploads/2016/05/40_964884.png

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

 

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

/wp-content/uploads/2016/05/41_964885.png

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.

/wp-content/uploads/2016/05/44_964929.png

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.

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Brenden Kennedy
      Brenden Kennedy

      I appreciate the comprehensive example here Maik, especially down to the CDS objects. Nice work!

      Author's profile photo Former Member
      Former Member

      Excellent presentation on CDS, It was very helpful.