Re: Can a DBA restrict privileges of a user that is the owner of a schema? We need to remove DDL privileges. Our DBA says it is impossible. Please help!

On 15 Aug 2006 15:54:02 -0500, mistonl_at_mail.com (Mistton) wrote:

>using 9.2.0.3.0 on Sun Unix 15k server (i know its old but that is what we
>have)
>
>we have following situation:
>
>User STEPH is the owner of schema WORK. as such he has full DML and DLL
>privileges.
>
>Due to production access direcitves from managment, we need to somehow
>restrict STEPH to have just DML on WORK schema. Our DBA said this is not
>possible, which means we would have to create a new user and change all our
>code that references STEPH.
>
>is it possible to restrict? is there some workaround?
>
>thanks in advance

It is not really possible to restrict. However with 8i and higher you can create triggers on DDL statement (in order to raise an error in your case)
Actually I would implement the idea of your DBA - lockdown the schema owner
- create a new owner with select, insert, update, delete on the original tables
- create synonyms for every table, view, function, procedure, package

• No need to change your code.

In the former solution you end up with a kludgy system. The second (and older) alternative is transparent and more easy to implement.

--
Sybrand Bakker, Senior Oracle DBA