Re: How to grant table access to

Erwan Layman wrote:
>
> I'm new at using Oracle and I have some difficulty granting table access
> to other users. Here's the situation:
>
> I log on as USER1 (with CONNECT, RESOURCE and DBA access granted) and
> created a table named USR1_TABLE with one field defined. I then grant
> all access to USER2 using,
>
> GRANT ALL ON USR1_TABLE TO USER2;
>
> I log on as USER2 (with CONNECT, RESOURCE and DBA access also) and
> created a table named USR2_TABLE with one field defined. Conversely, I
> grant all access to USER1 using,
>
> GRANT ALL ON USR2_TABLE TO USER1;
>
> Both users have the same default tablespace. Problem is, after inserting
> a few records into each table from the respective user account, USER1
> cannot select any records from USR2_TABLE and vice versa, USR2 cannot
> select from USR1_TABLE. I get the error,
>
> ORA-00942: table or view does not exist
>
> The tables are definitely created successfully because I can select the
> rows in USR1_TABLE when logged on as USER1 and vice versa.
>
> Can anyone tell me where I went wrong?
>
> many thanx,
> Erwan Layman

Are you qualifying the table names in your selects?

        select * from user1.usr1_table;

You can avoid this if you create a synonym.

	create synonym usr1_table for user1.usr1_table;
	select * from usr1_table;

Finally, don't forget that a user must issue a COMMIT before any other users can see the data.

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com