Dynamic SQL in SAP HANA stored procedures - Using mapping table

[former_member204732]

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;

[Cocquerel]

I would suggest the following code:

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, ''',''' )  ||  '''' || ')
 OR 0 < ' || SUM (CASE WHEN RESTRICTION = '*' THEN 1 ELSE 0 END)
 as RESTRICTION  from auth_table
 where USER_NAME = SESSION_USER
 group by USER_NAME;

 
 SELECT distinct RESTRICTION into OUT_FILTER from :VALUES_LIST;

END;

[former_member204732]

Thanks a lot all.

The syntax proposed : SELECT USER_NAME,'organisation in ('||''''|| STRING_AGG(RESTRICTION,''',''')||''''|| ')OR0<' || SUM (CASE WHEN RESTRICTION = '*' THEN1ELSE0END)

doesn't works.

I agree with the proposal of using : LIKE operator (-> 'organisation like ''%''').

I am not familiar with SQL Script coding do not know how to implement it ?

May you provide me some details ?

Thanks

[Cocquerel]

What error do you have with the code I suggested ?

[former_member204732]

The error message is : Syntax error. "0" is incorrect or misplaced.

[Cocquerel]

On my side, I have no syntax error and the result is as expected.
I guess you made a copy/paste error