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?

asked 29 Jun '22, 03:39

Christian%20Hamers's gravatar image

Christian Ha...
697131633
accept rate: 42%


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.

permanent link

answered 29 Jun '22, 06:12

John%20Smirnios's gravatar image

John Smirnios
11.9k396165
accept rate: 37%

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.

(29 Jun '22, 06:34) Christian Ha...
Replies hidden

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.

(29 Jun '22, 06:37) John Smirnios

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....'"?

(29 Jun '22, 07:47) Volker Barth

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.

(29 Jun '22, 10:39) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×243
×125

question asked: 29 Jun '22, 03:39

question was seen: 934 times

last updated: 29 Jun '22, 10:39