CODEWITHSUNDEEP
sqloracle-databaseora-00942
nobody
nobody

Reputation: 1949

Oracle table SELECT rights issue

My application has few tables on Oracle where user XYZ is the schema owner. Tables has been created using XYZ. And I would like to have ABCUSER to have CRUD rights on these tables. I have given the access via GRANT ALL ON TABLEABC to ABCUSER and grant is succeeded.

But when this user(ABCUSER) tries to query the DB (select * from TABLEABC) it doesn't seem to work. I get error message 

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 1 Column: 14

Could you please tell what am I missing ?

Upvotes: 2

Views: 1260

Answers (2)

APC
APC

Reputation: 146219

User ABCUSER has privileges on the table but doesn't own it. So you need to include the schema in the query:

select * from XYZ.TABLEABC
/

If you don't want to hardcode the schema name in your programs your user can build a synonym:

create synonym TABLEABC for XYZ.TABLEABC
/

Then the original query will work for ABCUSER.

Note that ABCUSER will require the CREATE SYNONYM privilege.

Upvotes: 6

Ollie
Ollie

Reputation: 17538

As APC says, you are missing a SYNONYM.

You might want either a PRIVATE or PUBLIC synonym depending upon who you want to be able to view the table.

A good description of the various types and their uses is here: http://www.orafaq.com/wiki/Synonym

Upvotes: 1

Related Questions