审核数据库DDL操作记录实现

1. 操作记录表
CREATE TABLE TOPUP.TAB_AUDIT_DDL_RECORD
(
  TIME         DATE,
  SESSION_ID   NUMBER,
  OS_USER      VARCHAR2(200 BYTE),
  IP_ADDRESS   VARCHAR2(200 BYTE),
  TERMINAL     VARCHAR2(200 BYTE),
  HOST         VARCHAR2(200 BYTE),
  USER_NAME    VARCHAR2(30 BYTE),
  DDL_TYPE     VARCHAR2(30 BYTE),
  OBJECT_TYPE  VARCHAR2(18 BYTE),
  OWNER        VARCHAR2(30 BYTE),
  OBJECT_NAME  VARCHAR2(128 BYTE),
  SQL_TEXT     VARCHAR2(4000 BYTE)
)
TABLESPACE BASE_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             20M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.TIME IS '操作时间';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.SESSION_ID IS '会话ID';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.OS_USER IS '终端OS用户';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.IP_ADDRESS IS '终端IP地址';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.TERMINAL IS '终端';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.HOST IS '终端主机名';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.USER_NAME IS 'Oracle用户名';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.DDL_TYPE IS 'DDL操作类型';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.OBJECT_TYPE IS '操作对象类型';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.OWNER IS '对象所有者';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.OBJECT_NAME IS '对象名称';

COMMENT ON COLUMN TOPUP.TAB_AUDIT_DDL_RECORD.SQL_TEXT IS 'DDL语句';

 

CREATE INDEX TOPUP.IDX_TAB_AUDIT_DDL_RECORD ON TOPUP.TAB_AUDIT_DDL_RECORD
(TIME)
LOGGING
TABLESPACE BASE_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

GRANT ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE ON TOPUP.TAB_AUDIT_DDL_RECORD TO SYSTEM;

 

2. 操作记录TRIGGER

CREATE OR REPLACE TRIGGER SYSTEM.TRG_AUDIT_DB_DDL
   BEFORE DDL
   ON DATABASE
DECLARE
   user_var         VARCHAR2 (200);
   login_user_var   VARCHAR2 (200);
   ip_add_var       VARCHAR2 (200);
   terminal_var     VARCHAR2 (200);
   host_var         VARCHAR2 (200);
   session_id_var   NUMBER;
   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个字符
   state_sql := SUBSTRB (state_sql, 1, 4000);

   --调用oracle函数得到基本信息

   SELECT SYS_CONTEXT ('USERENV', 'OS_USER'),
          SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
          SYS_CONTEXT ('USERENV', 'TERMINAL'),
          SYS_CONTEXT ('USERENV', 'HOST'),
          SYS_CONTEXT ('USERENV', 'SESSIONID')
     INTO user_var,
          ip_add_var,
          terminal_var,
          host_var,
          session_id_var
     FROM DUAL;

   SELECT ora_login_user INTO login_user_var FROM DUAL;

   IF login_user_var = 'TOPUP'
   THEN
      raise_application_error (-20001, 'Restric DDL! Please contact DBA');
   END IF;

   --将得到的信息插入到审计表中

   INSERT INTO TOPUP.TAB_AUDIT_DDL_RECORD (time,
                                           session_id,
                                           os_user,
                                           ip_address,
                                           terminal,
                                           HOST,
                                           user_name,
                                           ddl_type,
                                           object_type,
                                           owner,
                                           object_name,
                                           sql_text)
        VALUES (SYSDATE,
                session_id_var,
                user_var,
                ip_add_var,
                terminal_var,
                host_var,
                login_user_var,
                ora_sysevent,
                ora_dict_obj_type,
                ora_dict_obj_owner,
                ora_dict_obj_name,
                state_sql);
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-714546/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-714546/