Re: How to share a schema with other users?

You would have to either create PUBLIC synonyms, or each user would need to create their own synonyms. This of course assumes that you have already granted permissions to select/insert/update/delete on the tables in question. There are pro's and con's to both. Most people seem to advise against public synonyms.

The common trick would generally be to write a script that will generate the sql that you require.

If you spool something like the following to a file, then run the file. (assuming that userwithdata is the existing schema, and newuser is the user you want to be able to access the objects without using userwithdata.object_name)

set page 0
set heading off
set trimspool on
--permissions
select 'grant select on ' || table_name || ' to newuser;' from user_tables; select 'grant update on ' || table_name || ' to newuser;' from user_tables; ...etc
--synonyms
connect newuser/newpassword
select 'create synonym ' || table_name || ' for userwithdata.' || table_name || ';' from all_tables where owner = 'USERWITHDATA';

Adrian

"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.
>
>
> 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 - 02:57:36 CDT