Hello Masters,
I saw something weird last time and I need your expertise to understand.
I create two users.
[ora1210[XXX] /home/ora1210]$ sqlplus / as sysdba
SQL> create user ZZ01 identified by xxxxxxxxxxxxxxxxxxxx;
SQL> create user ZZ02 identified by xxxxxxxxxxxxxxxxxxxx;
SQL> grant create session, connect, create any table to ZZ01;
SQL> grant create session, connect to ZZ02;
The user ZZ01 create a table in another schema, want to read it but he had an error message.
It is normal because he was not granted the right to SELECT on this table.
BUT what is weird is that the message is not ORA-00942 but ORA-01031 : Oracle knows that ZZ01 has created this table...
SQL> connect ZZ01
SQL> create table system.ZZTEST(ID NUMBER);
SQL> select owner from dba_tables where table_name = 'ZZTEST';
OWNER
-----------
SYSTEM
SQL> select * from system.ZZTEST;
select * from system.ZZTEST
*
ERREUR a la ligne 1 :
ORA-01031: insufficient privileges
The user ZZ02 has the error message I expected : "ORA-00942: table or view does not exist" because he has no privilege on this table.
SQL> connect ZZ02
SQL> select * from system.ZZTEST;
select * from system.ZZTEST
*
ERREUR a la ligne 1 :
ORA-00942: table or view does not exist
If I quit my session and re-connect : same thing with ZZ01, so it wasn't an information inside my session, Oracle gave to this user the information that this table exists because, I think, he was its creator.
SQL> exit
[ora1210[XXX] /home/ora1210]$ sqlplus ZZ01
SQL> select * from system.ZZTEST;
select * from system.ZZTEST
*
ERREUR a la ligne 1 :
ORA-01031: insufficient privileges
Can you tell me where is this information? I looked inside DBA_TABLES, DBA_OBJECTS, TAB$, OBJ$ but nothing, there is no column "Created by" or "Creator" but only "Owner".
Have a nice day,
David D.
It's not because ZZ01 is the creator. It's because they have the create any table privilege.
Revoke this from ZZ01 and grant it to ZZ02 and you'll see the error messages flip:
grant create any table, create session to
u1 identified by u1;
grant create any table, create session to
u2 identified by u2;
conn u1/u1
create table chris.t (
c1 int
);
conn chris/chris
revoke create any table
from u1;
conn u1/u1
select * from chris.t;
ORA-00942: table or view does not exist
conn u2/u2
select * from chris.t;
ORA-01031: insufficient privileges