Skip to Content

Querying calculation views inside procedure

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    May 22 at 05:13 AM

    This is due to the way that object activation works in SAP HANA.

    The internal user _SYS_REPO creates runtime versions of repository artifacts. These objects are then owned by _SYS_REPO.

    In order to provide any user with access privileges, _SYS_REPO grants those privileges to the user activating the objects and to all role objects. While _SYS_REPO requires privileges with GRANT OPTION in order to perform the activation and further grant of privileges, it does not grant those privileges with GRANT OPTION.

    Bottom line: your user can SELECT the calculation view but you cannot grant the privilege to somebody else (which is required to run your procedure).

    The problem disappears when the procedure gets activated by _SYS_REPO as this user has the privilege WITH GRANT OPTION.

    That's the recommended way to develop your procedures anyhow.

    If you absolutely want to force the privilege with GRANT OPTION to some other user, that's possible too. Check my blog post here.

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks for the reply. I think I understand now. I also read your blog post and tried to grant privileges by using SQLScript procedure with DEFINER RIGHTS. It worked, but I guess, for this case, I will stick to repository procedure instead of using catalog procedure.