Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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
Unless you create a synonym for the table, you have to ask for it by schema.tablename, ie, user1.usr1_table.
If you don't want to use the schema name, use the following syntax to create a synonym:
create public synonym usr1_table for usr1_table;
-- Tom Mettling W.T. Chen & Company, Inc. mettlingt_at_wtchen.comReceived on Fri Aug 01 1997 - 00:00:00 CDT