DDL和客户端ip监控

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;