Skip to Content

Is the Keyword 'SQL Security' working as intended?

Aug 30, 2017 at 11:57 AM


avatar image

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


drop-table.png (7.6 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
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).


Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi Florian,

But what I am saying is, that this does not seem to be the case...

The test-user has a role with the following privileges:

But when I call the procedure with said user, I get the following error (in the .py sql trace file):

# Error call (thread 20000, con-id 335888) at 2017-08-30 13:14:44.116581

# con info [con-id 335888, tx-id 81, cl-pid 8048, cl-ip, user: SP_TEST, schema: SP_TEST]

# FAILURE OCCURRED AT: /data/jenkins_prod/workspace/HANA__FA_CO_PPC64GCC48HAPPY_rel_fa~hana1sp12/s/ptime/query/checker/ # con info [con-id 335888, tx-id 81, cl-pid 8048, cl-ip, user: SP_TEST, schema: SP_TEST]

# MESSAGE: insufficient privilege: Not authorized

This is the call I make:

and I get the following error:

Could not execute 'CALL "_SYS_REPO"."coop.secu.protected.procedures::dropVirtualTable"('EBW_SDA', 'TEST_TABLE')'

SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

Am I right in assuming this is not the intended way this should behave?



pxbyo.png (5.8 kB)
uggpo.png (8.7 kB)

How did you create that procedure? Via a repository object or with the CREATE PROCEDURE command?


It was created in the repository and the activated to generate the runtime object...

Interestingly enough, everything works fine for the creation of virtual tables...


Have you already done an authorization trace. If you do that you should see the detailed issue.