Oracle 11g audit file and oracle11g audit file

Oracle 11g audit file and oracle11g audit file

Audit:

The audit option enabled by default for 11g. The default value of the AUDIT_TRAIL parameter is DB, which indicates that audit data will be recorded in the base table of the audit dictionary in the database. In Oracle 10 Gb, the default value of this parameter is none, that is, audit is not enabled. Oracle officially announced that audit logs enabled by default will not have a huge negative impact on the performance of most product databases, oracle also recommends OS file-based audit logging (OS audit trail files ).

 

In 11g, the create session will be recorded as the audited permission. Therefore, when the SYSTEM tablespace cannot be extended due to disk space, this part of audit records cannot be generated, as a result, new sessions of common users cannot be created normally and normal users cannot log on to the database. In this scenario, users with SYSDBA identities can still be used to create sessions, and some records can be deleted after appropriate backup of audit data, or TRUNCATEAUD $ can solve the above problems.

When AUDIT_TRAIL is set to OS, the audit record file is generated in the directory specified by the AUDIT_FILE_DEST parameter. All these files can be deleted or copied at any time.

 

The following permissions will be audited for all users:

SQL> select privilege, success, failure fromdba_priv_audit_opts;

 

PRIVILEGE SUCCESS FAILURE

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

CREATE EXTERNAL JOB BY ACCESS

CREATE ANY JOB BY ACCESS

GRANT ANY OBJECT PRIVILEGE BY ACCESS

EXEMPT ACCESS POLICY BY ACCESS

CREATE ANY LIBRARY BY ACCESS

GRANT ANY PRIVILEGE BY ACCESS

DROP PROFILE BY ACCESS

ALTER PROFILE BY ACCESS

DROP ANY PROCEDURE BY ACCESS

ALTER ANY PROCEDURE BY ACCESS

CREATE ANY PROCEDURE BY ACCESS

ALTER DATABASE BY ACCESS

GRANT ANY ROLE BY ACCESS

CREATE PUBLIC DATABASE LINK BY ACCESS

DROP ANY TABLE BY ACCESS

ALTER ANY TABLE BY ACCESS

CREATE ANY TABLE BY ACCESS

DROP USER BY ACCESS

ALTER USER BY ACCESS

CREATE USER BY ACCESS

CREATE SESSION BY ACCESS

AUDIT SYSTEM BY ACCESS

ALTER SYSTEM BY ACCESS

 

23 rows selected.

 

Existing audit records in the current database. LOGNO/LOGOFF is generated for audit 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 the SYSTEM tablespace is too large:

In 96.216, LOGOFF and LOGOFF are more than 0.2 billion records respectively, and the aud $ table size is 80 GB.

 

Solution:

1. When the aud $ table is too large, you can clear the interrogation data in the table:

SQL> truncate table sys. aud $;

 

2. You can disable the create session interrogation:

SQL> noaudit create session;

 

3. Shut down the database interrogation and restart the database:

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