RE: Protecting schema changes

Following is one approach that I had implemented... (oracle 8i and up). The credit goes to Joe Testa for posting it 3 years ago.... :)

• Kirti

>On Thu, 6 Jan 2000, Joseph Testa wrote:
> Why would you want to do that, well, i'm at a place where the developers
> have the schema owner password but we DBAs(being retentitive as we are)dont
> want them to make any ddl changes.
>
>
> here are the steps:
>
> Feel free to change the names to your liking
>
> create user schema_control identified by <passwd>
> grant create any trigger to schema_control;
>
> edit the 3 following triggers, changing the <SCHEMA_NAME> to the schema you
> want to put the control on:
>
> create or replace trigger create_control_trigger
> before create on <SCHEMA_NAME>.schema
>
> begin
> raise_application_error(-20001,'NO CREATE DDL ALLOWED');
> end;
> /
>
> create or replace trigger drop_control_trigger
> before drop on <SCHEMA_NAME>.schema
>
> begin
> raise_application_error(-20001,'NO DROP DDL ALLOWED');
> end;
> /
>
> create or replace trigger alter_control_trigger
> before alter on <SCHEMA_NAME>.schema
>
> begin
> raise_application_error(-20001,'NO ALTER DDL ALLOWED');
> end;
> /
>
>
> revoke alter any trigger from <SCHEMA_NAME>;
>
> the previous line is needed so they dont alter the trigger to disable it :)
>
> the next time they attempt to create, drop or alter anything in that schema
> they get error msg.
>
> This all assumes they are not dba :)
>
> hht, joe
>
 

-----Original Message-----
Sent: Friday, January 10, 2003 1:50 PM
To: Multiple recipients of list ORACLE-L

In a n-tier system that connects to the database with JDBC, how does a DBA keep developers from modifying the application schema without the DBA's consent or knowledge in a centralized development environment? The developers can have their own personal database on their desktops to program/test with but we have a problem with them making changes to the main development database as the application schema owner. They know/have the application schema username/password since it is used to make the JDBC connections to the database from the app server. Does anyone have links to defined change control processes that might help? Thanks in advance!
Val
Valerie H. Webber
Management Systems Designers, Inc
Database Administrator
valerie.h.webber_at_irs.gov
704-566-5321

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).