Skip to Content

Hi SCN ,


I would like to share the steps to achieve Row and Column Data Security in HANA Studio  .

I thought this would be helpful for other HANA developers currently working across the Globe . Please refer Krishna’s Blog  “http://scn.sap.com/docs/DOC-54153”  that is specific to column level data security  . This is slightly different from Krishna’s approach as in this scenario we have both row and column level security with key combination which  should  be able to display or hide  from users .




Issue Description :


When a  BI User executes  WebI  Reports He /she should be able  see the data specific to the  key combination in his report  .  If the data is hidden/unauthorized  , the report columns  shows 0 . There is another complex requirement which had to be kept in mind  for this requirement was that  Columns ABC should  be shown to all the users in the table whereas Column DEF is should be shown to specific users(1,4,5)  only which suffices ( user , product and state) combination mentioned in the table  .


Solution :

Step 1 :

Create  a Table in SE11 (ECC)  for maintenance of  the users .

Hide : 0

Show /Unhide : Y

Show all the data :*


Below is the table :

    

                                                                                     A                    B              C                        D                    E                 F

Uname

Products

State

Order_ Qty

Net Revenue 

Invoice Qty 

Expenses

Price variance 

Bonus 

User 1

*

*

Y

Y

Y

Y

Y

Y

User 2

Chocolates & Confectionaries

UT

Y

Y

Y

0

0

0

User 2

Juice & Drinks

TX

Y

Y

Y

0

0

0

User 3

Baking

TX

Y

Y

Y

0

0

0

User 3

Coffee

TX

Y

Y

Y

0

0

0

User 3

Baking

CA

Y

Y

Y

0

0

0

User 3

Coffee

CA

Y

Y

Y

0

0

0

User 4

Alcohol Beverages

OK

Y

Y

Y

Y

Y

Y

User 5

Juice & Drinks

*

Y

Y

Y

Y

Y

Y

User5

Baking

*

Y

Y

Y

Y

Y

Y


Step 2 : This Table, once created in the ECC  would  be seen Within in  the Catalog folder in  HANA Studio .

Create an Attribute View (“AUTH_TABLE_AV ”)  using this ECC Table  with Dummy as a Calculated Column .


Step 3 : Add  Dummy in the  Reporting Calculation View(“Reporting_CV”)  . This is required for mapping only .


Step 4 : Write  a Scripted Calculation View  for the Data Security .

I am sharing the code Conditional Hide and show depending on the  User access set up in the Authorization Table   . Ensure you add all the other Key /Text fields , Calculated Columns  in this Data Security Scripted CV  if you have some fields coming from underlying  Graphical CV’s as this  Scripted CV would be final CV that would be  exposed to the Universe Layer  for Reporting i .

____________________________________________________________________________________________________________________________________


BEGIN


  var_out =


SELECT


(CASE WHEN((AUTH.ZPRODUCT =’*’ OR AUTH.ZPRODUCT = REP.PRODUCT_KEY)

AND


(AUTH.ZSTATE =’*’ OR AUTH.ZSTATE = REP.STATE_KEY)


AND


AUTH.Expenses =’Y’)


THEN REP.EXPENSES ELSE 0 END) AS EXPENSES,

//* This code is for Column D , ensure  similar  code is written for “EF “Columns with Product and State as these columns are visible to only specific users .

IFNULL(ORDER_QTY,0) AS ORDER_QTY, 

//* This code  is  specific to A Column .Similar  code is required for B and C Columns .

FROM


“_SYS_BIC”.”Project/REPORTING_CV” REP


LEFT OUTER JOIN “_SYS_BIC”.”Project/AUTH_TABLE” AUTH ON


REP.DUMMY = AUTH.DUMMY 


WHERE


AUTH.UNAME = SESSION_USER


AND


(AUTH.ZPRODUCT =’*’ OR AUTH.ZPRODUCT = REP.PRODUCT_KEY)


AND


(AUTH.ZSTATE =’*’ OR AUTH.STATE = REP.STATE_KEY) ;


______________________________________________________________________


Step 5: Enable SSL  on  HANA  and Configure SAML from BO to HANA in order to view the data ,according to the User Login   from the  WebI Report .


Hope it was helpful , any suggestion  would be much appreciated ,Thank you 😎

Best regards

Zeenath Syeda ,PMP

SAP BW/BI/BPC/HANA Certified  Independent Consultant

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Appukuttan Ajesh

    Hi Zeenath,

    We have a similar problem. We have a Universe that is built on a scripted calculation view, the row level security is enforced by analytical privilege and analytical procedure. When we execute the view from HANA studio as the user, the data is filtered and all is fine.

    When we execute a WEBI report connecting to the Universe, the row level restriction is not applied and the user is able to see all the data.

    The user in BO and HANA have the same credentials. SSO is set up via SAML.

    What could be the problem?

    Thanks,

    Ajesh A

    (0) 
    1. Zeenath Syeda Post author

      Hi Ajesh ,

      Please confirm the actual User name that SAML is passing back to Calculated view (Usually it is BI_USER).

      Did you set up SAML HANA side as well as in SAML at BO side ? Please confirm me on that .

      We need to set up in SAML in both these sides in order for your report to execute and showup the respective user wise .

      Hope this helps !

      Thanks

      Zeenath

      (0) 

Leave a Reply