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 »