Re: Schemas and authorizations

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Sat, 05 May 2001 16:22:34 -0700
Message-ID: <3AF48B3A.6CC5B91C@exesolutions.com>

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 Received on Sat May 05 2001 - 18:22:34 CDT