1.建立日志表
2.建立審計(jì)DDL觸發(fā)器
--1.建立日志表
-- Create table
create table T_ZYK_AUDIT_DDL
(
DDL_TIME DATE,
SESSION_ID NUMBER,
OS_USER VARCHAR2(200),
IP_ADDRESS VARCHAR2(200),
TERMINAL VARCHAR2(200),
HOST VARCHAR2(200),
USER_NAME VARCHAR2(30),
DDL_TYPE VARCHAR2(30),
OBJECT_TYPE VARCHAR2(18),
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SQL_TEXT VARCHAR2(4000)
);
-- Add comments to the columns
comment on column T_ZYK_AUDIT_DDL.DDL_TIME
is '時(shí)間';
comment on column T_ZYK_AUDIT_DDL.SESSION_ID
is '會(huì)話ID';
comment on column T_ZYK_AUDIT_DDL.OS_USER
is '終端OS用戶';
comment on column T_ZYK_AUDIT_DDL.IP_ADDRESS
is '終端IP地址';
comment on column T_ZYK_AUDIT_DDL.TERMINAL
is '終端';
comment on column T_ZYK_AUDIT_DDL.HOST
is '終端主機(jī)名';
comment on column T_ZYK_AUDIT_DDL.USER_NAME
is 'ORACLE用戶名';
comment on column T_ZYK_AUDIT_DDL.DDL_TYPE
is 'DDL操作的類型';
comment on column T_ZYK_AUDIT_DDL.OBJECT_TYPE
is '操作的對(duì)象類型';
comment on column T_ZYK_AUDIT_DDL.OWNER
is '對(duì)象的所有者';
comment on column T_ZYK_AUDIT_DDL.OBJECT_NAME
is '對(duì)象的名稱';
comment on column T_ZYK_AUDIT_DDL.SQL_TEXT
is 'SQL語句';
--2.建立審計(jì)DDL觸發(fā)器
create or replace trigger tri_zyk_ddl
after ddl on olivenan.schema
/*觸發(fā)器功能：對(duì)某個(gè)用戶下的DDL語句進(jìn)行審計(jì)，日志記錄到t_zyk_audit_ddl表中,注:該例子是對(duì)olivenan用戶進(jìn)行審計(jì)*/
declare
sql_text ora_name_list_t;
i integer;
state_sql varchar2(4000);
begin
--獲取DDL語句
for i in 1..ora_sql_txt(sql_text) loop
state_sql := state_sql||sql_text(i);
end loop;
--如果語句長度大于4000，則取前4000個(gè)字符
state_sql :=substrb(state_sql,1,4000);
--插入日志表
insert into t_zyk_audit_ddl
(ddl_time,
session_id,
os_user,
ip_address,
terminal,
host,
user_name,
ddl_type,
object_type,
owner,
object_name,
sql_text)
values
(sysdate,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','TERMINAL'),
sys_context('USERENV','HOST'),
ora_login_user,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name,
state_sql
);
exception when others then
null;
end;
截獲DML動(dòng)作觸發(fā)器
CREATE OR REPLACE TRIGGER capt_sql
BEFORE DELETE OR INSERT OR UPDATE ON t1
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
insert into t_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME,OWNER)
values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY');
end;
