Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to share a schema with other users?

Re: How to share a schema with other users?

From: Adrian Carlson-Hedges <adrian.ch_at_btinternet.com>
Date: Sun, 14 Jul 2002 07:57:36 +0000 (UTC)
Message-ID: <agratg$foa$1@venus.btinternet.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US