Kamran Agayev's Oracle Blog

Oracle Certified Master

Archive for November 3rd, 2009

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

Posted by Kamran Agayev A. on 3rd November 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>

Posted in SQL and PL/SQL | 13 Comments »