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;

[pfefferf · Accepted solution]

The IN operator will not work for that case, because it will just do a string comparison for the '*' from your auth_table.

You need to implement a special handling. If for the user a star is found in the auth_table, instead of returning an expression with the IN operator, you have to return an expression with the LIKE operator (-> 'organisation like ''%''').

[former_member204732 · Accepted solution]

May Thanks Florian.

Is there a way to debug SQLScript ? Or to display step by step variables values ?

[pfefferf · Accepted solution]

Yes, it is possible. Just search for "SQLScript Debugger", then you find all the relevant stuff.

[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