Re: Schemas and authorizations

From: Steve Long <steven.long_at_erols.com>
Date: Sat, 5 May 2001 21:46:25 -0400
Message-ID: <9d2aiv$obu$1@bob.news.rcn.net>

connect as system and spool the output of the following to a file:

spool grant.sql

select 'grant select on '||rtrim(owner)||'.'||rtrim(object_name)||';' from dba_objects
where lower(owner) in ('schema1', 'schema2') and lower(object_type) in ('table', 'view) order by owner, object_type,object_name
/

spool off

now edit the spool file to insert connect statements so that the grants for schema1 are run by user schema1 and grants for schema2 are run by user schema2. then run the sql statements as a sql command file.

Original Message ----- From: "Wolf 'n Pinguin" <duif.op.spijkers_at_wxs.nl> Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server Sent: Saturday, May 05, 2001 5:21 PM Subject: Schemas and authorizations

"Wolf 'n Pinguin" <duif.op.spijkers_at_wxs.nl> wrote in message news:01c0d5a9$61ff8960$2eaa79c3_at_hester...
> Hi Everybody,
>
> I have the following problem:
>
> I have a couple of schemas (SCHEMA1, SCHEMA2, SCHEMA3, etc ) each of which
> contains an identical datamodel. The number of schemas can get quite
> large.
>
> I wish to create a user, and allow it SELECT privileges on all
tables/views
> in SCHEMA1 and SCHEMA2, but not on other schemas.
>
> Is there an easy way to accomplish this?
>
> Thanx in advance, Ide
Received on Sat May 05 2001 - 20:46:25 CDT