Re: HOW TO? 2 or more users using the same schema?

From: Ethan Post <epost1_at_my-deja.com>
Date: Wed, 30 Aug 2000 18:51:19 GMT
Message-ID: <8ojl2s$1fm$1@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

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Aug 30 2000 - 13:51:19 CDT