How to avoid ‘JDBC: [258]: insufficient privilege: Not authorized’ on HCP trial for procedures

Hello,

I’ve decided to post this, since I didn’t find much organized information on the problem. If it’s there already, please add the official document/note recommending different/better?/ solution. Looking forward to your comments!

So what is the problem?

When you create a SQLScript in your project in your HCP Trial account you are allowed to activate your coding, but then you are not allowed to use it. For example I’ve created very simple SQLScript and activated it via my HCP trial account in i058469trial.sbx.lotto.models/get_winNo_by_year_sql.procedure” file.

CREATE PROCEDURE get_winNo_by_year_sql (

  in fromD DATE, in untilD DATE,

  out winNums tt_winNums)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

winNums =

SELECT “DrawingDate”, “N1”, “N2”, “N3”, “N4”, “N5”, “N6”, “NS”

  FROM “DEV_3SJAJGTO3PI1Y7GVC5BQ1ZK84″.”i058469trial.sbx.lotto.data::win_nums”

  WHERE “DrawingDate” > :fromD AND “DrawingDate” < :untilD;

END;

By default, the procedure will be activated in “_SYS_BIC” catalog.

“Could not execute ‘call “_SYS_BIC”.”i058469trial.sbx.lotto.models/get_winNo_by_year_sql”(fromD => ‘1960010’, untilD => …’ in 33 ms 371 µs . SAP DBTech JDBC: [258]: insufficient privilege: Not authorized”

If you have your own HANA box or productive cloud account you can request or set yourself the authorization and solve the problem… but not in the trial.

What is the fastest solution, if you are using HCP trial account?

From my point of view, easiest way to get around this problem is to create the object in your own NEO_ catalog, where you should put also the rest of your objects. This would enable you to use the procedure, after you activate it.

You can do that by changing:

CREATE PROCEDURE get_winNo_by_year_sql (

to

CREATE PROCEDURE NEO_BPGLM0UWOPVFLY5NNWPGVT74N.get_winNo_by_year_sql (

After the change is activated, call should work. In my example:

call “NEO_BPGLM0UWOPVFLY5NNWPGVT74N”.”i058469trial.sbx.lotto.models/get_winNo_by_year_sql”(fromD => ‘1960-01-01’, untilD => ‘1970-01-01’, winNums => ?)

You need to adapt the path to the procedure (i058469trial.sbx.lotto.models/get_winNo_by_year_sql”) and the schema name NEO_BPGLM0UWOPVFLY5NNWPGVT74N.

Best Regards,

Pavel