数据库审计.doc

1、数据库审计方案(包括 login,logout,DML,DDL 等)/*=|文件名：数据库审计事件记录脚本|说明：为了对数据库事件进行审计，而进行相关设计=*/* = 审计记录表 = */* - 系统会话事件审计表 - */CREATE TABLE SYSTEM.Audit_Login_DB(/*|名称：审计数据库会话登陆事件记录表|说明：*/Session_Id NUMBER, /* 会话 ID */OS_User VARCHAR2(200), /* 终端 OS 用户 */IP_Address VARCHAR2(200), /* 终端 IP 地址 */Terminal VARCHAR2(20

2、0), /* 终端 */Host VARCHAR2(200), /* 终端主机名 */User_Name VARCHAR2(30), /* ORACLE 用户名*/LogOn_Date DATE, /* 登陆时间 */LogOff_Date DATE, /* 登离时间 */Elapsed_Minutes NUMBER /* 在线时间 */)TABLESPACE TOOLS;/* - 会话事件审计表索引 - */CREATE INDEX IX_AUDIT_LOGIN_SESSIONID ON SYSTEM.AUDIT_LOGIN_DB(SESSION_ID) TABLESPACE INDX;CR

3、EATE INDEX IX_AUDIT_LOGIN_LOGONDATE ON SYSTEM.AUDIT_LOGIN_DB(LOGON_DATE)TABLESPACE INDX;/* - DDL 事件审计表 - */CREATE TABLE system.Audit_DDL_OBJ(/*|名称：审计针对数据库对象的 DDL 记录表|说明：*/Opr_Time DATE, /* 操作时间 */Session_Id NUMBER, /* 会话 ID */OS_User VARCHAR2(200), /* 终端 OS 用户 */IP_Address VARCHAR2(200), /* 终端 IP 地址

4、 */Terminal VARCHAR2(200), /* 终端 */Host VARCHAR2(200), /* 终端主机名 */User_Name VARCHAR2(30), /* ORACLE 用户名*/DDL_Type VARCHAR2(30), /* DDL 操作类型 */DDL_Sql VARCHAR2(2000), /* DDL 语句 */Object_Type VARCHAR2(18), /* 操作对象类型 */Owner VARCHAR2(30), /* 对象拥有者 */Object_Name VARCHAR2(128) /* 对象名称 */);/* - DDL 事件审计表索

5、引 - */CREATE INDEX IX_AUDIT_DDL_SESSIONID ON SYSTEM.AUDIT_DDL_OBJ(SESSION_ID)TABLESPACE INDX;CREATE INDEX IX_AUDIT_DDL_OPRTIME ON SYSTEM.AUDIT_DDL_OBJ(OPR_TIME)TABLESPACE INDX;/* = 审计触发器 = */* - AFTER LOGON ON DATABASE 触发器 -*/CREATE OR REPLACE TRIGGER SYS.Login_Audit_TriggerAFTER LOGON ON DATABASE/*

6、|名称：会话登陆事件审计触发器|说明：*/DECLARESession_Id_Var NUMBER; /* 会话 ID */Os_User_Var VARCHAR2(200); /* 终端 OS 用户 */IP_Address_Var VARCHAR2(200); /* 终端 IP */Terminal_Var VARCHAR2(200); /* 终端 */Host_Var VARCHAR2(200); /* 终端主机名 */BEGIN/* 获取登陆用户信息 */SELECT SYS_CONTEXT(USERENV,SESSIONID),SYS_CONTEXT(USERENV,OS_USER)

7、,SYS_CONTEXT(USERENV,IP_ADDRESS),SYS_CONTEXT(USERENV,TERMINAL),SYS_CONTEXT(USERENV,HOST)INTO Session_Id_Var,Os_User_Var,IP_Address_Var,Terminal_Var,Host_VarFROM DUAL;/* 记录登陆审计信息 */INSERT INTO system.Audit_Login_DB(Session_Id, /* 会话 ID */OS_User, /* 终端 OS 用户 */IP_Address, /* 终端 IP 地址 */Terminal, /* 终

8、端 */Host, /* 终端主机名 */User_Name, /* ORACLE 用户名*/LogOn_Date, /* 登陆时间 */LogOff_Date, /* 登离时间 */Elapsed_Minutes /* 在线时间 */)VALUES( Session_Id_Var,Os_User_Var,IP_Address_Var,Terminal_Var,Host_Var,USER,SYSDATE,NULL,NULL);COMMIT;EXCEPTIONWHEN OTHERS THENNULL; END Login_Audit_Trigger;/* - BEFORE LOGOFF ON D

9、ATABASE 触发器 -*/CREATE OR REPLACE TRIGGER SYS.LogOff_Audit_TriggerBEFORE LOGOFF ON DATABASE/*|名称：会话登离事件审计触发器|说明：*/DECLARESession_Id_Var NUMBER; /* 会话 ID */BEGIN/* 获取登陆用户信息 */SELECT SYS_CONTEXT(USERENV,SESSIONID)INTO Session_Id_VarFROM DUAL;/* 更新会话审计记录信息 */UPDATE system.Audit_Login_DBSET LogOff_Date =

10、 SYSDATE,Elapsed_Minutes = ROUND(SYSDATE - LogOn_Date)* 1440)WHERE Session_Id = Session_Id_Var;-WHERE SYS_CONTEXT(USERENV,SESSIONID) = Session_Id;COMMIT;EXCEPTIONWHEN OTHERS THENNULL;END LogOff_Audit_Trigger;/* - AFTER DDL ON DATABASE 触发器 -*/CREATE OR REPLACE TRIGGER SYS.DDL_Audit_TriggerAFTER DDL O

11、N DATABASE/*|名称：DDL 事件审计触发器|说明：*/DECLARESession_Id_Var NUMBER; /* 会话 ID */Os_User_Var VARCHAR2(200); /* 终端 OS 用户 */IP_Address_Var VARCHAR2(200); /* 终端 IP */Terminal_Var VARCHAR2(200); /* 终端 */Host_Var VARCHAR2(200); /* 终端主机名 */Cut NUMBER; /* SQL 列表长度 */Sql_Text ORA_NAME_LIST_T; /* SQL_TEXT 列表 */L_Tr

12、ace NUMBER; /* 循环执行条件 */DDL_Sql_Var VARCHAR2(2000); /* DDL 语句 */BEGIN/* 获取操作用户信息 */SELECT SYS_CONTEXT(USERENV,SESSIONID),SYS_CONTEXT(USERENV,OS_USER),SYS_CONTEXT(USERENV,IP_ADDRESS),SYS_CONTEXT(USERENV,TERMINAL),SYS_CONTEXT(USERENV,HOST)INTO Session_Id_Var,Os_User_Var,IP_Address_Var,Terminal_Var,Hos

13、t_VarFROM DUAL;/* 获取 DDL SQL 语句 */BEGINSELECT COUNT(*) INTO L_Trace FROM DUAL WHERE ORA_DICT_OBJ_NAME NOT LIKE MLOG% AND ORA_DICT_OBJ_NAME NOT LIKE %LOGAND UTL_INADDR.GET_HOST_ADDRESS IS NOT NULLAND SYS_CONTEXT(USERENV,IP_ADDRESS) IS NOT NULLAND SYS_CONTEXT(USERENV,IP_ADDRESS) 0 THENCut := ORA_SQL_T

14、XT(Sql_Text);FOR i IN 1Cut LOOPDDL_Sql_Var := SUBSTR(DDL_Sql_Var | Sql_Text(i),1,2000);END LOOP;END IF;EXCEPTIONWHEN OTHERS THENNULL;END;/* 记录登陆审计信息 */INSERT INTO system.Audit_DDL_OBJ(Opr_Time, /* 操作时间 */Session_Id, /* 会话 ID */OS_User, /* 终端 OS 用户 */IP_Address, /* 终端 IP 地址 */Terminal, /* 终端 */Host,

15、/* 终端主机名 */User_Name, /* ORACLE 用户名*/DDL_Type, /* DDL 操作类型 */DDL_Sql, /* DDL 语句 */Object_Type, /* 操作对象类型 */Owner, /* 对象拥有者 */Object_Name /* 对象名称 */)VALUES( SYSDATE,Session_Id_Var,Os_User_Var,IP_Address_Var,Terminal_Var,Host_Var,ORA_LOGIN_USER,ORA_SYSEVENT,DDL_Sql_Var,ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_O

16、WNER,ORA_DICT_OBJ_NAME);COMMIT;EXCEPTIONWHEN OTHERS THENNULL;END DDL_Audit_Trigger;/* = 审计 DML 语句 = */*|名称：利用 DBMS_FGA 包的细粒度审计功能实现对 DML 语句的审计|说明：*/CREATE SEQUENCE SEQ_SELECT_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;CREATE SEQUENCE SEQ_INSERT_NUMBER MINVALUE 1 MAXVALUE 100000 STA

17、RT WITH 1 INCREMENT BY 1;CREATE SEQUENCE SEQ_UPDATE_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;CREATE SEQUENCE SEQ_DELETE_NUMBER MINVALUE 1 MAXVALUE 100000 START WITH 1 INCREMENT BY 1;SELECT DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA = |OWNER|, OBJECT_NAME = |TABLE_NAME|,STATEMENT_TYPES =

18、SELECT,POLICY_NAME = CHK_SELECT_|SEQ_SELECT_NUMBER.NEXTVAL|); FROM ALL_TABLES WHERE WNER=INXITEUNION SELECT DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA = |OWNER|, OBJECT_NAME = |TABLE_NAME|,STATEMENT_TYPES = INSERT,POLICY_NAME = CHK_INSERT_|SEQ_INSERT_NUMBER.NEXTVAL|); FROM ALL_TABLES WHERE WNER=INXITEUNION S

19、ELECT DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA = |OWNER|, OBJECT_NAME = |TABLE_NAME|,STATEMENT_TYPES = UPDATE,POLICY_NAME = CHK_UPDATE_|SEQ_UPDATE_NUMBER.NEXTVAL|); FROM ALL_TABLES WHERE WNER=INXITEUNION SELECT DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA = |OWNER|, OBJECT_NAME = |TABLE_NAME|,STATEMENT_TYPES = DELETE

20、,POLICY_NAME = CHK_DELETE_|SEQ_DELETE_NUMBER.NEXTVAL|); FROM ALL_TABLES WHERE WNER=INXITE-需要在以上生成的语句前加上 BEGIN，END；语句，以补充完全获取登录信息:1、 USERENV(OPTION)返回当前的会话信息.OPTION=ISDBA若当前是 DBA 角色,则为 TRUE,否则 FALSE.OPTION=LANGUAGE返回数据库的字符集.OPTION=SESSIONID为当前会话标识符.OPTION=ENTRYID返回可审计的会话标识符 .OPTION=LANG返回会话语言名称的 ISO

21、简记.OPTION=INSTANCE返回当前的实例.OPTION=terminal返回当前计算机名SELECT USERENV(LANGUAGE) FROM DUAL;2、sys_contextselect SYS_CONTEXT(USERENV,TERMINAL) terminal, SYS_CONTEXT(USERENV,LANGUAGE) language, SYS_CONTEXT(USERENV,SESSIONID) sessionid, SYS_CONTEXT(USERENV,INSTANCE) instance, SYS_CONTEXT(USERENV,ENTRYID) entry

22、id, SYS_CONTEXT(USERENV,ISDBA) isdba, SYS_CONTEXT(USERENV,NLS_TERRITORY) nls_territory, SYS_CONTEXT(USERENV,NLS_CURRENCY) nls_currency, SYS_CONTEXT(USERENV,NLS_CALENDAR) nls_calendar, SYS_CONTEXT(USERENV,NLS_DATE_FORMAT) nls_date_format, SYS_CONTEXT(USERENV,NLS_DATE_LANGUAGE) nls_date_language, SYS_

23、CONTEXT(USERENV,NLS_SORT) nls_sort, SYS_CONTEXT(USERENV,CURRENT_USER) current_user, SYS_CONTEXT(USERENV,CURRENT_USERID) current_userid, SYS_CONTEXT(USERENV,SESSION_USER) session_user, SYS_CONTEXT(USERENV,SESSION_USERID) session_userid, SYS_CONTEXT(USERENV,PROXY_USER) proxy_user, SYS_CONTEXT(USERENV,

24、PROXY_USERID) proxy_userid, SYS_CONTEXT(USERENV,DB_DOMAIN) db_domain, SYS_CONTEXT(USERENV,DB_NAME) db_name, SYS_CONTEXT(USERENV,HOST) host, SYS_CONTEXT(USERENV,OS_USER) os_user, SYS_CONTEXT(USERENV,EXTERNAL_NAME) external_name, SYS_CONTEXT(USERENV,IP_ADDRESS) ip_address, SYS_CONTEXT(USERENV,NETWORK_PROTOCOL) network_protocol, SYS_CONTEXT(USERENV,BG_JOB_ID) bg_job_id, SYS_CONTEXT(USERENV,FG_JOB_ID) fg_job_id, SYS_CONTEXT(USERENV,AUTHENTICATION_TYPE) authentication_type, SYS_CONTEXT(USERENV,AUTHENTICATION_DATA) authentication_data from dual