Tags

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
sys.audit_ddl_trg after ddl on database
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;
/

By running the above script, a table and a trigger will be created. The trigger will practically updates the table on all the DDL takes place on the database by specifying which osuser is actually executed the DDL command and from which terminal. By selecting from this table, we may know who is the user/party/team actually dropped or altered any object in the database. Please see below for the example:

SQL> select * from AUDIT_DDL;

no rows selected

SQL> conn ctsadm/ctsadm
Connected.

SQL> CREATE TABLE test_ddl (
2 d date,
3 USERNAME varchar2(20),
4 TYPE varchar2(30),
5 STATUS varchar2(15));

Table created.

SQL> conn /as sysdba
Connected.
SQL> col OSUSER for a10
SQL> col CURRENT_USER for a10
SQL> col HOST for a15
SQL> col TERMINAL for a10
SQL> col OWNER for a20
SQL> col TYPE for a15
SQL> col NAME for a10
SQL> col SYSEVENT for a15
SQL> set lines 148
SQL> set pages 9000
SQL> select * from audit_ddl;

D OSUSER CURRENT_US HOST TERMINAL OWNER TYPE NAME SYSEVENT
——— ———- ———- ——- ———- ——– ——— ——– ——–
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL CREATE

SQL> conn ctsadm/ctsadm
Connected.
SQL> alter table TEST_DDL modify (USERNAME varchar2(30));

Table altered.

SQL> conn /as sysdba
Connected.

SQL> select * from audit_ddl;

D OSUSER CURRENT_US HOST TERMINAL OWNER TYPE NAME SYSEVENT
——— ———- ———- ——– ———– ——— ——— ——- ——-
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL CREATE
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL ALTER

SQL> conn ctsadm/ctsadm
Connected.

SQL> drop table TEST_DDL;

Table dropped.

SQL> conn /as sysdba
Connected.

SQL> select * from audit_ddl;

D OSUSER CURRENT_US HOST TERMINAL OWNER TYPE NAME SYSEVENT
——— ———- ———- ——— ———- ———– ——— ———- ———–
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL CREATE
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL ALTER
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL ALTER
21-JAN-10 oracle SYS bina26 pts/1 CTSADM TABLE TEST_DDL DROP

As you all can see, all the DDL executed is updated on the table. The osuser column show “oracle” and terminal column will show the terminal. From this I can determine that the changes is done by someone who connecting as “oracle” where usually in Client environment it will be the DBA team. From this output, we can prove to any other team (usually application team) who blame us (DBA) for any missing objects on the database.

By Manoj Devan