ORA-00942: table or view does not exist

Have you come across the error? Then below is for your info…

what is an object privilege?
An object privilege is a privilege or right to perform particular action on a specific table, view, sequence or procedure. Each object has a particular set of granted privileges.

what are the different object privileges and on which objects are they applicable?

Object privilege Table View Sequence
ALTER Yes Yes
DELETE Yes Yes
INSERT Yes Yes
UPDATE Yes Yes
SELECT Yes Yes Yes
REFERENCE Yes
INDEX Yes

Different object privileges are available for different types of schema objects. A user automatically has all object privileges for schema objects contained in the user’s schema. For e.g. if user A create a table employee, then A will have all rights like INSERT, DELETE, UPDATE, etc on the table employee. A user can grant any object privilege on any schema object that the users own to any other user or role.

A privilege granted on a synonym is converted to a privilege on the base table referenced by the synonym.

so when you get the same oracle error, check whether the db objects like table, view, sequence are granted the respective object privileges to the particular user used to connect to oracle database.

NOTE: Even when the object privileges are not available for sequence, you will get the same error “ORA-00942: table or view does not exist”. We faced an issue during SIT testing, an object privilege SELECT on a particular sequence was dropped for a particular user. This sequence was used to populated the primary key column of nearly 20 tables. when we tried to insert record into those tables, we got the same error. Based on the error message, we checked the table object privileges and synonyms, everything was fine. we were not able to analyse the root cause. On further analysis, we found the dropped privilege on the sequence for that user and recreated it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: