Skip to Content
0
May 18, 2018 at 11:32 AM

Querying calculation views inside procedure

397 Views Last edit May 18, 2018 at 11:33 AM 2 rev

Hello.

I have created the following catalog procedure:

CREATE PROCEDURE XXXXXXXX(IN GGGGMM INT, IN ID_IZPILDES_LAIKS INT)
LANGUAGE SQLSCRIPT AS

BEGIN

INSERT INTO YYYYYYYY(ID_IZPILDES_LAIKS,ID_NODOKLMAKS,GADS_MENESIS,ID_KRITERIJS,VERTIBA) 
     SELECT :ID_IZPILDES_LAIKS, 
            NM_ID, 
            :GGGGMM * 100 + 1 AS GADS_MENESIS, 
            KRIT_ID, 
            SUM(KRIT_VERTIBA)
     FROM "_SYS_BIC"."ZZZZ/BBBB" (PLACEHOLDER."$$PERIODS$$"=>:GGGGMM)
     GROUP BY GADS_MENESIS, KRIT_ID, NM_ID;
END;

As you can see, procedure uses calculation view. I am able to call the procedure as the creator/owner of the procedure, but I cannot grant EXECUTE rights on this procedure to other users. The error l get, looks like this:

insufficient privilege: Not authorized to grant the privilege on the procedure

Even when I grant EXECUTE rights on whole schema, the user still cannot call the procedure. He gets error:

insufficient privilege: Not authorized

The other user can query the calculation view and even execute INSERT statement on the table. But when I put the INSERT statement inside procedure, I cannot grant rights and/or execute it as other users.

Is it because of the calculation view? Do I miss some kind of privileges?

I also created a stored procedure inside one of my packages and this problem was solved, but I wanted to know the reason why it is not working. The only difference between this and other procedures I use, is that it is using calculation view instead of regular tables/views. Is this the only way to do it?