Implementing Column and Row Level Data Security in SAP Business Suite on HANA using SQL Scripted Calculation Views

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

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