cancel
Showing results for 
Search instead for 
Did you mean: 

What is "sql security invoker" supposed to do in a stored procedure that creates a table?

Chris26
Participant
1,565

Using v17.0.11.6933

Log in as user DBA.
Run this script from Interactive SQL.

create or replace procedure DBA.CreateTestTable()
sql security invoker
begin
  create table Test (
     Id     integer not null default autoincrement,
     Field1 integer not null
  );
end;

create user UserTest;
grant DBA to UserTest;

setuser UserTest;
call DBA.CreateTestTable();
setuser;

select * from UserTest.Test;

Could not execute statement.
Table 'Test' not found
SQLCODE=-141, ODBC 3 State="42S02"

Why does this create table DBA.Test and not UserTest.Test? Is this an "undocumented feature" or expected behaviour?

Accepted Solutions (1)

Accepted Solutions (1)

johnsmirnios
Advisor
Advisor
0 Kudos

If you look in sysprocedure you will see that the table name ended up getting fully qualified: ... create table "dba"."Test" ...

If you have a reference to a table in a SELECT statement in a procedure with SQL SECURITY INVOKER, it is left unqualified.

I don't know if the difference in behaviour is a bug or intentional. You could likely work around it by using EXECUTE IMMEDIATE for the CREATE TABLE statement.

Chris26
Participant
0 Kudos

I know I can work around it. It's just that the procedure I'm trying to create contains about 20 tables and is about 3000 lines in total. That makes it "a bit hard", to say the least, if I need to put that in strings and use EXECUTE IMMEDIATE.
I also looked at using indirect indentifiers for the owner, but as far as I can tell that doesn't work with CREATE statements.

johnsmirnios
Advisor
Advisor
0 Kudos

Only the CREATE TABLE statements need to go into EXECUTE IMMEDIATE. I cannot think of any other work-arounds at the moment. I've sent a question to the QP team to see if this is a bug or expected behaviour. I'll post when I get a response.

VolkerBarth
Contributor
0 Kudos

FWIW, TABLE REFs won't do, either:

  • Table reference variables cannot be used to specify tables in DDL statements.

Would it be an acceptable workaround to create the tables (probably with owner specification) and then finally use a bunch of EXECUTE IMMEDIATE statements with "'ALTER TABLE ... ALTER OWNER TO ' || INVOKING USER || ' PRESERVE....'"?

johnsmirnios
Advisor
Advisor
0 Kudos

So far, the response from QP is that they agree the current behaviour isn't desirable; however, changing it could affect customers that rely on it so the behaviour is unlikely to be changed.

Answers (0)