Standard Database Auditing

最新推荐文章于 2021-03-17 06:22:17 发布

ciyuqin9435

最新推荐文章于 2021-03-17 06:22:17 发布

阅读量178

点赞数

转自：http://hi.baidu.com/dbaeyes/blog/item/ad1da21b9f2616d6ad6e75bd.html

Enabled through the AUDIT_TRAIL parameter

* NONE: Disables collection of audit records
* DB: Enables auditing with records stored in the database
* OS: Enables auditing with records stored in the operating system audit trail

Can audit:

* Login events
* Exercise of system privileges
* Exercise of object privileges
* Use of SQL statements

Standard Database Auditing

To use database auditing you must first set the non-dynamic parameter AUDIT_TRAIL to point to a storage location for audit records. The normal setting for this parameter is DB, which causes audit records to be stored in the DBA_AUDIT_TRAIL table.

Database auditing can capture information about login events, the exercise of system privileges, and the exercise of object privileges. Audit information can be focused by the user generating the audit event or by the status of the event (successful or not). The following audit command that generates information is probably not required because it is not well focused. This option captures any operation that affects any table:

SQL> AUDIT TABLE;
Audit succeeded.

A better example of an audit command (because it is more narrowly focused) is:

SQL> AUDIT DELETE ON hr.employees WHENEVER SUCCESSFUL;
Audit succeeded.

Specifying Audited Options

* System privilege auditing (nonfocused and focused)
AUDIT select any table, create any trigger;
AUDIT select any table BY hr BY SESSION;

* Object privilege auditing (nonfocused and focused)
AUDIT ALL on hr.employees;
AUDIT UPDATE,DELETE on hr.employees BY ACCESS;

* Session auditing
AUDIT session whenever not successful;

SQL statement auditing: The statement shown in the slide will audit any DDL statement that affects a table including CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and so on. SQL Statement auditing can be focused by username or by success/failure.

SQL> AUDIT TABLE BY hr WHENEVER NOT SUCCESSFUL;

System privilege auditing can be used to audit the exercise of any system privilege (such as DROP ANY TABLE). It can be focused by username or success/failure. By default, each time an audited system privilege is exercised an audit record is generated. You can choose to group those records so that only one record is generated per session (that way if a user updates 100,000 records in a table belonging to another user, you only gather one audit record). If the BY SESSION clause is not specified, the default is BY ACCESS. Consider using the BY SESSION clause to limit the performance and storage impact of system privilege auditing.

Object privilege auditing can be used to audit actions on tables, views, procedures, sequences, directories and user-defined data types. This type of auditing can be focused by success/failure and grouped by session or access. Unlike system privilege auditing, the default grouping is by session so you must implicitly specify BY ACCESS if you want a separate audit trail record generated for each action. （因为测试的时候没有加 by access, 使得select后的insert没有被audit，在这里迷惑了很久，重新仔细的看文档，才发现这段，E文还是不行啊）

The AUDIT SESSION option audits the creation of user sessions. It can be focused by username or by success/failure. This option is unique because it generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session such as connection time, disconnection time, logical and physical I/Os processed, and more is stored in a single audit record that corresponds to the session. In many databases it is common to use the AUDIT SESSION (nonfocused) command. In almost all databases you should AUDIT SESSION WHENEVER NOT SUCCESSFUL because this allows you to detect attempts to break into your database

Note: Often your audit options start as nonfocused because you are not sure what type of activity you are looking for. The AUDIT ALL option is a convenient shortcut to audit a broad range of activity.

Viewing Auditing Options

To see which audit options have been selected, use the views listed above.

DBA_STMT_AUDIT_OPTSand DBA_PRIV_AUDIT_OPTScontain only records of statement or privilege audit options that have been specified. DBA_OBJ_AUDIT_OPTS contains one record per auditable object regardless of what object audit options have been specified. The view shows a column for each auditable option. For example, INSERTaudit options are shown in the INScolumn. Audit options are displayed as SUCCESSFUL/NOT SUCCESSFULwith three possible values for each status:

? - Not audited
? S Collect audit records by session
? A Collect audit records by access

SQL> SELECT object_name, object_type, ins, upd
FROM dba_obj_audit_opts WHERE object_name = 'EMPLOYEES'
OBJECT_NAME OBJECT_TY INS UPD

------------ --------- --- ---
EMPLOYEES TABLE A/S -/-

简言之：查询
select * from dba_obj_audit_opts --object的audit

Viewing Auditing Results

Access to audit records should be tightly controlled because they may contain sensitive information. Usually the task of managing the audit trail is handled by the administrator but if it needs to be delegated the DELETE_CATALOG_ROLE grants permission to delete from the audit trail.

Value-Based Auditing

Database auditing records that inserts, updates, and deletes have occurred on audited objects, but does not capture the actual values that were changed. Value-based auditing extends database auditing, capturing the actual values that have been changed. Value-based auditing leverages database triggers (event-driven PL/SQL constructs).

网转中文：

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
1、审计初始化参数：在init文件中添加 AUDIT_TRAIL = DB

2、设置审计
对象审计：AUDIT DELETE,INSERT,UPDATE ON TABLE (BY USER);
SESSION审计：
AUDIT SESSION;
-- 指定用户
AUDIT SESSION BY (USER1,USER2)
权限审计：
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOTSUCCESSFUL;
AUDIT DELETE ANY TABLE;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS WHENEVER NOT SUCCESSFUL;

3、关闭审计NOAUDIT

4、清除审计
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name = 。。。

5、相关视图查询,从中可以查询到相关的数据
STMT_AUDIT_OPTION_MAP-审计选项类型代码
AUDIT_ACTIONS-action代码
ALL_DEF_AUDIT_OPTS-对象创建时默认的对象审计选项
DBA_STMT_AUDIT_OPTS-当前数据库系统审计选项
DBA_PRIV_AUDIT_OPTS-权限审计选项
DBA_OBJ_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS-对象审计选项
DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL-审计记录
DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT-审计对象列表
DBA_AUDIT_SESSION
USER_AUDIT_SESSION-session审计
DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT-语句审计
DBA_AUDIT_EXISTS-使用BY AUDIT NOT EXISTS选项的审计
DBA_AUDIT_POLICIES-审计POLICIES
DBA_COMMON_AUDIT_TRAIL-标准审计+精细审计

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

审计是对选定的用户动作的监控和记录，通常用于：

u 审查可疑的活动。例如：数据被非授权用户所删除，此时安全管理员可决定对该数据库的所有连接进行审计，以及对数据库的所有表的成功地或不成功地删除进行审计。

u 监视和收集关于指定数据库活动的数据。例如：DBA可收集哪些被修改、执行了多少次逻辑的I/O等统计数据。

ORACLE支持三种审计类型：

u 语句审计，对某种类型的SQL语句审计，不指定结构或对象。

u 特权审计，对执行相应动作的系统特权的使用审计。

u 对象审计，对一特殊模式对象上的指定语句的审计。

ORACLE所允许的审计选择限于下列方面：

u 审计语句的成功执行、不成功执行，或者其两者。

u 对每一用户会话审计语句执行一次或者对语句每次执行审计一次。

u 对全部用户或指定用户的活动的审计。

当数据库的审计是使能的，在语句执行阶段产生审计记录。审计记录包含有审计的操作、用户执行的操作、操作的日期和时间等信息。审计记录可存在数据字典表（称为审计记录）或操作系统审计记录中。数据库审计记录是在SYS模式的AUD$表中。

设置ORACLE审计

下列步骤可以设置ORACLE的审计功能：

1. 修改参数文件（init.ora，如果使用服务器参数文件使用alter system set = scope=spfile|both，详情参照1.1节中关于参数文件的介绍），设置 AUDIT_TRAIL参数，并且重启数据库。AUDIT_TRAIL的取值如下：

l DB/TRUE：启动审计功能，并且把审计结果存放在数据库的 SYS.AUD$ 表中

l OS：启动审计功能，并把审计结果存放在操作系统的审计信息中

l DB_EXTENDED：具有DB/TRUE的功能，另外填写AUD$的SQLBIND和SQLTEXT字段

l NONE/FALSE：关闭审计功能

2.如果设置 AUDIT_TRAIL = OS, 还需要修改参数AUDIT_FILE_DEST。

如果操作系统支持设置AUDIT_TRAIL=OS，文件会自动存放在AUDIT_FILE

_DEST所指定的目录下，并且文件名包含进程的PID。

比如：

AUDIT_FILE_DEST = $ORACLE_HOME/rdbms/audit

$ ls -l $ORACLE_HOME/rdbms/audit

-rw-rw---- 1 ora92 dba 881 Mar 17 09:57 ora_13264.aud

$ ps -ef|grep 13264