Thứ Năm, 30 tháng 8, 2018

Cấu hình Audit DDL Log Oracle Database

Mục đích: Lưu log các thao tác DDL (drop table, create table, drop procedure, drop package,...) để truy vết các thao tác của DBA hay quản trị ứng dụng (có dùng user DB) làm sai, nhầm.
1. Tạo bản lưu log

CREATE TABLE SYS.DDL_LOG
(
  USER_NAME    VARCHAR2(30 BYTE),
  IP_ADDRESS   VARCHAR2(30 BYTE),
  HOSTNAME     VARCHAR2(30 BYTE),
  DDL_DATE     DATE,
  DDL_TYPE     VARCHAR2(30 BYTE),
  OBJECT_TYPE  VARCHAR2(18 BYTE),
  OWNER        VARCHAR2(30 BYTE),
  OBJECT_NAME  VARCHAR2(128 BYTE),
  SQL_TEXT     VARCHAR2(2000 BYTE)
)
TABLESPACE DATA_LOG
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

CREATE INDEX SYS.DDL_LOG_I1 ON SYS.DDL_LOG
(DDL_DATE)
LOGGING
TABLESPACE INDX_LOG
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );
2. Tạo trigger bắt log DDL

CREATE OR REPLACE TRIGGER SYS.ddl_trig
    AFTER DDL
    ON DATABASE
--ON binhtv.tab1 -- Chỉ giám sát 1 bảng
DECLARE
    ip_addr      VARCHAR2 (30);
    hostname     VARCHAR2 (30);
    l_count      NUMBER;
    l_sql_text   ora_name_list_t;
    l_sql        VARCHAR2 (2000);
BEGIN
    l_count := ora_sql_txt (l_sql_text);

    IF l_count > 1
    THEN
        l_sql := l_sql_text (1) || l_sql_text (2);
    ELSE
        l_sql := l_sql_text (1);
    END IF;

    SELECT   SYS_CONTEXT ('USERENV', 'IP_ADDRESS') INTO ip_addr FROM DUAL;

    SELECT   SYS_CONTEXT ('USERENV', 'HOST') INTO hostname FROM DUAL;

    IF (ip_addr NOT LIKE '192.168.1.%')
    THEN
        INSERT INTO ddl_log (user_name,ip_address,hostname,ddl_date,ddl_type,object_type,
                             owner,object_name,sql_text)
          VALUES   (ora_login_user,ip_addr,hostname,SYSDATE,ora_sysevent,ora_dict_obj_type,
                    ora_dict_obj_owner,ora_dict_obj_name,l_sql);
    END IF;

END ddl_trig;
/

3. Test
CREATE TABLE binhtv.tab1 (col1 DATE)'

DROP TABLE binhtv.tab1;

4. Kiểm tra log

SELECT * FROM sys.dll_audit_log;

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master