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

User · Answer

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 gt  create user schemaA identified by schemaA   User created   SQL gt  create user schemaB identified by schemaB   User created   SQL gt  create user test user identified by test user   User created   SQL gt  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 gt  create table schemaA table1 a number    Table created   SQL gt  create table schemaB table2 a number    Table created   SQL gt  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 gt  select   from schemaA table1  select   from schemaA table1                         ERROR at line 1  ORA-00942  table or view does not exist   SQL gt  select   from schemaB table2  select   from schemaB table2                         ERROR at line 1  ORA-01031  insufficient privileges   SQL gt

User · Answer

In SQL Developer  Everything was working fine and I had all the permissions to login and there was no password change and I could click the table and see the data tab    But when I run query  simple select statement  it was showing  ORA-01031  insufficient privileges  message   The solution is simply disconnect the connection and reconnect   Note  only doing Reconnect did not work for me  SQL Developer Disconnect Snapshot

User · Answer

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

User · Answer

ORA-01031  insufficient privileges Solution  Go to Your System User  then Write This Code    SQL  grant dba to UserName    Put This username which user show this error message    Grant succeeded

User · Answer

for ORA-01031  insufficient privileges  Some of the more common causes are    You tried to change an Oracle username or password without having the appropriate privileges  You tried to perform an UPDATE to a table  but you only have SELECT access to the table  You tried to start up an Oracle database using CONNECT INTERNAL  You tried to install an Oracle database without having the appropriate privileges to the operating-system    The option s  to resolve this Oracle error are    You can have the Oracle DBA grant you the appropriate privileges that you are missing  You can have the Oracle DBA execute the operation for you  If you are having trouble starting up Oracle  you may need to add the Oracle user to the dba group    For ORA-00942  table or view does not exist  You tried to execute a SQL statement that references a table or view that either does not exist  that you do not have access to  or that belongs to another schema and you didn t reference the table by the schema name   If this error occurred because the table or view does not exist  you will need to create the table or view   You can check to see if the table exists in Oracle by executing the following SQL statement   select   from all objects where object type in   TABLE   VIEW   and object name    OBJECT NAME     For example  if you are looking for a suppliers table  you would execute   select   from all objects where object type in   TABLE   VIEW   and object name    SUPPLIERS     OPTION  2  If this error occurred because you do not have access to the table or view  you will need to have the owner of the table view  or a DBA grant you the appropriate privileges to this object   OPTION  3  If this error occurred because the table view belongs to another schema and you didn t reference the table by the schema name  you will need to rewrite your SQL to include the schema name   For example  you may have executed the following SQL statement   select   from suppliers    But the suppliers table is not owned by you  but rather  it is owned by a schema called app  you could fix your SQL as follows   select   from app suppliers    If you do not know what schema the suppliers table view belongs to  you can execute the following SQL to find out   select owner from all objects where object type in   TABLE   VIEW   and object name    SUPPLIERS     This will return the schema name who owns the suppliers table

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

Examples related to oracle

Examples related to privileges