Getting "ORA-00942: table or view does not exist" when missing REFERENCES privilege

Article by author Kamran Agayev Agamehdi

Kamran Agayev Agamehdi  is an Oracle Certified Professional DBA (9i, 10g) with over 5 years experience with UNIX Systems and with Oracle Databases and author of Oracle Backup & Recovery by Rampant Tech Press.

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

Oops… 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>