Re: Grants Rights

Select * from schemaname.tablename

Regards
Adil

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<3c080aa5$0$7488$afc38c87_at_news.optusnet.com.au>...
> 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...
Received on Tue Dec 04 2001 - 05:02:52 CST