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

Hi,
You have to identify the owner of the table in your select statement. example:
as user1 select * from user2.usr2_table
a way around this is to create public synonyms for the tables of each user
example
as system create public synonym user2_table for user2.user2_table; same for user1 table.

hope this helps.
Dick Received on Tue Jul 29 1997 - 00:00:00 CDT