Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: HOW TO? 2 or more users using the same schema?
Ethan Post wrote in message <8ojl2s$1fm$1_at_nnrp1.deja.com>...
>In article <39ac246a_at_news>,
> "Patrick Joyal" <please.reply_at_to.the.newsgroup> wrote:
>> Hi,
>>
>> is there a quick way to have 2 or more users using the same schema,
without
>> having to grant privileges on every object and creating synonyms for
every
>> objects?
>>
>> I could use the same account for each user, but I want to keep a
trace for
>> each
>> user.
>>
>> In other words, I want one schema, and many user using that schema
like if
>> it was their own
>> schema.
>>
>> thanks for your help
>>
>> Patrick
>>
>>
>
>It's actually not that bad. You can write a script that uses the data
>dictionary tables to perform all of the grants and creates all of the
>synonyms. I keep this handy script around and just edit it when I need
>to do somthing like this.
>
>For "put sql here" you want,
>
>select 'grant all to john on ' || owner || '.' || table_name || ' to
>kim;';
>
>*************************************
>set pause off
>set linesize 60
>set heading off
>set term off
>set feedback off
>set pagesize 0
>spool file_name
>put sql here;
>spool off
>@file_name
>exit
>******************************
>
>The other option is to create the synonyms then grant the privs to a
>role and then grant the role to whatever user you want to access the
>objects.
>
>-Ethan
>http://www.freetechnicaltraining.com
>http://www.gnumetrics.com
>
>
That what I did, using a stored procedure instead of a script, except for the role, but that's a good idea.
thanks
Patrick Received on Wed Aug 30 2000 - 14:24:22 CDT