For my Oracle database I created a user. I want that user to have access to only 3 tables. So I wrote those queries:
grant select on table1 to newuser;
grant select on table2 to newuser;
grant select on table3 to newuser;
And I got this from the console, which ensures that I gave the grant.
GRANT succeeded
However when I connect to database with this user and write the following query, I get ORA-00942 error.
Select * from table1;
I think I need to write additional queries for privileges and roles(I already added CONNECT role). What might it be?
Assume that,
NEWUSER --> THE user to which a grant has been provided.
EXISTINGUSER --> The owner of the table for which a grant is provided.
Login as EXISTINGUSER, and enter following query:
Login as NEWUSER, and select using:
In case you want to avoid using "EXISTINGUSER"."TABLE1", then you can create a synonym, which is equivalent to a ALIAS NAME:
Login as NEWUSER, enter following query:
For selecting data from synonym, login as NEWUSER and select using:
Synonym Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm
Run the query by specifying table owner.
If this doesn't work then either you have not granted access on correct table or you are logged in with wrong user.
remember same table name can exist in multiple schemas.