创建监控表
—CREAT LOG TABLE
CREATE TABLE SYS.DDL_AUDIT_LOG
( "DDL_DATE" DATE,
"USER_NAME" VARCHAR2(30),
"IP_ADDR" VARCHAR2(30),
"OBJ_NAME" VARCHAR2(50),
"DDL_TYPE" VARCHAR2(30),
"OBJECT_TYPE" VARCHAR2(18),
"OWNER" VARCHAR2(30),
"SQL_TEXT" VARCHAR2(4000 CHAR)
) ;
创建监控触发器
—CREATE TRIGGER
CREATE OR REPLACE TRIGGER SYS.DDL_AUDIT
AFTER CREATE OR ALTER OR DROP OR TRUNCATE OR GRANT OR REVOKE OR RENAME
ON DATABASE
DECLARE
ipaddr VARCHAR2 (20);
STEXT VARCHAR2 (2000);
BEGIN
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') INTO ipaddr FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
ipaddr := '-';
END;
BEGIN
SELECT SQL_TEXT
INTO STEXT
FROM v$open_cursor
WHERE UPPER (sql_text) LIKE 'ALTER%'
OR UPPER (sql_text) LIKE 'DROP%'
OR UPPER (sql_text) LIKE 'TRUNCATE%'
OR UPPER (sql_text) LIKE 'GRANT%'
OR UPPER (sql_text) LIKE 'REVOKE%';
EXCEPTION
WHEN OTHERS
THEN
STEXT := '-';
END;
INSERT INTO SYS.DDL_AUDIT_LOG
VALUES (SYSDATE,
USER,
NVL (ipaddr, '-'),
NVL (ora_dict_obj_name, '-'),
NVL (ORA_SYSEVENT, '-'),
NVL (ora_dict_obj_type, '-'),
NVL (ora_dict_obj_owner, '-'),
STEXT);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
查询监控结果
—SELECT DDL OPERATION
SELECT * FROM SYS.DDL_AUDIT_LOG