Monday 11 February 2008

Preventing Oracle DDL Unauthorized Schema Changes

One of the biggest problems in Oracle are "unexpected" schema changes. In production environments changes are often allowed only during special maintenance times, and changes such as re-analyzing schema statistics and DDL to change table/index structures must be prohibited at other times.

This can be tracked with an Oracle DDL trigger, but the DBA can disable DDL at the object level with a simple script to "disable table lock" syntax:

alter table mbsdba.t_dept disable table lock;

This script could be used to lock all tables within a schema:

connect sys/xxx as sysdba;
spool runme.sql
select "alter table "||owner||"."||table_name||" disable table lock;"
from
dba_tables
where
owner = "mbsdba";

spool off;
@runme.sql

While locked, any attempted schema changes will be rejected
SQL>ALTER TABLE mbsdba.t_dept ADD (NEWCOL NUMBER);

ALTER TABLE mbsdba.t_dept
*

ERROR at line 1:
ORA-00069: cannot acquire lock-- table locks disabled for mbsdba.t_dept