11g默认审计选项

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

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

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

注意在默认情况下会以AUTOEXTEND ON自动扩展选项创建SYSTEM表空间,因此系统表空间在必要情况下还是会自动增长的,我们所需注意的是磁盘上的剩余空间是否能够满足其增长需求,以及数据文件扩展的上限,对于普通的8k smallfile表空间而言单个数据文件的最大尺寸是32G。

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


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com


SQL> select privilege,success,failure from dba_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

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
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

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS

23 rows selected.

以下语句也将对所有用户审计:

SQL> select audit_option,success,failure from dba_stmt_audit_opts;

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER SYSTEM                             BY ACCESS  BY ACCESS
SYSTEM AUDIT                             BY ACCESS  BY ACCESS
CREATE SESSION                           BY ACCESS  BY ACCESS
CREATE USER                              BY ACCESS  BY ACCESS
ALTER USER                               BY ACCESS  BY ACCESS
DROP USER                                BY ACCESS  BY ACCESS
PUBLIC SYNONYM                           BY ACCESS  BY ACCESS
DATABASE LINK                            BY ACCESS  BY ACCESS
ROLE                                     BY ACCESS  BY ACCESS
PROFILE                                  BY ACCESS  BY ACCESS
CREATE ANY TABLE                         BY ACCESS  BY ACCESS

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
ALTER ANY TABLE                          BY ACCESS  BY ACCESS
DROP ANY TABLE                           BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
GRANT ANY ROLE                           BY ACCESS  BY ACCESS
SYSTEM GRANT                             BY ACCESS  BY ACCESS
ALTER DATABASE                           BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
ALTER PROFILE                            BY ACCESS  BY ACCESS
DROP PROFILE                             BY ACCESS  BY ACCESS

AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
CREATE ANY JOB                           BY ACCESS  BY ACCESS
CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

28 rows selected.

当前数据库中的现有的审计记录:


SQL> select action_name,count(*) from dba_audit_trail group 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

10 rows selected.

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. SQL> select * from v$version;BANNER CON_ID——————————————————————————– ———-Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta 0PL/SQL Release 12.1.0.0.2 – Beta 0CORE 12.1.0.0.2 Beta 0TNS for Linux: Version 12.1.0.0.2 – Beta 0NLSRTL Version 12.1.0.0.2 – Beta 0SQL> select privilege,success,failure from dba_priv_audit_opts;PRIVILEGE SUCCESS FAILURE—————————————- ———- ———-EXEMPT IDENTITY POLICY BY ACCESS BY ACCESSEXEMPT IDENTITY POLICY BY ACCESS BY ACCESSEXEMPT IDENTITY POLICY BY ACCESS BY ACCESSMANAGE SCHEDULER BY ACCESS BY ACCESSMANAGE SCHEDULER BY ACCESS BY ACCESSMANAGE SCHEDULER BY ACCESS BY ACCESSCREATE JOB BY ACCESS BY ACCESSCREATE JOB BY ACCESS BY ACCESSCREATE JOB BY ACCESS BY ACCESSIMPORT FULL DATABASE BY ACCESS BY ACCESSIMPORT FULL DATABASE BY ACCESS BY ACCESSIMPORT FULL DATABASE BY ACCESS BY ACCESSEXPORT FULL DATABASE BY ACCESS BY ACCESSEXPORT FULL DATABASE BY ACCESS BY ACCESSEXPORT FULL DATABASE BY ACCESS BY ACCESSDEBUG ANY PROCEDURE BY ACCESS BY ACCESSDEBUG ANY PROCEDURE BY ACCESS BY ACCESSDEBUG ANY PROCEDURE BY ACCESS BY ACCESSDEBUG CONNECT SESSION BY ACCESS BY ACCESSDEBUG CONNECT SESSION BY ACCESS BY ACCESSDEBUG CONNECT SESSION BY ACCESS BY ACCESSEXEMPT ACCESS POLICY BY ACCESS BY ACCESSEXEMPT ACCESS POLICY BY ACCESS BY ACCESSEXEMPT ACCESS POLICY BY ACCESS BY ACCESSADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESSADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESSADMINISTER DATABASE TRIGGER BY ACCESS BY ACCESSCREATE ANY INDEXTYPE BY ACCESS BY ACCESSCREATE ANY INDEXTYPE BY ACCESS BY ACCESSCREATE ANY INDEXTYPE BY ACCESS BY ACCESSCREATE INDEXTYPE BY ACCESS BY ACCESSCREATE INDEXTYPE BY ACCESS BY ACCESSCREATE INDEXTYPE BY ACCESS BY ACCESSCREATE ANY OPERATOR BY ACCESS BY ACCESSCREATE ANY OPERATOR BY ACCESS BY ACCESSCREATE ANY OPERATOR BY ACCESS BY ACCESSCREATE OPERATOR BY ACCESS BY ACCESSCREATE OPERATOR BY ACCESS BY ACCESSCREATE OPERATOR BY ACCESS BY ACCESSCREATE ANY LIBRARY BY ACCESS BY ACCESSCREATE ANY LIBRARY BY ACCESS BY ACCESSCREATE ANY LIBRARY BY ACCESS BY ACCESSCREATE LIBRARY BY ACCESS BY ACCESSCREATE LIBRARY BY ACCESS BY ACCESSCREATE LIBRARY BY ACCESS BY ACCESSCREATE ANY TYPE BY ACCESS BY ACCESSCREATE ANY TYPE BY ACCESS BY ACCESSCREATE ANY TYPE BY ACCESS BY ACCESSCREATE TYPE BY ACCESS BY ACCESSCREATE TYPE BY ACCESS BY ACCESSCREATE TYPE BY ACCESS BY ACCESSCREATE ANY MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE ANY MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE ANY MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE MATERIALIZED VIEW BY ACCESS BY ACCESSCREATE MATERIALIZED VIEW BY ACCESS BY ACCESSALTER RESOURCE COST BY ACCESS BY ACCESSALTER RESOURCE COST BY ACCESS BY ACCESSALTER RESOURCE COST BY ACCESS BY ACCESSDROP PROFILE BY ACCESS BY ACCESSDROP PROFILE BY ACCESS BY ACCESSDROP PROFILE BY ACCESS BY ACCESSALTER PROFILE BY ACCESS BY ACCESSALTER PROFILE BY ACCESS BY ACCESSALTER PROFILE BY ACCESS BY ACCESSCREATE PROFILE BY ACCESS BY ACCESSCREATE PROFILE BY ACCESS BY ACCESSCREATE PROFILE BY ACCESS BY ACCESSCREATE ANY TRIGGER BY ACCESS BY ACCESSCREATE ANY TRIGGER BY ACCESS BY ACCESSCREATE ANY TRIGGER BY ACCESS BY ACCESSCREATE TRIGGER BY ACCESS BY ACCESSCREATE TRIGGER BY ACCESS BY ACCESSCREATE TRIGGER BY ACCESS BY ACCESSCREATE ANY PROCEDURE BY ACCESS BY ACCESSCREATE ANY PROCEDURE BY ACCESS BY ACCESSCREATE ANY PROCEDURE BY ACCESS BY ACCESSCREATE PROCEDURE BY ACCESS BY ACCESSCREATE PROCEDURE BY ACCESS BY ACCESSCREATE PROCEDURE BY ACCESS BY ACCESSALTER DATABASE BY ACCESS BY ACCESSALTER DATABASE BY ACCESS BY ACCESSALTER DATABASE BY ACCESS BY ACCESSCREATE ROLE BY ACCESS BY ACCESSCREATE ROLE BY ACCESS BY ACCESSCREATE ROLE BY ACCESS BY ACCESSDROP PUBLIC DATABASE LINK BY ACCESS BY ACCESSDROP PUBLIC DATABASE LINK BY ACCESS BY ACCESSDROP PUBLIC DATABASE LINK BY ACCESS BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESSCREATE DATABASE LINK BY ACCESS BY ACCESSCREATE DATABASE LINK BY ACCESS BY ACCESSCREATE DATABASE LINK BY ACCESS BY ACCESSCREATE ANY SEQUENCE BY ACCESS BY ACCESSCREATE ANY SEQUENCE BY ACCESS BY ACCESSCREATE ANY SEQUENCE BY ACCESS BY ACCESSCREATE SEQUENCE BY ACCESS BY ACCESSCREATE SEQUENCE BY ACCESS BY ACCESSCREATE SEQUENCE BY ACCESS BY ACCESSCREATE ANY VIEW BY ACCESS BY ACCESSCREATE ANY VIEW BY ACCESS BY ACCESSCREATE ANY VIEW BY ACCESS BY ACCESSCREATE VIEW BY ACCESS BY ACCESSCREATE VIEW BY ACCESS BY ACCESSCREATE VIEW BY ACCESS BY ACCESSDROP PUBLIC SYNONYM BY ACCESS BY ACCESSDROP PUBLIC SYNONYM BY ACCESS BY ACCESSDROP PUBLIC SYNONYM BY ACCESS BY ACCESSCREATE PUBLIC SYNONYM BY ACCESS BY ACCESSCREATE PUBLIC SYNONYM BY ACCESS BY ACCESSCREATE PUBLIC SYNONYM BY ACCESS BY ACCESSCREATE ANY SYNONYM BY ACCESS BY ACCESSCREATE ANY SYNONYM BY ACCESS BY ACCESSCREATE ANY SYNONYM BY ACCESS BY ACCESSCREATE SYNONYM BY ACCESS BY ACCESSCREATE SYNONYM BY ACCESS BY ACCESSCREATE SYNONYM BY ACCESS BY ACCESSCREATE ANY CLUSTER BY ACCESS BY ACCESSCREATE ANY CLUSTER BY ACCESS BY ACCESSCREATE ANY CLUSTER BY ACCESS BY ACCESSCREATE CLUSTER BY ACCESS BY ACCESSCREATE CLUSTER BY ACCESS BY ACCESSCREATE CLUSTER BY ACCESS BY ACCESSCREATE ANY TABLE BY ACCESS BY ACCESSCREATE ANY TABLE BY ACCESS BY ACCESSCREATE ANY TABLE BY ACCESS BY ACCESSCREATE TABLE BY ACCESS BY ACCESSCREATE TABLE BY ACCESS BY ACCESSCREATE TABLE BY ACCESS BY ACCESSDROP ROLLBACK SEGMENT BY ACCESS BY ACCESSDROP ROLLBACK SEGMENT BY ACCESS BY ACCESSDROP ROLLBACK SEGMENT BY ACCESS BY ACCESSALTER ROLLBACK SEGMENT BY ACCESS BY ACCESSALTER ROLLBACK SEGMENT BY ACCESS BY ACCESSALTER ROLLBACK SEGMENT BY ACCESS BY ACCESSCREATE ROLLBACK SEGMENT BY ACCESS BY ACCESSCREATE ROLLBACK SEGMENT BY ACCESS BY ACCESSCREATE ROLLBACK SEGMENT BY ACCESS BY ACCESSDROP USER BY ACCESS BY ACCESSDROP USER BY ACCESS BY ACCESSDROP USER BY ACCESS BY ACCESSALTER USER BY ACCESS BY ACCESSALTER USER BY ACCESS BY ACCESSALTER USER BY ACCESS BY ACCESSBECOME USER BY ACCESS BY ACCESSBECOME USER BY ACCESS BY ACCESSBECOME USER BY ACCESS BY ACCESSCREATE USER BY ACCESS BY ACCESSCREATE USER BY ACCESS BY ACCESSCREATE USER BY ACCESS BY ACCESSDROP TABLESPACE BY ACCESS BY ACCESSDROP TABLESPACE BY ACCESS BY ACCESSDROP TABLESPACE BY ACCESS BY ACCESSMANAGE TABLESPACE BY ACCESS BY ACCESSMANAGE TABLESPACE BY ACCESS BY ACCESSMANAGE TABLESPACE BY ACCESS BY ACCESSALTER TABLESPACE BY ACCESS BY ACCESSALTER TABLESPACE BY ACCESS BY ACCESSALTER TABLESPACE BY ACCESS BY ACCESSCREATE TABLESPACE BY ACCESS BY ACCESSCREATE TABLESPACE BY ACCESS BY ACCESSCREATE TABLESPACE BY ACCESS BY ACCESSALTER SESSION BY ACCESS BY ACCESSALTER SESSION BY ACCESS BY ACCESSALTER SESSION BY ACCESS BY ACCESSCREATE SESSION BY ACCESS BY ACCESSCREATE SESSION BY ACCESS BY ACCESSCREATE SESSION BY ACCESS BY ACCESSAUDIT SYSTEM BY ACCESS BY ACCESSAUDIT SYSTEM BY ACCESS BY ACCESSAUDIT SYSTEM BY ACCESS BY ACCESSALTER SYSTEM BY ACCESS BY ACCESSALTER SYSTEM BY ACCESS BY ACCESSALTER SYSTEM BY ACCESS BY ACCESS177 rows selected.

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569