Skip to Content

Analytic privilege error for CV secured by SQL analytic privilege using stored procedure

Oct 16, 2017 at 01:22 PM


avatar image


I have below procedure which i ave designed just for testing views secured with Dynamic AP using stored procedure.

PROCEDURE "SYSTEM"."X_HANA_XYZ_PR::Nikunj234" (OUT test VARCHAR(20) ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER DEFAULT SCHEMA SYSTEM READS SQL DATA AS v_filter VARCHAR(20); CURSOR v_cusror FOR SELECT distinct "Customer_Name" FROM "SYSTEM"."CUSTOMER_DIM1" where "Customer_ID" = '786'; BEGIN OPEN v_cusror; FETCH v_cusror INTO v_filter; test := v_filter; CLOSE v_cusror; END;

the procedure syntax is correct, but when I try data preview of views secured by AP using this procedure I get error as below. I have checked and all the necessary access is in place. the view i am able to preview data for if i just secured it with simple SQL. Can anyone help what could be the issue. error screenshot attachedhana-error.jpg

hana-error.jpg (45.2 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Michael Healy
Oct 16, 2017 at 04:43 PM

1: Firstly make sure the _SYS_REPO has the Select granted to it on the Schema.

2: If the _SYS_REPO has the select already granted, then check this Note I wrote:

3: If none of the above work then run the trace outlined in this Wiki and attach the trace here so i can look at it to see what you're missing

10 |10000 characters needed characters left characters exceeded
Gowthami B Oct 26, 2017 at 11:20 AM

Hi Ninkuj

This error is due to the invalid filter clause that comes from the procedure's output. The scalar output from the procedure should be exactly like a filter condition in a WHERE clause i.e

Region='West' (assuming Region is column in your table)

10 |10000 characters needed characters left characters exceeded