Re: How to share a schema with other users?

Perhaps best practice would be to grant the privs to a role and then grant the role to the user, saves a lot of admin headache... Also, why not issue the select and update privs in a single statement...

2c..

"Adrian Carlson-Hedges" <adrian.ch_at_btinternet.com> wrote in message news:agratg$foa$1_at_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 - 04:20:02 CDT