Using Oracle DDL Triggers
Auditing database changes: Oracle
provides DDL triggers to audit all schema
changes and can report the exact change,
when it was made, and by which user.
There are several ways to audit within
Oracle and the following auditing tools are
provided:
SQL audit command (for DML)
Auditing with object triggers (DML auditing)
Auditing with system-level triggers (DML and
DDL)
Auditing with LogMiner (DML and DDL)
Fine-grained auditing (select auditing)
DDL triggers: Using the Data
Definition Language (DDL) triggers, the
Oracle DBA can automatically track all
changes to the database, including changes
to tables, indexes, and constraints. The
data from this trigger is especially useful
for change control for the Oracle DBA.
DDL triggers execute every time a DDL
statement is executed, and adds new entries
to your new table, as shown below:
connect sys/manager
create or replace trigger
DDLTrigger
AFTER DDL ON DATABASE
BEGIN
insert into
perfstat.stats$ddl_log
(
user_name,
ddl_date,
ddl_type,
object_type,
owner,
object_name
)
VALUES
(
ora_login_user,
sysdate,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name
);
END;
/
What is
truly exciting is that we can combine the
system-level triggers with STATSPACK
extension tables, thereby building a
mechanism that will give complete auditing
information on user logon and logoff, DDL,
and server errors.
Just as an Oracle trigger fires on a
specific DM event, system-level triggers are
fired at specific system events such as
logon, logoff, database startup, DDL
execution, and servererror triggers
DDL triggers -- Using Oracle DDL triggers,
the Oracle administrator can automatically
track all changes to the database including
changes to tables, indexes, and constraints.
The data from this trigger is especially
useful for change control for the Oracle
DBA.
DDL table -- Using the code snippet shown
below, we create an Oracle table to capture
all of the salient metrics required to do
effective change management within an Oracle
environment. Note this table contains the
date that the DDL was made, the ID of the
user who originated the DDL change, the type
of the object, and the object's name. This
information can be quite useful for tracking
purposes.
DDL
Triggers - DDL triggers execute every
time a DDL statement is executed and adds
new entries to the stats$ddl_log table.
DDL Reports - This report is critical
for the Oracle DBA who must track changes to
their production database. This report uses
Oracle DDL triggers and produces a complete
audit log of all Oracle databases changes.
|
|
