on 2022 Jun 29 4:39 AM
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?
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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....'"?
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.