Oracle DDL trigger--Watch DB operationQuite 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
Here is the script.
------------------------------------
-- 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;
/
Oracle DDL trigger
最新推荐文章于 2021-04-05 23:28:17 发布