Getting "ORA-00942: table or view does not exist" when missing REFERENCES privilege
Posted by Kamran Agayev A. on November 3rd, 2009
SQL> create user usr1 identified by usr1; User created. SQL> grant dba to usr1; Grant succeeded. SQL> create user usr2 identified by usr2; User created. SQL> grant dba to usr2; Grant succeeded. SQL> conn usr1/usr1 Connected. SQL> create table tbl_usr1 (id number); Table created. SQL> conn usr2/usr2 Connected. SQL> create table tbl_usr2 (id number); Table created. SQL> insert into tbl_usr2 values(1); 1 row created. SQL> commit; Commit complete. SQL> alter table tbl_usr2 add primary key(id); Table altered. SQL> create public synonym tbl_usr2 for tbl_usr2; Synonym created. SQL> disc Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> conn usr1/usr1 Connected. SQL> select * from tbl_usr2; ID ---------- 1 SQL> alter table tbl_usr1 add foreign key (id) references tbl_usr2(id); alter table tbl_usr1 add foreign key (id) references tbl_usr2(id) * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from tbl_usr2; ID ---------- 1
Ops… Although I can query the table, while creating foreign key and referencing to that table, I get “ORA-00942: table or view does not exist” error. The reason is that the user hasn’t REFERENCES privilege which should be granted
GRANT REFERENCES is a privilege required by a user on a table so that this user can create new tables referencing such tables in foreign keys where he/she would otherwise be restricted.
SQL> conn usr2/usr2 Connected. SQL> grant references on tbl_usr2 to usr1; Grant succeeded. SQL> conn usr1/usr1 Connected. SQL> alter table tbl_usr1 add foreign key (id) references tbl_usr2(id); Table altered. SQL>
November 3rd, 2009 at 5:19 pm
even as dba? that’s surprising indeed !
another seldom used privilege is GRANT INDEX ON TBL_USER2 TO USR1;
There is however no INDEX ANY TABLE or REFERENCES ANY TABLE privilege, worth an enhancement request? maybe 😉
November 3rd, 2009 at 5:48 pm
Hi Laurent. Nice to meet you here in my Blog
Why it’s strange is because I’ve dba privilege and I can query the table. Why Oracle returns “table or view doesn’t exits”? Is it normal? Shouldn’t it return a different error such as “You haven’t privilege to REFERENCE object” error?
November 3rd, 2009 at 6:53 pm
Kamran, looks like there is a problem with copy paste. How can you connect with a non-existing user usr2 before creating it ?
SQL> create user usr1 identified by usr1;
SQL> conn usr2/usr2
Connected.
SQL> create table tbl_usr2 (id number);
User created.
SQL> grant dba to usr1;
Grant succeeded.
SQL> create user usr2 identified by usr2;
User created.
November 3rd, 2009 at 7:03 pm
step2 &3 : how can you connect usr2 before creating “usr2” user.
Step 9 : how can you insert data into table of user2.because you login as a user1.
Sir, this is bug on your steps..please modify your steps.
but i did it.i understand about ora-00942 reference privilege.
Thanks & Regards
Azar
DBA
November 3rd, 2009 at 7:21 pm
Thanks Mohammad. It was typo (typing error) Let me correct it. As you’ve created the second table in the same schema, you didn’t get this error. Try it once more
November 3rd, 2009 at 11:54 pm
well, if the REFERENCE ANY TABLE would exist, the privilege would be granted to dba, and the dba would get no error
November 4th, 2009 at 9:52 am
Nice explanation Laurent
November 4th, 2009 at 1:39 pm
quite funny that a google search of “grant index any table” gives you a dozen of links describing how to grant this inexistant privilege
November 4th, 2009 at 1:49 pm
Yeap. That’s funny. But have you tried this kind of search?
http://tinyurl.com/yhfxjp7
November 4th, 2009 at 3:15 pm
Hi All
Another Similar issue is
user A(dba) can select user B’s table – tabB. But when user A executes select on B.tabB inside procedure, he gets the same error.
and the solution is grant select on B.tabB specifically
November 10th, 2009 at 5:32 pm
Hi Coskan. Sure, it was typo
P.S. Just saw your post, it was at spam, thus haven’t approved it. Sorry
What’s your opinion on this issue?
November 24th, 2010 at 5:28 pm
dear Mr.karman. thanks for your support but i want to learn more about pl/sql but i am a newbie so please kindly show me what to do so that i will be expert in pl/sql. thanks.
November 24th, 2010 at 5:34 pm
Dear Kasante
I’d suggest you to read the following boook:
http://www.amazon.com/Oracle-PL-SQL-Example-3rd/dp/0131172611