Question Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table in Technology Q&A

Dynamic SQL in SAP HANA stored procedures - Using mapping table

Former Member — Sat, 29 May 2021 07:09:53 GMT

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;

Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table

pfefferf — Sat, 29 May 2021 07:28:05 GMT

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 ''%''').

Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table

Former Member — Sat, 29 May 2021 14:24:59 GMT

May Thanks Florian.

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

Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table

Cocquerel — Sat, 29 May 2021 14:44:59 GMT

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;

Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table

Former Member — Sun, 30 May 2021 07:16:03 GMT

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

Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table

Cocquerel — Sun, 30 May 2021 09:55:18 GMT

What error do you have with the code I suggested ?

Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table

Former Member — Sun, 30 May 2021 18:06:10 GMT

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

Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table

pfefferf — Mon, 31 May 2021 04:50:50 GMT

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

Re: Dynamic SQL in SAP HANA stored procedures - Using mapping table

Cocquerel — Mon, 31 May 2021 06:42:12 GMT

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