From: nsouto@nsw.bigpond.net.au.nospam (Nuno Souto)
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Re: Schemas and authorizations
Organization: D.C.S. P/L
Message-ID: <3af4a2b2.2049619@news-server>
References: <01c0d5a9$61ff8960$2eaa79c3@hester>
X-Newsreader: Forte Free Agent 1.21/32.243
Lines: 22
Date: Sun, 06 May 2001 01:10:46 GMT
NNTP-Posting-Host: 144.132.164.247
X-Complaints-To: news@bigpond.net.au
X-Trace: news-server.bigpond.net.au 989111446 144.132.164.247 (Sun, 06 May 2001 11:10:46 EST)
NNTP-Posting-Date: Sun, 06 May 2001 11:10:46 EST


On 5 May 2001 21:21:21 GMT, "Wolf 'n Pinguin"
<duif.op.spijkers@wxs.nl> wrote:

>
>I wish to create a user, and allow it SELECT privileges on all tables/views
>in SCHEMA1 and SCHEMA2, but not on other schemas.
>

Grant the privileges to a role, then grant the role to the user. 
You realize of course that if the schemas are identical, the user will
have to refer to objects in them by "schema_name.object_name", don't
you?

You can use PL/SQL to create the grant statements, or just use SQL
itself in SQL*PLus to generate them.  If you don't know how to do
this, then I'm sure a lot of others here will be able to help you with
that.

Cheers
Nuno Souto
nsouto@bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html