DDL和客户端ip监控
2024-09-02 22:16:23
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;
最新文章
- 简析Geoserver中获取图层列表以及各图层描述信息的三种方法
- 15、ASP.NET MVC入门到精通——MVC-路由
- 【leetcode】Implement strStr() (easy)
- Delphi按下F1不能出现帮助文档的解决方法
- windows下安装PhpDocumentor(phpdoc)笔记
- js事件小记
- java_常用数据类型转换基础篇
- 基于FPGA的按键扫描程序
- Jquery中Ajax异步请求中的async参数的作用
- [转载]WCF 几种常见错误
- 分享一下个人的Vim配置文件
- ASP.NET自定义控件组件开发 第五章 模板控件开发
- Android四大组件(详细总结)
- linux模块驱动之led（ioremap）
- 【.net】未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序解决办法
- python网络编程（UDP+广播）
- NewSQL和TiDB入门
- Python新手入门英文词汇（1-1）
- 使用axios加入进度条
- springboot自定义SpringApplication启动类