Getting ORA-01031: insufficient privileges while querying a table instead of ORA-00942: table or view does not exist

Question

When I'm querying a table in schema C from schema A, I'm getting ORA-01031: insufficient privileges and when I'm querying the same table from schema B, I'm getting ORA-00942: table or view does not exist. On the table neither of the schemas are having any privileges. Why am I getting different error messages in this case?

Answer 1

You may get ORA-01031: insufficient privileges instead of ORA-00942: table or view does not exist when you have at least one privilege on the table, but not the necessary privilege.

Create schemas

SQL> create user schemaA identified by schemaA;

User created.

SQL> create user schemaB identified by schemaB;

User created.

SQL> create user test_user identified by test_user;

User created.

SQL> grant connect to test_user;

Grant succeeded.

Create objects and privileges

It is unusual, but possible, to grant a schema a privilege like DELETE without granting SELECT.

SQL> create table schemaA.table1(a number);

Table created.

SQL> create table schemaB.table2(a number);

Table created.

SQL> grant delete on schemaB.table2 to test_user;

Grant succeeded.

Connect as TEST_USER and try to query the tables

This shows that having some privilege on the table changes the error message.

SQL> select * from schemaA.table1;
select * from schemaA.table1
                      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from schemaB.table2;
select * from schemaB.table2
                      *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>