|
Using v17.0.11.6933 Log in as user DBA. 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? |
|
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. 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.
(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:
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
|
