Today I want to share some simple trigger to set the database wise auditing.
Method1:
CREATE TABLE ddl_audit_log (
stamp DATE,
username VARCHAR2(30),
osuser VARCHAR2(30),
machine VARCHAR2(30),
terminal VARCHAR2(30),
operation VARCHAR2(30),
objtype VARCHAR2(30),
objname VARCHAR2(30))
/
CREATE OR REPLACE TRIGGER fkhalid.audit_ddl_changes
AFTER create OR drop OR alter
ON FKHALID.SCHEMA -- for individual schema auditing
-- ON DATABASE -- for whole database auditing
BEGIN
INSERT INTO fkhalid.ddl_audit_log VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER')||' FOR '||sys.dictionary_obj_owner,
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;
/
show errors
-- Now, let's try that
CREATE TABLE my_test_table (col1 DATE)
/
DROP TABLE my_test_table purge
/
SELECT * FROM ddl_audit_log
/
Method 2:
CREATE TABLE FKHALID.EVENT_TABLE
(
OBJECT_OWNER VARCHAR2(100 BYTE),
OBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_TYPE VARCHAR2(20 BYTE),
DATE_CREATED DATE,
DDL_TYPE varchar2(50)
);
create or replace trigger fkhalid.after_create_trg
after create
on fkhalid.schema
begin
/* This trigger tracks the creation of table, index, sequence,
procedure, function, package and type. */
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
OR sys.dictionary_obj_type='PROCEDURE'
OR sys.dictionary_obj_type='INDEX'
OR sys.dictionary_obj_type='FUNCTION'
OR sys.dictionary_obj_type='TYPE'
OR sys.dictionary_obj_type='PACKAGE'
then
insert into fkhalid.event_table values
(sys.dictionary_obj_owner,sys.dictionary_obj_name, sys.dictionary_obj_type, sysdate,'CREATE');
end if;
end;
/
create or replace trigger fkhalid.after_alter_trg
after alter
on fkhalid.schema
begin
/* This trigger tracks the creation of table, index, sequence,
procedure, function, package and type. */
if sys.dictionary_obj_type='TABLE'
OR sys.dictionary_obj_type='SEQUENCE'
OR sys.dictionary_obj_type='PROCEDURE'
OR sys.dictionary_obj_type='INDEX'
OR sys.dictionary_obj_type='FUNCTION'
OR sys.dictionary_obj_type='TYPE'
OR sys.dictionary_obj_type='PACKAGE'
then
insert into fkhalid.event_table values
(sys.dictionary_obj_owner,sys.dictionary_obj_name, sys.dictionary_obj_type, sysdate,'ALTER');
end if;
end;
/
alter table test_table2
modify (ename varchar2(100),enumber number);
create table test_table2
(ename varchar2(20),enumber number);
Method 3:
SHOW PARAMETER AUDIT
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SHUTDOWN
STARTUP
-----------------------------------------------------
CREATE USER audit_test IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp1
QUOTA UNLIMITED ON users;
GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;
AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
-----------------------------------------------------
CREATE TABLE test_tab (
id NUMBER
);
INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;
DROP TABLE test_tab;
-----------------------------------------------------
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'audit_test'
ORDER BY timestamp;
Hope this will help
Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
