Oracle Audit学习与测试 参考文档 >P643 >P781 目录 1 Overview of Audit 2 Audit Records and the Audit Trail 3 Mechanisms for Auditing 4 Focus Auditing 5 Managing the Audit Trail 6 Viewing Database Audit Trail Information 7 test 8 Notes 1 Overview of Audit 1.1 Auditing is the monitoring and recording of selectedd user database actions. 1.2 Oracle supports three general types of auditing: Statement auditing: AUDIT DDL statement and DML statement. .(for example, AUDIT TABLE audits all CREATE and DROP TABLE statements不针对具体的Schema Object). Privilege auditing: AUDIT CREATE TABLE Schema object auditing: AUDIT SELECT ON employee Fine-grained auditing: allows the monitoring of data access based on content. 2 Audit Records and the Audit Trail Audit records include information such as the operation that was audited, and the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called thedatabase audit trail,or anoperating system audit trail. The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle database’s data dictionary.Several predefined views are provided to help to view information. The operating system audit trail is encoded and not readable, but it is decoded in data dictionary files and error messages. ACTION CODEresides in dictionary table AUDIT_ACTIONS. PRIVILEGES USEDresides in dictionary table SYSTEM_PRIVILEGE_MAP. COMPLETION CODEdescribes the result of an attempted operation. 3 Mechanisms for Auditing 3.1 when are audit records generated The recording of audit information can be enabled or disabled. An audit record is generated during the execute phase of statement execution. SQL statements inside PL/SQL program units are individually audited, when the program unit is executed.The generation and insertion of an audit trail record is independent of a user’s transaction. Therefore, even if a user’s transaction is rolled back, the audit trail record remains committed.(审计记录的时机) 3.2 Events always audited to the Operating System Audit Trail Regardless of whether database auditing is enabled, Oracle always records some database-related actions into the operating system audit trail: (AIX, $ORACLE_HOME/rdbms/audit/) 1) At instance startup. 2) At instance shutdown 3) During connections with administrator privileges. 4 Focus Auditing 4.1 Successful and Unsuccessful statement execution auditing Using either form. of the AUDIT statement, you can include: The WHENEVER SUCCESSFUL clause, to audit only successful executions of the audited statement The WHENEVER NOT SUCCESSFUL clause, to audit only unsuccessful executions of the audited statement Neither of the previous clauses, to audit both successful and unsuccessful executions of the audited statement 4.2 By Session and By Access 1) BY SESSIONinsertsonly one audit recordin the audit trail, for each user and schema object, during the session that includes an audited action.(只插入一条审计记录,default) A session is the time between when a user connects to and disconnects from an Oracle database. 2) Setting auditBY ACCESSinserts one audit record into the audit trail for each execution of an auditable operation within a cursor.(一次执行插入一条审计记录) 4.3 Audit by user By focusing on specific users, you can minimize the number of audit records generated. AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake; 5 Managing the Audit Trail 5.1 Enable and Disable Auditing 1) Setting the AUDIT_TRAIL Initialization Parameter,the parameter can be set to the following values: DB: Enable database auditing and directs all audit records to the database audit trial,except for records that are always written to the operating system audit trail. OS: Enable database auditing and directs all audit records to an operating system file. None: Disable auditing(Default) idle> show parameter AUDIT_TRAIL NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string NONE 2) setting the AUDIT_FILE_DEST Initialization Parameter, the parameter specifies an operating system directory into which the audit trail is written when AUDIT_TRAIL=OS is specified.it is also the location to which mandatory auditing information is written.(默认OS路径) idle> show parameter AUDIT_FILE_DEST NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string ?/rdbms/audit 3) Audit_sys_operations： idle> show parameter Audit_sys_operations NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_sys_operations boolean FALSE 默认为false，当设置为true时，所有sys用户（包括以sysdba,sysoper身份登录的用户）的操作都会被记录，audit trail不会写在aud$表中，这个很好理解，如果数据库还未启动aud$不可用，那么像conn /as sysdba这样的连接信息，只能记录在其它地方。如果是windows平台，audti trail会记录在windows的事件管理中，如果是linux/unix平台则会记录在audit_file_dest参数指定的文件中。 5.2 Setting Audit Level Specify auditing options using the AUDIT statement.The AUDIT statement allows you to set audit options at three level:Statement, Privilege, Object. 5.3 Turn off audit options 将对应审计语句的audit改为noaudit即可，如audit session whenever successful对应的取消审计语句为noaudit session whenever successful; To disable statement or privilege auditing options, you must have the AUDIT SYSTEM system privilege 5.4 Controlling the Growth and Size of the Audit Trail DELETE FROM SYS.AUD$; DELETE FROM SYS.AUD$ WHERE obj$name='EMP'; 6 Viewing Database Audit Trail Information 6.1 Creating the audit trail views views (except STMT_AUDIT_OPTION_MAP) are created by the CATALOG.SQL and CATAUDIT.SQL scripts: 6.2 Deleting the audit trail views running the script. file CATNOAUD.SQL. 6.3和审计相关的视图 dba_audit_trail：保存所有的audit trail，实际上它只是一个基于aud$的视图。其它的视图dba_audit_session,dba_audit_object,dba_audit_statement都只是dba_audit_trail的一个子集。 dba_stmt_audit_opts：可以用来查看statement审计级别的audit options，即数据库设置过哪些statement级别的审计。dba_obj_audit_opts,dba_priv_audit_opts视图功能与之类似 all_def_audit_opts：用来查看数据库用on default子句设置了哪些默认对象审计。 7 test 7.1、激活审计 SQL> conn /as sysdba SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string ?/rdbms/audit audit_sys_operations boolean FALSE audit_trail string NONE transaction_auditing boolean TRUE SQL> alter system set audit_sys_operations=TRUE scope=spfile; --审计管理用户(以sysdba/sysoper角色登陆) SQL> alter system set audit_trail=db scope=spfile; SQL> shutdown immediate SQL> startup idle> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string ?/rdbms/audit audit_sys_operations boolean TRUE audit_trail string DB transaction_auditing boolean TRUE 7.2、开始审计 col DEST_NAME format a30 col OS_USERNAME format a15 col USERNAME format a15 col USERHOST format a15 col TERMINAL format a15 col OBJ_NAME format a30 select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid from dba_audit_trail; OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP --------------- --------------- --------------- --------------- --------- OWNER OBJ_NAME ACTION_NAME SESSIONID ------------------------------ ------------------------------ --------------------------- ---------- oracle TEST 17-JAN-10 TEST DEPART SESSION REC 299 audit select table by test by access; 如果在命令后面添加by user则只对user的操作进行审计,如果省去by用户,则对系统中所有的用户进行审计(不包含sys用户). 例： AUDIT DELETE ANY TABLE; --审计删除表的操作 AUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL; --只审计删除失败的情况 AUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL; --只审计删除成功的情况 AUDIT DELETE,UPDATE,INSERT ON depart by test; --审计test用户对表user.table的delete,update,insert操作 7.3、撤销审计 SQL> noaudit all on DEPART; 7.4 Fine-grained auditing 参考dbms_fga　Package． 8 Notes 1 Auditing a schema object invalidates that schema object in the cache and causes it to be reloaded =================================================================================[@more@]

http://hi.baidu.com/edeed/blog/item/dc7c0cd75f667adea144dfea.html

http://blog.csdn.net/ryb7899/archive/2010/03/24/5413720.aspx

http://bbs.chinaunix.net/viewthread.php?tid=1309829