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>

Answer 2

ORA-01031: insufficient privileges happens when the object exists in the schema but do not have any access to that object.

ORA-00942: table or view does not exist happens when the object does not exist in the current schema. If the object exists in another schema, you need to access it using .. Still you can get insufficient privileges error if the owner has not given access to the calling schema.