Re: can GRANT give schema level access?

Oracle does not provide the ability to GRANT privileges to a schema, only privileges on objects within the schema.

Users should log on as the schema 'owner' (in your case, loaddata), create the objects, and grant appropriate privileges on the individual objects.

I'd avoid granting users the CREATE ANY .. or ALTER ANY ... system privileges, unless there is a compelling reason that a user needs the privilege.

"Kenneth Topp" <toppk_at_dynasty.norkle.com> wrote in message news:39F9D285.3A552C25_at_dynasty.norkle.com...
> I want to have a schema:
>
> loaddata
>
> where anyone can create tables/drop tables/insert/update/delete,
> basically no permissions.
>
> That way all the other database'ss (ie: dev/qa/prod) can load in some
> initial values via "copy from", etc...
>
> But, in reading oracle's doco's, it seems that I have to chose between:
>
> 1)
> - create role blah
> - allow role read/write (ie: create/drop/table perms) to any schema
> - remove all schemas except "loaddata" from the definition of "any"
>
> 2) - force people to log in as "loaddata"
> - after creating tables' they must grant role permissions.
>
> These options both look bad to me (and I don't even know how,
> specifically to do them),
> perhaps people see a better way?
>
> (oracle 8.1.6/8.1.7 on solaris)
>
> Thanks,
>
> And cc my email address if possible,
>
> Kenneth Topp
>
>
Received on Sat Oct 28 2000 - 20:01:58 CDT