Skip to Content
0
D D
May 29 at 07:09 AM

Dynamic SQL in SAP HANA stored procedures - Using mapping table

105 Views Last edit May 29 at 07:12 AM 3 rev

In SAP ECC we have created a mapping table containing user and organisation authorization access.

U1 : OrgA
U2 : OrgB
U3 : *

We have created a SAP HANA Calculation View in SQL Analytics Privileges. And we have an authorization dynamic authorization process in place, which will filter the access to the organisation dynamically based on the reading of the mapping table.

The solution works.

Expect for the management of the * value corresponding to the access to all organisation.

Would you know how to adjust the source code below to manage the access to all organisation ?

PROCEDURE "_SYS_BIC"."REPORTS::CONTROLE_AUTH_ORGANISATION" ( out OUT_FILTER VARCHAR(500) ) 
    LANGUAGE SQLSCRIPT
    SQL SECURITY definer
    DEFAULT SCHEMA ABAP
    READS SQL DATA AS
BEGIN

 VALUES_LIST = SELECT USER_NAME,'organisation in (' ||'''' || STRING_AGG(RESTRICTION, ''',''' )  ||  '''' || ')' 
 as RESTRICTION from auth_table
 where USER_NAME = SESSION_USER
 group by USER_NAME;
 
 SELECT distinct RESTRICTION into OUT_FILTER from :VALUES_LIST;

END;