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.
Laurent Schneider also has this
alternative DDL triggers script for DDL
auditing: CREATE
TABLE AUDIT_DDL (
d date,
OSUSER varchar2(255),
CURRENT_USER
varchar2(255),
HOST varchar2(255),
TERMINAL
varchar2(255),
owner varchar2(30),
type varchar2(30),
name varchar2(30),
sysevent
varchar2(30));
create or replace trigger audit_ddl_trg
after ddl on schema
begin
if (ora_sysevent='TRUNCATE')
then
null; -- I do not
care about truncate
else
insert into
audit_ddl(d,
osuser,current_user,host,terminal,owner,type,name,sysevent)
values(
sysdate,
sys_context('USERENV','OS_USER')
,
sys_context('USERENV','CURRENT_USER')
,
sys_context('USERENV','HOST')
,
sys_context('USERENV','TERMINAL')
,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent
);
end if;
end;
/
Now whenever a change is made, the details will be written to
your table, and you can create audit reports for management.
This report is critical for the Oracle DBA who must track
changes to his or her production database. This report uses
the DDL trigger and produces a complete audit log of all
Oracle databases changes, as shown below:
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.
|
and include the URL for the page.
