Search code examples
sqloracleora-00942

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 ?

Solution

  • 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.