Re: Grants Rights

Because "dog" is not actually the name of the table you created. It's actually called 'user_one.dog' -in other words, the schema (username, if you like) in which the table was created is an intrinsic part of the object name.

Now, Oracle thoughtfully and helpfully prepends the current user's name to the front of any unqualified table names supplied in queries. So, whilst still logged in as 'user_one', if I do a select * from dog, Oracle silently turns that into a 'select * from user_one.dog' -which of course exists, and produces no errors.

But if I log in as user_two and do the same query, what is actually submitted is 'select * from user_two.dog' -which obviously doesn't exist, and hence the (perfectly correct) error message.

What you need to do as user_two is explicitly mention the correct schema: 'select * from user_one.dog' will work every time, whoever issues it, provided that user has the permissions to select from that table, of course.

To avoid code having to mention the schema like this all the time, it is often the case that you create a public synonym for the table (in this case, say, 'canines'). That way a 'select * from canines' can be issued by anyone, and will be thunked into a 'select * from user_one.dog' by the database engine itself.

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"doug" <d.smith_at_ins.hcc.mnscu.edu> wrote in message
news:dd3da57f.0111301102.41b47b37_at_posting.google.com...


> I need some help here! I've been using MYSQL and Postgres for quite

> awhile and have not had this problem before. With Oracle9i I can not

> get my users to be able to see each others tables.

>

> As user_one:

>

> create table dog

> (id number(2));

> Grant all on dog to user_two;

>

> insert into dog(id) values('2');

>

> select * from dog;

>

> ----

> id

> ----

> 2

>

> exit

>

> sqlplus user_two/<password>

>

> sql>select * from tab;

> no rows selected.

>

> sql>select * from dog;

> ORA-00942: table or view does not exist

>

> I should also point out I created the users as system/<password>

>

> create user user_one identified by <password>

> grant connect, resource to user_one;

>

> created both isers the same way.

>

> I realize this is over simplistic but I'm going nuts here!

>

> HELP!!!!

>

> Thankx in advnace doug...