From: "Daniel A. Morgan" <dmorgan@exesolutions.com>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Re: Schemas and authorizations
Date: Sat, 05 May 2001 16:22:34 -0700
Organization: EXE
Message-ID: <3AF48B3A.6CC5B91C@exesolutions.com>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
References: <01c0d5a9$61ff8960$2eaa79c3@hester>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yabetcha.drizzle.com!unknown@win07.drizzle.com
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
X-Complaints-To: newsabuse@supernews.com
Lines: 25


Wolf 'n Pinguin wrote:

> 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

The easy way is to write a stored procedure that goes through USER_OBJECTS
selecting object_name and object_type. Then builds a string with the object
privileges you want and grants them to a role using dynamic SQL.

Then you can assign the role to any user you wish.

Daniel A. Morgan