Skip to Content
avatar image
Former Member

Is the Keyword 'SQL Security' working as intended?

Hello Everyone,

This is my first time asking a question here, so please be gentle. :)

I have written a stored procedure that allows certain users to create virtual tables in a local schema using data from a remote source (SDA).

Clearly, I don't want the users to be the owner of these tables, which is why the stored procedure was put in place. The creation of these tables works as intended and the owner of the created table is _SYS_REPO, which is how we want it to be.

The users also need the possibility to delete these tables. So I provided them with another stored procedure, that deletes said tables. On invocation of the procedure, the users get an 'insufficient privileges' message. The "SQL SECURITY"-option in the procedure is set to "DEFINER". From what I understand, this means, that on invocation of the procedure the privilege of the owner of the procedure (_SYS_REPO) should be checked, right?

Interestingly, this does not seem to be the case. When I call the procedure with the "SYSTEM" User, everything works fine. When I call the procedure with a user, that only has the privilege for invoking said procedure, i get an insufficient privileges error.

_SYS_REPO has all privileges for said schema.

Has anybody ever experienced a similar issue? Can you recreate this error on your systems? Is this a bug or am I missing something?

Kind Regards


Luca






drop-table.png (7.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Aug 31, 2017 at 04:36 AM

    SQL Security Definer means that the procedure is executed with the rights of the definier (in case of a HANA repository procedure _SYS_REPO).

    But still the user who wants to execute the procedure needs the privilege to execute the procedure ("only" the checks required for the statements in the procedure body are done against the definer).

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded