Re: How to share a schema with other users?

Benjamin Lai <ywlaiben_at_so-net.com.hk> wrote in message news:<3D3117F7.5F2005C4_at_so-net.com.hk>...
> Thank you for the hint Sybrand!
>
> I've looked up the documentation for the use of
> synonyms. I think what I can do is:
>
> 1] connect as a dba
> 2] create synonym <schema1>.<table_name> for <schema2>.<table_name>;
>
> Am I right?
>
> It seems I have to repeat this procedure for every single
> table I have created. Is there really a once-for-all method to achieve
> the same effect ? I really want to share the whole schema instead
> of the tables one by one. Hope that you don't feel I am too demanding.
> I just feel that this is a common problem that many DBAs have
> experienced.
>

It is a common problem and most DBAs' solution is to create public synonyms for every table. It's not a lot of work. But if you really don't want to use synonyms, have your users run,
alter session set current_schema=<schema2>; They can then access the tables without having to qualify them. This is not what I would do though.

>
> Sybrand Bakker wrote:
>
> > "Benjamin Lai" <ywlaiben_at_so-net.com.hk> wrote in message
> > news:3D3060DA.BCAF9301_at_so-net.com.hk...
> > > I have created a number of tables/views using
> > > a DBA role user. What I want to do is the allow
> > > other less privleged users to share this schema's
> > > data. After I granted the select right to
> > > other users, I found that other users still have to access
> > > the tables with the schema name before the table name, like
> > >
> > > select * from <schema name>.<table name>
> > >
> > > Does anybody know if there's any way to simplify this
> > > troublesome naming scheme? Can I do anything to
> > > allow other less privileged users to access the tables
> > > by the table names only?
> > >
> > > Any opinion is welcome!
> > >
> >
> > You'll need to create private or public synonyms.
> > Please read up on synonyms in your Oracle documentation.
> >
> > Regards
> >
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
> >
> > to reply remove '-verwijderdit' from my e-mail address
Received on Sun Jul 14 2002 - 06:51:52 CDT