|
 |
|
Preventing Oracle DDL unauthorized schema changes
By Burleson Consulting
|
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 sapr3.vbelm 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 = "SAPR3";
spool off;
@runme.sql
While locked, any attempted schema changes will
be rejected, and the attempt can be logged with a
servererror trigger:
SQL>ALTER TABLE SAPR3.VBELM ADD
(NEWCOL NUMBER);
ALTER TABLE SAPR3.VBELM
*
ERROR at line 1:
ORA-00069: cannot acquire lock-- table locks disabled for
SAPR3.VBELM
|
