Insufficient privilege error while creating a procedure in HDI

[former_member511331]

In my database i am able to create a normal procedure but if i try to create the following procedure it is showing insufficient privilege error.

PROCEDURE "SELECT_TEST"(
	IN COLUMN_NAME NVARCHAR(1000)
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER AS
BEGIN
	
	DECLARE START_TIMESTAMP TIMESTAMP;
	DECLARE LAST_SYNC_TIMESTAMP TIMESTAMP;
	DECLARE TEMP_TIMESTAMP TIMESTAMP;
	DECLARE STARTTIME TIMESTAMP;
	DECLARE ENDTIME TIMESTAMP;
	DECLARE COL_NAME NVARCHAR(1000);
	DECLARE FACT_COL_NAME NVARCHAR(1000);
	DECLARE FACT_AGG_COL_NAME NVARCHAR(1000);
	declare delimeter string := ',';
	declare splitted string;
		
	IF :COLUMN_NAME IS NULL 
	THEN
		COL_NAME := '';
		FACT_COL_NAME := '';
		FACT_AGG_COL_NAME := '';
	ELSE
		COL_NAME := '"'||REPLACE(:COLUMN_NAME,',','","')||' ",';
		FACT_COL_NAME := 'FACT_TABLE."'||REPLACE(:COLUMN_NAME,',','",FACT_TABLE."')||' ",';
		if right(:COLUMN_NAME,1) <> delimeter then
			COLUMN_NAME := :COLUMN_NAME || delimeter;
		end if;
		FACT_AGG_COL_NAME := '';
		while :COLUMN_NAME <> SUBSTR_AFTER (:COLUMN_NAME,delimeter)
		do
		 splitted = SUBSTR_BEFORE (:COLUMN_NAME,delimeter);
		 COLUMN_NAME = SUBSTR_AFTER (:COLUMN_NAME,delimeter);
		 FACT_AGG_COL_NAME := :FACT_AGG_COL_NAME || 'AGG_TABLE."'||splitted||' " = FACT_TABLE."'||splitted||' " AND';
		end while;
			
	END IF;
	
	SELECT MAX(CREATION_TIMESTAMP) INTO START_TIMESTAMP FROM "AGGREGATED_TABLE";
	IF :START_TIMESTAMP IS NULL THEN
		SELECT MIN(CREATED_TIME) INTO START_TIMESTAMP FROM "MASTER_TABLE";
	END IF;
	
	SELECT LAST_SYNC_TIMESTAMP INTO LAST_SYNC_TIMESTAMP FROM "LAST_SYNC";
	
	TEMP_TIMESTAMP := LAST_SYNC_TIMESTAMP;
	
	IF LAST_SYNC_TIMESTAMP > START_TIMESTAMP THEN
	
	WHILE TEMP_TIMESTAMP >= ADD_SECONDS(START_TIMESTAMP,-3*30*60) DO
	
		IF(TO_DECIMAL(TO_VARCHAR(TEMP_TIMESTAMP,'MI'))<30)
		THEN
			STARTTIME	:= TO_TIMESTAMP(CONCAT(TO_VARCHAR(TEMP_TIMESTAMP,'YYYY-MM-DD HH24'),':00:00.000'),'YYYY-MM-DD HH24:MI:SS.FF3');
			ENDTIME		:= TO_TIMESTAMP(CONCAT(TO_VARCHAR(TEMP_TIMESTAMP,'YYYY-MM-DD HH24'),':29:59.999'),'YYYY-MM-DD HH24:MI:SS.FF3');
		ELSE
			STARTTIME	:= TO_TIMESTAMP(CONCAT(TO_VARCHAR(TEMP_TIMESTAMP,'YYYY-MM-DD HH24'),':30:00.000'),'YYYY-MM-DD HH24:MI:SS.FF3');
			ENDTIME		:= TO_TIMESTAMP(CONCAT(TO_VARCHAR(TEMP_TIMESTAMP,'YYYY-MM-DD HH24'),':59:59.999'),'YYYY-MM-DD HH24:MI:SS.FF3');
		END IF;
		
		EXECUTE IMMEDIATE 'MERGE INTO "AGGREGATED_TABLE" AGG_TABLE'||'  USING ('
				||' 	SELECT "REQUEST_URL","CLIENT_IP",'
				||' 		'|| :COL_NAME
				||' 		SUM("MESSAGE_COUNT") AS "MESSAGE_COUNT"  '
				||' 		'
				||' 	FROM "MASTER_TABLE" WHERE "CREATED_TIME">= '||:STARTTIME
				||' 		AND "CREATED_TIME" <= '||:ENDTIME
				||' 	GROUP BY "REQUEST_URL",'|| :COL_NAME
				||' 		"CLIENT_IP" ) FACT_TABLE'
				||' ON'
				||' 	AGG_TABLE."REQUEST_URL" = FACT_TABLE."REQUEST_URL" AND'
				||' 	AGG_TABLE."CLIENT_IP" = FACT_TABLE."CLIENT_IP" AND'
				||:FACT_AGG_COL_NAME
				||' 	AGG_TABLE."CREATION_TIMESTAMP" = BIND_AS_VALUE('||:STARTTIME||' )'
				||' WHEN MATCHED THEN UPDATE SET '
				||' 	AGG_TABLE."MESSAGE_COUNT" = FACT_TABLE."MESSAGE_COUNT"'
				||' WHEN NOT MATCHED THEN '
				||' 	INSERT ('
				||' 		"ID",'
				||' 		"REQUEST_URL",'
				||' 		"CLIENT_IP",'
				||' 		'|| :COL_NAME
				||' 		"MESSAGE_COUNT",'
				||' 		"CREATION_TIMESTAMP"'
				||' 	) VALUES('
				||' 		SYSUUID,'
				||' 		FACT_TABLE."REQUEST_URL",'
				||' 		FACT_TABLE."CLIENT_IP",'
				||' 		'||:FACT_COL_NAME
				||' 		FACT_TABLE."MESSAGE_COUNT",'
				||' 		BIND_AS_VALUE('||:STARTTIME||' ))';
			
				TEMP_TIMESTAMP := ADD_SECONDS(TEMP_TIMESTAMP,-30*60);
			
			END WHILE;
	END IF;
END;

[luisdarui]

By looking at your procedure I can see that you are selecting some tables - and based on it, probably your user does not have authorization to either create a procedure in the schema or your user does not have authorization to query these tables.

Either way, you should enable an authorization trace to find out which authorizations you are missing - Troubleshooting SAP HANA Privileges and Authorisations.

Cheers,
Luis