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?