SAP Hana procedure, Could not create catalog object: insufficient privilege on creating a Stored Procedure

"insufficient privilege" is a common error while struggling with SAP Hana.
Easiest way is to configure a trace for your user.

This I was creating a stored procedure and got this error.

[42227]{251259}[51/87698283] 2016-08-29 16:15:48.131475 i Authorization SQLFacade.cpp(01415) : UserId(141949) is not authorized to do SELECT on ObjectId(2,0,oid=11254798)
[42227]{251259}[51/87698283] 2016-08-29 16:15:48.131572 i Authorization SQLFacade.cpp(01961) :



Select * from objects where object_oid = '11254798';
Select * from users where user_id = 141949;

User was SYS_REPO and object was table type i use in procedure output DAILY_REPLICATION.

PROCEDURE "MYUSER"."RFX_Business_Scenario::chk_lastweek_withno_data" ( out OUTPUT_TABLE "MYUSER"."DAILY_REPLICATION" )


I gave select to _SYS_REPO on myschema and created my procedure successfully.

GRANT SELECT ON SCHEMA MYSCHEMA TO _SYS_REPO WITH GRANT OPTION;


In logs u can see user trying to do these jobs is SYS_REPO.

User _SYS_REPO tried to execute 'CREATE PROCEDURE

SAP Hana Store procedure to find days where no transaction occurred on our system, OPInt Business Situation

Think you have a system where people create transactions.
You want to find the days which people generated no transactions.

Lets assume you want to check last week.

Define a UNION ALL block to manually generate days of last week
Query day differences of transaction and today
Find days which is not in above difference data.

You can use this as detection procedure in OPINt.



SELECT mdays.mday
FROM
(
SELECT 1 mday from dummy UNION ALL
SELECT 2 mday from dummy UNION ALL
SELECT 3 mday from dummy UNION ALL
SELECT 4 mday from dummy UNION ALL
SELECT 5 mday from dummy UNION ALL
SELECT 6 mday from dummy UNION ALL
SELECT 7 mday from dummy
) mdays
where mdays.mday not in (

select
distinct DAYS_BETWEEN (to_date("CREATED_AT" ),now())
from
"MY_SCHEMA"."TRANSACTION_TABLE"
where DAYS_BETWEEN (to_date("CREATED_AT" ),now()) < 10
)

SAP OPInt Business Situation

Think that you want to have an alarm when there is no order created in a particular day.

In OPInt this is achieved by creating a new Business Situation with a detection procedure.

A detection procedure returns data in table format.
For example you want to find days which you create any purchase order in last 10 days.

PROCEDURE "TABLESCHEMA"."RFX_Business_Scenario::chk_replication" ( out OUTPUT_TABLE "YOURSCHEMA"."DAILYCOUNT" )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER

READS SQL DATA AS
BEGIN
var1 = SELECT
top 20
LPAD(CAST(LPAD(cast("CREATED_AT" as NVARCHAR),14,'0') AS timestamp),10,'0') , count(*)

FROM "SRM_GRPQ_REPL"."CRMD_ORDERADM_H"
where
LPAD(CAST(LPAD(cast("CREATED_AT" as NVARCHAR),14,'0') AS timestamp),10,'0') < LPAD( now(),10,'0')

group by LPAD(CAST(LPAD(cast("CREATED_AT" as NVARCHAR),14,'0') AS timestamp),10,'0')
order BY LPAD(CAST(LPAD(cast("CREATED_AT" as NVARCHAR),14,'0') AS timestamp),10,'0') DESC ;



OUTPUT_TABLE = SELECT count(*) as TOTAL_COUNT FROM :var1 ;
END;

I try to write rare errors i got during SAP HANA jobs. Usually I can not find an entry in INTERNET about the error.

Below error was occurring during activation of a scenario.

sap hana java.lang.IllegalStateException: java.sql.SQLException: Table type not found

I created a stored procedure as below

PROCEDURE "SRM_GRP_REPL"."RFX_Business_Scenario::chk_replication" ( out OUTPUT_TABLE table(TOTAL_COUNT integer) )

calling this function is legal. But when I use this in a scenario it generates error.
Unexpected problem ocurred while validating scenario artifacts
java.lang.IllegalStateException: java.sql.SQLException: Table type not found





Probably OPInt have difficulty with a temp table declaration. ( table(TOTAL_COUNT integer) )

Converting it into a real table type fixed the problem.

PROCEDURE "TARGET_SCHEMA"."RFX_Business_Scenario::chk_replication" ( out OUTPUT_TABLE "MYSCHEMA"."DAILYCOUNT" )

User is not authorized to query for ProcessDefinition

Sometimes OPInt problems do not have hit on internet. I try to write problem as I see to help others
and for me to remember what i have to do if I encounter same error again.

Exception

User xyzis not authorized to search for processes in 10.6.xxx.xxx Contact your administrator for the required authorizations.

com.sap.pv.opm.discovery.bpm.search.BPMSearchOperation$BPMRequestFailedException: com.sap.visibility.bpm.facade.service.ExecutionFault: User is not authorized to query for ProcessDefinition.
at com.sap.pv.opm.discovery.bpm.search.BPMSearchOperation.run(BPMSearchOperation.java:45)
at com.sap.pv.opm.discovery.bpm.search.BPMSearchOperation.run(BPMSearchOperation.java:1)
at com.sap.pv.opm.discovery.common.operation.CancellableLongOperation$WrapperThread.run(CancellableLongOperation.java:70)
Caused by: com.sap.visibility.bpm.facade.service.ExecutionFault: User is not authorized to query for ProcessDefinition.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.sun.xml.internal.ws.fault.SOAPFaultBuilder.createException(SOAPFaultBuilder.java:135)
at com.sun.xml.internal.ws.client.sei.StubHandler.readResponse(StubHandler.java:238)

Solution :

Assign a UME action SAP_BPM_EXPORT_MODEL to related user.

From SAP Documentation :

Additional Security Configuration
The SAP HANA user who discovers SAP Business Process Management process definitions needs to have authorization as an SAP Gateway user. To consume process definitions from the SAP Business Process Management system through the SAP Gateway, the user that is authenticated in the SAP Business Process Management system needs to be assigned a UME action SAP_BPM_EXPORT_MODEL. We also recommend the following:
SAP_BPM_EXPORT_MODEL action is assigned to the SAP_BPM_SuperDisplay role.
SAP_BPM_EXPORT_MODEL action is assigned to the Support user.
To enable the user to see business scenario definitions in the space.me workspace, you need to assign the user the respective SQL and analytic privileges. For more information, see .