Oracle Audit 学习与测试

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 the database audit trail, or an operating 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 CODE resides in dictionary table AUDIT_ACTIONS.

PRIVILEGES USED resides in dictionary table SYSTEM_PRIVILEGE_MAP.

COMPLETION CODE describes 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 SESSION inserts only one audit record in 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 audit BY ACCESS inserts 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-625288/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-625288/