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.

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