Re: Create object in other schemas?

From: Heikki Siltala <abcwebmasterxyz_at_abcheikkisiltalaxyz.abccomxyz>
Date: Fri, 01 Dec 2006 10:12:18 +0200
Message-ID: <vtRbh.63557$oL4.26111@reader1.news.jippii.net>

DA Morgan kirjoitti:
> 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).

We have been using this solution in a production enviroment for several years and it works OK.

We have a consolidated Oracle database where each schema represents a dataset which means that each schema is "a logical subdatabase". On the other hand we have a rule that each user (read: person) accessing the database must be correclty identified so logging in using schema owner's username & password is strictly forbidden.

The only way to implement this was to grant "subdatabase administrators" DROP ANY..., CREATE ANY..., ALTER ANY... rights and then build a control DDL trigger that controls on what schema(s) a user can execute DDL operations. Received on Fri Dec 01 2006 - 02:12:18 CST