DDL触发器监控脚本部署步骤
以下操作请使用sys用户：
--第一步：创建表（此表主要保存ddl触发器产生的信息），可以根据不同的业务，使用相关的监控用户，在此监控用户为c##upctest
从可维护性考虑，此表要长期保存ddl触发器产生的信息，建议采用范围分区.
CREATE TABLE c##upctest.STATS$DDL_AUDIT
( USER_NAME VARCHAR2(30) ,
SID NUMBER,
INST_ID NUMBER,
IP_ADDRESS VARCHAR2(20),
OSUSER VARCHAR2(30),
MACHINE VARCHAR2(64),
OBJ_OWNER VARCHAR2(30),
OBJ_NAME VARCHAR2(128),
OBJ_TYPE VARCHAR2(18),
DDL_TIME DATE,
DDL_TYPE VARCHAR2(30),
DDL_SQL VARCHAR2(4000)
)
PARTITION BY RANGE (DDL_TIME)
(PARTITION P_R_201912 VALUES LESS THAN (TO_DATE('2019-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_R_202001 VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_R_202002 VALUES LESS THAN (TO_DATE('2020-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_R_202003 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202004 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202005 VALUES LESS THAN (TO_DATE('2020-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202006 VALUES LESS THAN (TO_DATE('2020-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202007 VALUES LESS THAN (TO_DATE('2020-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202008 VALUES LESS THAN (TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202009 VALUES LESS THAN (TO_DATE('2020-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202010 VALUES LESS THAN (TO_DATE('2020-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202011 VALUES LESS THAN (TO_DATE('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202012 VALUES LESS THAN (TO_DATE('2020-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_PMAX VALUES LESS THAN (TO_DATE('2099-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
)
COMMENT ON COLUMN STATS$DDL_AUDIT.USER_NAME IS '登陆用户名';
COMMENT ON COLUMN STATS$DDL_AUDIT.SID IS 'session id';
COMMENT ON COLUMN STATS$DDL_AUDIT.INST_ID IS '实例号';
COMMENT ON COLUMN STATS$DDL_AUDIT.IP_ADDRESS IS '客户端ip地址';
COMMENT ON COLUMN STATS$DDL_AUDIT.OSUSER IS '客户端操作系统用户名';
COMMENT ON COLUMN STATS$DDL_AUDIT.MACHINE IS '客户端执行的机器';
COMMENT ON COLUMN STATS$DDL_AUDIT.OBJ_OWNER IS 'DDL操作对象的所有者';
COMMENT ON COLUMN STATS$DDL_AUDIT.OBJ_NAME IS 'DDL操作所对应的数据库对象名';
COMMENT ON COLUMN STATS$DDL_AUDIT.OBJ_TYPE IS 'DDL操作所对应的数据库对象的类型';
COMMENT ON COLUMN STATS$DDL_AUDIT.DDL_TIME IS 'DDL操作时间';
COMMENT ON COLUMN STATS$DDL_AUDIT.DDL_TYPE IS '触发器的系统事件名称';
COMMENT ON COLUMN STATS$DDL_AUDIT.DDL_SQL IS 'DDL语句';
--2：创建ddl监控触发器(表STATS$DDL_AUDIT的用户是c##upctest,可根据自己的业务进行修改)
CREATE OR REPLACE TRIGGER sys.TRG_DDL_AUDIT
AFTER DDL ON DATABASE
DECLARE
v_sql_text ora_name_list_t;
v_ddl_sql c##upctest.STATS$DDL_AUDIT.ddl_sql%TYPE;
v_len NUMBER;
v_piece_len NUMBER;
v_sid NUMBER;
v_machine VARCHAR2(64);
v_osuser VARCHAR2(30);
BEGIN
-- Get DDL SQL statement
v_len := 0;
FOR i IN 1..ora_sql_txt(v_sql_text) LOOP
v_piece_len := length(v_sql_text(i));
EXIT WHEN v_len + v_piece_len > 4000;
v_ddl_sql := v_ddl_sql||v_sql_text(i);
v_len := v_len + v_piece_len;
END LOOP;
-- get sid and machine from audsid
SELECT sid,machine,osuser INTO v_sid,v_machine,v_osuser
FROM v$session
where sid=userenv('sid');
-- insert to audit table
INSERT INTO c##upctest.STATS$DDL_AUDIT
(
user_name,
sid,
inst_id,
ip_address,
osuser,
machine,
obj_owner,
obj_name,
obj_type,
ddl_time,
ddl_type,
ddl_sql
)
VALUES
(
ora_login_user,
v_sid,
ora_instance_num,
sys_context('userenv','ip_address'), -- ora_client_ip_address seems no use here?
v_osuser,
v_machine,
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
SYSDATE,
ora_sysevent,
v_ddl_sql
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END trg_ddl_audit;
此触发器可适用操作范围: alter ,drop create ,truncate ,analyze,comment,grant,revoke,rename,noaudit,audit,disassociate statistics,associate,statistics
以及分区表针对分区的truncate,drop ,split,add,exchange操作(因为分区表的这些操作都属于alter)
第三步：STATS$DDL_AUDIT表维护
1：首先创建好下一年的表STATS$DDL_AUDIT_2020
CREATE TABLE c##upctest.STATS$DDL_AUDIT_2020
( USER_NAME VARCHAR2(30) ,
SID NUMBER,
INST_ID NUMBER,
IP_ADDRESS VARCHAR2(20),
OSUSER VARCHAR2(30),
MACHINE VARCHAR2(64),
OBJ_OWNER VARCHAR2(30),
OBJ_NAME VARCHAR2(128),
OBJ_TYPE VARCHAR2(18),
DDL_TIME DATE,
DDL_TYPE VARCHAR2(30),
DDL_SQL VARCHAR2(4000)
)
PARTITION BY RANGE (DDL_TIME)
(
PARTITION P_R_202001 VALUES LESS THAN (TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_R_202002 VALUES LESS THAN (TO_DATE('2020-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_R_202003 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202004 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202005 VALUES LESS THAN (TO_DATE('2020-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202006 VALUES LESS THAN (TO_DATE('2020-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202007 VALUES LESS THAN (TO_DATE('2020-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202008 VALUES LESS THAN (TO_DATE('2020-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202009 VALUES LESS THAN (TO_DATE('2020-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202010 VALUES LESS THAN (TO_DATE('2020-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202011 VALUES LESS THAN (TO_DATE('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_202012 VALUES LESS THAN (TO_DATE('2020-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) ,
PARTITION P_R_PMAX VALUES LESS THAN (TO_DATE('2099-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
)
2：alter TRIGGER sys.TRG_DDL_AUDIT disable;
--3，4 两步在c##upctest用户下执行
3:rename STATS$DDL_AUDIT to STATS$DDL_AUDIT_2020
4:rename STATS$DDL_AUDIT_2020 to STATS$DDL_AUDIT;
5:alter trigger sys.TRG_DDL_AUDIT compile;
6:alter TRIGGER sys.TRG_DDL_AUDIT enable;
客户端ip触发器脚本监控
功能说明：记录客户端ip地址到v$session视图中的client_info 字段中
CREATE OR REPLACE TRIGGER SYS.TRG_USER_LOGON
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
