Re: Create object in other schemas?

wagen123_at_yahoo.com wrote:
> Oracle10g 10.2.0.1
>
>
> I have the following request:
>
> User1 should be able to create/drop objects in schemas A, B and C ONLY.
> There are other schemas in the database such as D,E,F + the system
> accounts where user1 is not permitted to create/drop objects.
>
> Any idea of how this can be accomplished without writing procedures,
> etc.
>
> Thanks

Two thoughts.

The first is that you could grant DROP ANY TABLE to User1 and then write a DDL Event Trigger to RAISE an exception if the table's owner isn't either A, B, or C. (same code in Morgan's Library at www.psoug.org under DDL EVENT TRIGGER).

The second thought is that this is a horrible design and you should find someone to help you fix it.

I'd recommend dealing with the second thought before the first.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org