Create a table in current user schema in HANA stored procedure?

Question

I have some stored procedures that create tables that we use for validation purposes. Most of these tables get dropped on the procedure next execution.

I created these procedures and when I run them they run fine, but my problem is that now we want someone else to run those procedures, but they can't.

After some research, I found out that problem was that the stored procedure is trying to write into my schema, so since the users didn't have access to my scheme, they would get that insufficient privileges error.

Is there any way that the stored procedure would create tables into the scheme from where the stored procedure was called from rather than the schema belonging to whomever created the stored procedure?

Here is a small stored procedure I created for testing:

CREATE OR REPLACE PROCEDURE XXX.SP_TMP_TEST ()
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER  AS
BEGIN
    CALL XXX.SP_DROP_TABLE_IF_EXISTS ('TMP_TEST');
    CREATE COLUMN TABLE TMP_TEST (TEST INTEGER);
END

GRANT EXECUTE ON XXX.SP_TMP_TEST TO USER_B;

Let's say USER_A creates this stored procedure in Schema XXX

Now every time USER_A calls it, table TMP_TEST gets created into USER_A schema.

Let's say USER_B tries to run it. They would get an error saying two tables with the same name can not exist. This is because the stored procedure to drop the table drops the table from USER_B's schema, but the create table tries to create the table on USER_A's schema.

The only way for this to work is to change SQL_SECURY TO DEFINER, but that would let USER_B create tables into USER_A's schema, and I want the tables to be created into USER_B's schema or USER_C or D, whoever calls the stored procedure.

I hope that makes sense.

Thanks in advance for your time and help.

Regards, Leonardo

Answer 1

This HANA SQLScript behavior is indeed not as documented.

The documentation tells us that if a procedure does not specify the DEFAULT SCHEMA then the current_schema of the session is used for unqualified objects (that is, objects where the schema name is not mentioned in the command).

If we rely on this (at least up until HANA 2 SP 04) then we run into the situation the OP described: instead of the current_schema of the session, the schema that the procedure is "in" gets used for unqualified objects.

Personally, I would guess that this is a documentation error.

How to handle the requirement (single procedure that is callable from other users) now?

One way can be to use dynamic SQL for this scenario.

CREATE OR REPLACE PROCEDURE do_in_my_schema()

   SQL SECURITY INVOKER 

AS
BEGIN
    
    IF (obj_exists (CURRENT_USER, 'TAB_X') = 1) THEN 
        EXEC 'DROP TABLE "'|| current_user ||'"."TAB_X"';
    END IF;
     
    EXEC 'CREATE TABLE "'|| current_user ||'"."TAB_X" (USERNAME NVARCHAR(256) PRIMARY KEY)';
   
END;

The OBJ_EXISTS function is a custom function that checks whether or not an object exists (see https://www.lbreddemann.org/if-exists-would-exist/ for the definition).

The main "trick" is to use the CURRENT_USER function to specify the target schema name for the desired actions. These are then executed in dynamic SQL EXEC commands;

All this has nothing to do with the SECURITY AS DEFINER setting. This rather specifies the security context (who owns the created tables) and not which namespace (schema) the tables belong to.