/ 中存储网

关于oracle 11g数据库的审计文件

2017-07-06 20:28:52 来源:中存储

关于Oracle审计:

11g默认启用的审计选项,AUDIT_TRAIL参数的缺省值为DB,表示审计数据将记录在数据库中的SYS.AUD$审计字典基表上。而在Oracle 10g中该参数默认值为none,即不启用审计。Oracle官方宣称默认启用的审计日志不会对绝大多数产品数据库的性能带来过大的负面影响,同时Oracle公司还推荐使用基于OS文件的审计日志记录方式(OS audit trail files)。

因为在11g中CREATE SESSION将被作为受审计的权限来被记录,因此当SYSTEM表空间因磁盘空间而无法扩展时将导致这部分审计记录无法生成,最终导致普通用户的新会话将无法正常创建,普通用户将无法登陆数据库。在这种场景中仍可以使用SYSDBA身份的用户创建会话,在将审计数据合适备份后删除一部分记录,或者TRUNCATEAUD$都可以解决上述问题。

当AUDIT_TRAIL设置为OS时,审计记录文件将在AUDIT_FILE_DEST参数所指定的目录中生成。全部这些文件均可以随时被删除或复制。

以下权限将对所有用户审计:

SQL> select privilege,success,failure fromdba_priv_audit_opts;

PRIVILEGE SUCCESS FAILURE

---------------------------------------- --------------------

CREATE EXTERNAL JOB BY ACCESS BY ACCESS

CREATE ANY JOB BY ACCESS BY ACCESS

GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS

EXEMPT ACCESS POLICY BY ACCESS BY ACCESS

CREATE ANY LIBRARY BY ACCESS BY ACCESS

GRANT ANY PRIVILEGE BY ACCESS BY ACCESS

DROP PROFILE BY ACCESS BY ACCESS

ALTER PROFILE BY ACCESS BY ACCESS

DROP ANY PROCEDURE BY ACCESS BY ACCESS

ALTER ANY PROCEDURE BY ACCESS BY ACCESS

CREATE ANY PROCEDURE BY ACCESS BY ACCESS

ALTER DATABASE BY ACCESS BY ACCESS

GRANT ANY ROLE BY ACCESS BY ACCESS

CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS

DROP ANY TABLE BY ACCESS BY ACCESS

ALTER ANY TABLE BY ACCESS BY ACCESS

CREATE ANY TABLE BY ACCESS BY ACCESS

DROP USER BY ACCESS BY ACCESS

ALTER USER BY ACCESS BY ACCESS

CREATE USER BY ACCESS BY ACCESS

CREATE SESSION BY ACCESS BY ACCESS

AUDIT SYSTEM BY ACCESS BY ACCESS

ALTER SYSTEM BY ACCESS BY ACCESS

23 rows selected.

当前数据库中的现有的审计记录,LOGNO/LOGOFF为审计create session产生的:

SQL> select action_name,count(*) from dba_audit_trailgroup by action_name;

ACTION_NAME COUNT(*)

---------------------------- ----------

LOGOFF BY CLEANUP 40

LOGON 460

LOGOFF 377

ALTER USER 2

SYSTEM GRANT 12

ALTER SYSTEM 10

CREATE PUBLIC SYNONYM 5

ALTER DATABASE 2

CREATE DATABASE LINK 1

DROP PUBLIC SYNONYM 5

96.216 SYSTEM表空间过大:

96.216中 LOGOFF/LOGOFF分别为2亿多条记录,使用得aud$表大小为80G。

解决方法:

1.当aud$表过大时,可以清除表中的审讯数据:

SQL> truncate table sys.aud$;

2.可以关闭对create session的审讯:

SQL> noaudit create session;

3.关闭数据库的审讯,需要重启数据库:

SQL> alter system set audit_trail=none scope=spfile;