|
/*
DDL trigger
Quite often, DBAs need to know what DDL operations the users have done in a test environment. Here is the way I can know what they did.
This table and the trigger (you may name them the way you like) should be in SYS, SYSTEM or an account with DBA role. It will store who did what, when and from where (machine and IP), by what method (sqlplus, toad, sql worksheet etc).
There is a limitation: when a DDL operation has more than 2000 characters, it won't go through. For example, when a user is trying to create a new or modify an existing stored procedure, s/he may be in trouble if the code has more than 2000 characters.
I have caught follwoing DDLs:
alter,
analyze,
comment,
create,
drop,
grant,
revoke,
truncate
Sam
*/
-- create table
CREATE table ddl_event (
timestamp date,
user_name varchar2(30),
os_user varchar2(30),
machine varchar2(20),
ip_addr varchar2(20),
program VARCHAR2(30),
event varchar2(20),
Object_name varchar2(30),
object_type varchar2(30),
object_owner varchar2(30),
statement varchar2(256) )
/
-- who changed what and when and how
create or replace trigger ddl_watcher
after ddl on database
when (user not in ('SYS', 'SYSTEM'))
declare
v_osuser varchar2(30);
v_machine varchar2(20);
v_ip_addr varchar2(20);
v_program VARCHAR2(30);
event varchar2(30);
obj_name varchar2(30);
obj_type varchar2(30);
obj_owner varchar2(30);
sql_text ora_name_list_t;
stmt VARCHAR2(256);
n number;
begin
select osuser,
machine,
nvl(program, 'sqlplus'),
sys_context('userenv','ip_address')
into
v_osuser,
v_machine,
v_program,
v_ip_addr
from v$session
where audsid = userenv('sessionid');
-- select sys_context('userenv','ip_address') into v_ip_addr from dual;
-- v_ip_addr := ora_client_ip_address;
event := ora_sysevent;
obj_name := ora_dict_obj_name;
obj_type := ora_dict_obj_type;
obj_owner := ora_dict_obj_owner;
n := ora_sql_txt(sql_text);
if n > 256 then
n:= 256;
end if;
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
insert into ddl_event (timestamp, user_name, os_user, machine, ip_addr,
program, event, object_name, object_type, object_owner, statement)
values (sysdate, user, v_osuser, v_machine, v_ip_addr, v_program,
event, obj_name, obj_type, obj_owner, stmt);
end;
/
|
