Skip to Content
-1

Insufficient privilege error while creating a procedure in HDI

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;
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Feb 08, 2019 at 11:34 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.