Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 11, 2004

DDL Event Security in Oracle Database

By Amar Kumar Padhi

We have various levels of security (inside and outside of Oracle database) that can be implemented according to one's requirements. Mentioned here is a way of implementing security against structural changes or Data Definition Language (DDL) changes. This security is put within the database for logical objects.

What it means for the DBA

DDL commands are critical and cannot be rolled back. As a norm, new developments/changes should be done on test boxes before promoting them to Production. Such promotions to production can be done as scheduled upgrades during off-peak hours or when a maintenance window is available as system downtime. At all other times the system should be up and running as desired.

DDL event security is aimed at preventing structural changes when the site application is up and running. An application user may never fire such commands explicitly or may not have access to do so, but then, this security can be implemented in general and not aimed at a specific set of users. For example, a script may be fired on the production database by the IT teamin error.

Commands such as CREATE, ALTER, DROP and TRUNCATE can be tracked and audited or prevented from executing. The requirement here is to maintain the stability and availability of the system and prevent mishaps when the users are working.

The idea is to track down DDL commands when they are fired. This can be done using system event triggers introduced in Oracle 8i. Mentioned below is a simple process that I use at my site. If you plan to set up something similar, you can use the code below or modify it as per your needs.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM