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![:)](http://kamranagayev.com/wp-includes/images/smilies/simple-smile.png)
![:)](http://kamranagayev.com/wp-includes/images/smilies/simple-smile.png)
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![:)](http://kamranagayev.com/wp-includes/images/smilies/simple-smile.png)
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![:)](http://kamranagayev.com/wp-includes/images/smilies/simple-smile.png)
November 4th, 2009 at 9:52 am
Nice explanation Laurent![:)](http://kamranagayev.com/wp-includes/images/smilies/simple-smile.png)
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![:mrgreen:](http://kamranagayev.com/wp-includes/images/smilies/mrgreen.png)
November 4th, 2009 at 1:49 pm
Yeap. That’s funny. But have you tried this kind of search?![:)](http://kamranagayev.com/wp-includes/images/smilies/simple-smile.png)
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![:)](http://kamranagayev.com/wp-includes/images/smilies/simple-smile.png)
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