oracle 标准审计
1 开启和关闭审计(Enabling and Disabling Standard Auditing)
设置AUDIT_TRAIL参数，参数选型如下:
DB 启动审计功能，并且把审计结果存放在数据库的 SYS.AUD$ 表中
XML 启动审计功能，并把审计结果存放在操作系统的xml文件里
DB,EXTENDED 具有DB/TRUE的功能，另外填写AUD$的SQLBIND和SQLT EXT字段
XML,EXTENDED 具有XML/TRUE的功能,另外SQLBIND和SQLT EXT字段
OS 启动审计功能，并把审计结果存放在操作系统的审计信息中
NONE 关闭审计功能
ORACLE 10G 默认选项是NONE
ORACLE 11G 默认选项是OS
设置AUDIT_FILE_DEST参数
如果设置了AUDIT_TRAIL=OS or AUDIT_TRAIL=XML or AUDIT_TRAIL=XML,EXTENDED 需要该参数AUDIT_FILE_DEST
设置AUDIT_SYSLOG_LEVEL参数
可以参考maclean 的一片文章
2 审计分类
|
Level |
Effect |
|
Statement |
Causes auditing of specific SQL statements or groups of statements that affect a particular type of database object. For example, AUDIT TABLE audits the CREATE TABLE, TRUNCATE TABLE, COMMENT ON TABLE, and DELETE [FROM] TABLE statements. |
|
Privilege |
Audits SQL statements that are authorized by the specified system privilege. For example, AUDIT CREATE ANY TRIGGER audits statements issued using the CREATE ANY TRIGGER system privilege. |
|
Object |
Audits specific statements on specific objects, such as ALTER TABLE on the emp table. |
|
Network |
Audits unexpected errors in network protocol or internal errors in the network layer. |
审计选项
BY SESSION/BY ACCESS
BY SESSION 每个会话记录一次
BY ACCESS 每访问一次记录一次
BY USER 指定用户
WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL
成功/失败
(1) 语句审计 Statement
--session
AUDIT SESSION;
AUDIT SESSION BY jeff, lori;
--DDL
AUDIT CREATE TABLE
--DML
AUDIT SELECT TABLE
取消审计
NOAUDIT SESSION;
NOAUDIT SESSION BY jeff, lori;
--DDL
NOAUDIT CREATE TABLE
--DML
NOAUDIT SELECT TABLE
AUDIT ALL;---打开全部语句审计
NOAUDIT ALL;--关闭全部审计
查询设置选项视图--DBA_STMT_AUDIT_OPTS
审计记录查询视图--DBA_AUDIT_TRAIL,DBA_AUDIT_SESSION,DBA_AUDIT_STATEMENT
(2) 权限审计 Privilege
审计语句
AUDIT DELETE ANY TABLE
BY ACCESS
WHENEVER NOT SUCCESSFUL;
AUDIT DELETE ANY TABLE;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS
WHENEVER NOT SUCCESSFUL;
取消审计
NOAUDIT DELETE ANY TABLE
BY ACCESS
WHENEVER NOT SUCCESSFUL;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS
WHENEVER NOT SUCCESSFUL;
AUDIT ALL PRIVILEGES;--打开全部权限审计
NOAUDIT ALL PRIVILEGES;--关闭全部权限审计
--查询设置选项视图--DBA_PRIV_AUDIT_OPTS
-- 审计记录查询视图--DBA_AUDIT_TRAIL
注意改审计是使用该权限时触发 而不是首选触发 当与语句审计重复是 语句审计优先
(3) 对象审计
审计语句：
AUDIT DELETE ON jeff.emp;
AUDIT SELECT, INSERT, DELETE
ON jward.dept
BY ACCESS
WHENEVER SUCCESSFUL;
AUDIT SELECT
ON DEFAULT
WHENEVER NOT SUCCESSFUL;
取消审计语句
NOAUDIT DELETE ON jeff.emp;
NOAUDIT SELECT, INSERT, DELETE
ON jward.dept
BY ACCESS
WHENEVER SUCCESSFUL;
NOAUDIT SELECT
ON DEFAULT
WHENEVER NOT SUCCESSFUL;
AUDIT ALL
ON emp;
NOAUDIT ALL
ON emp;
查询设置选项视图--DBA_OBJ_AUDIT_OPTS
审计记录查询视图--DBA_AUDIT_TRAIL,DBA_AUDIT_OBJECT
(4) 网络审计
3 删除审计记录
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$
WHERE obj$name='EMP';
4 相关视图:
|
View |
Description |
|
STMT_AUDIT_OPTION_MAP |
Contains information about auditing option type codes. Created by the SQL.BSQ script. at CREATE DATABASE time. |
|
AUDIT_ACTIONS |
Contains descriptions for audit trail action type codes. |
|
ALL_DEF_AUDIT_OPTS |
Contains default object-auditing options that will be applied when objects are created. |
|
DBA_STMT_AUDIT_OPTS |
Describes current system auditing options across the system and by user. |
|
DBA_PRIV_AUDIT_OPTS |
Describes current system privileges being audited across the system and by user. |
|
DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS |
Describes auditing options on all objects. The USER view describes auditing options on all objects owned by the current user. |
|
DBA_AUDIT_TRAIL USER_AUDIT_TRAIL |
Lists all audit trail entries. The USER view shows audit trail entries relating to current user. |
|
DBA_AUDIT_OBJECT USER_AUDIT_OBJECT |
Contains audit trail records for all objects in the system. The USER view lists audit trail records for statements concerning objects that are accessible to the current user. |
|
DBA_AUDIT_SESSION USER_AUDIT_SESSION |
Lists all audit trail records concerning CONNECT and DISCONNECT. The USER view lists all audit trail records concerning connections and disconnections for the current user. |
|
DBA_AUDIT_STATEMENT USER_AUDIT_STATEMENT |
Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user. |
|
DBA_AUDIT_EXISTS |
Lists audit trail entries produced BY AUDIT NOT EXISTS. |
|
DBA_AUDIT_POLICIES |
Shows all the auditing policies on the system. |
|
DBA_FGA_AUDIT_TRAIL |
Lists audit trail records for value-based auditing. |
|
DBA_COMMON_AUDIT_TRAIL |
Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format. |