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;
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;
(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;
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);
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;
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;
/
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/