Oracle Audit--aud$ large footprint processing scheme

Oracle 11G, the database default is the audit function, so sometimes we forget to turn off this feature caused the system table space is full, but because the shutdown audit function needs to restart the database, such operations are not allowed in the production environment, so we need to find out which kind of audit generated more, And then shut down separately, we can find the following methods:
If you find that the aud$ table is larger, check what type of audit is the space:
Sql> Select Action_name,count (*) from Dba_audit_trail group by Action_name; Generally, logon and logoff types are the most audited. Cancellation of such audits:
Sql> Noaudit session whenever successful; In general, such audits remain as good if the space does not occupy a particular number of places. You can cancel audits of some users who are logged in particularly frequently, such as DBSNMP users:
Sql> Noaudit session by DBSNMP;
Cleanup of table sys.aud$ after auditing is turned off
TRUNCATE TABLE sys.aud$;

1. What is Audit

Auditing (Audit) is used to monitor database operations performed by users, and ORACLE stores audit trail results to OS files (the default location is $oracle_base/admin/$ORACLE _sid/adump/ ) or the database (stored in the sys.aud$ table in the system table space, which is viewable through the view dba_audit_trail). By default, auditing is not turned on before 11g.
Regardless of whether you open the auditing capabilities of the database, the following operating systems Force logging: Connect instance with administrator privileges, start the database, and close the database.

2, and audit related to the two main parameters

Audit_sys_operations:
The default is false, and when set to True, all SYS users (including those logged in as Sysdba,sysoper) will be logged, audit trail will not be written in the aud$ table, which is a good understanding if the database has not been started aud$ unavailable, So the connection information, like Conn/as sysdba, can only be recorded elsewhere. If it is a Windows platform, Audti Trail is recorded in event management for Windows, and if it is the Linux/unix platform, it is recorded in the file specified in the Audit_file_dest parameter.

Audit_trail:
NONE:11G before the default value, do not audit;
DB: Audit trail is recorded in the database audit related tables, such as aud$, the results of audit only connection information;
Db,extended: In this way, the audit results include the specific statements executed at that time except the connection information;
OS: The audit trail is recorded in the operating system file and the file name is specified by the audit_file_dest parameter;
New in the xml:10g.

Note: These two parameters are static parameters and need to be restarted for the database to take effect.

3. Audit level

When auditing is turned on, the database can be audited at three levels: Statement (statement), PRivilege (permission), object.

Statement:
Audit by statement, for example, Audit table audits all the Create Table,drop table,truncate table statements in the database, and alter session by CMY audits all database connections for the CMY user.

Privilege:
Audit by permission, when the user is using this permission is audited, such as execute grant select any table to a, when the audit Select any table statement is executed, when user a accesses the User B's table (such as SELECT * from B.T) will use the SEL ECT any table permission, so it will be audited. Note that users are the owner of their own tables, so users accessing their own tables will not be audited.

Object:
Auditing by object, auditing only the relevant operations on the specified object of the on keyword, such as Aduit Alter,delete,drop,insert on cmy.t by Scott; The CMY user's T-table is audited here, but the By clause is used at the same time, so only actions initiated by the Scott user are audited. Note that Oracle does not provide auditing capabilities for all objects in the schema, only one object audit, and Oracle provides an on default clause to enable automatic auditing for objects created later, such as executing audit drop on default by Access; , the drop operation for the subsequently created object is audited. However, this default will be valid for all database objects created later, and there seems to be no way to specify that only the object created by a user is valid, which is slightly less than trigger can "audit" the DDL of the schema.

4. Some other options for auditing

by access/by session:
By access each audited operation generates a audit trail.
by session within a session the same type of operation will only generate a audit trail, which defaults to the by session.

Whenever [NOT] successful:
Whenever successful operation was successful (the ReturnCode field in Dba_audit_trail is 0) before auditing,
Whenever not successful conversely. If the clause is omitted, it will be audited regardless of the success or absence of the operation.

5. Audit-related views

Dba_audit_trail: Save all audit trail, in fact it's just a aud$-based view. Other view dba_audit_session,dba_audit_object,dba_audit_statement are just a subset of the Dba_audit_trail.
Dba_stmt_audit_opts: Can be used to view the audit options of the statement audit level, which is the statement level of auditing that the database has set up. Dba_obj_audit_opts,dba_priv_audit_opts View function similar to
All_def_audit_opts: Used to view which default object auditing is set by the on-default clause of the database.

6. Cancellation of Audit

The corresponding Audit statement audit changed to Noaudit, such as audit session whenever successful corresponding cancellation audit statement for Noaudit session whenever successful;

7, the audit in 10g told everything

Oracle Database 10g auditing captures user behavior at a very detailed level, which eliminates manual, trigger-based audits.
Assume that user Joe has permission to update the table and updates one row of data in the table as follows:
Update SCOTT. EMP Set salary = 12000 where empno = 123456;
How do you track this behavior in the database? In Oracle 9i databases and their lower versions, audits can only capture "who" to do this, not the "what" content that was executed. For example, it lets you know that Joe updated all of SCOTT's table EMP, but it doesn't show that he updated the salary column for employee number 123456 in the table. It does not show the value of the salary column before the change-to capture such detailed changes, you will have to write your own triggers to capture the values before the changes, or use Logminer to retrieve them from the archive log.
Fine-grained auditing (FGA), introduced in Oracle 9i, is able to record SCN and row-level changes to reconstruct old data, but they can only be used with SELECT statements, not DML, such as update, INSERT, and DELETE statements. Therefore, for versions prior to Oracle database 10g, using triggers is the only reliable way to track user-initiated changes at the row level, but it is not an attractive option.

8, example explanation

8.1. Activation Audit

Sqlplus/as SYSDBA

Sql> Show Parameter Audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Audit_file_dest String/u01/app/oracle/admin/orcl/adump
Audit_sys_operations Boolean FALSE
Audit_syslog_level string
Audit_trail string NONE

Sql> alter system set AUDIT_SYS_OPERATIONS=TRUE Scope=spfile; --Audit management user (login with sysdba/sysoper role)
Sql> alter system set audit_trail=db,extended Scope=spfile;
sql> startup force;
Sql> Show Parameter Audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Audit_file_dest String/u01/app/oracle/admin/orcl/adump
Audit_sys_operations Boolean TRUE
Audit_syslog_level string
Audit_trail string DB, EXTENDED

8.2. Start the audit

Sqlplus/as SYSDBA
Sql> audit all on t_test;
Sql> Conn U_test
Sql> select * from T_test;
sql> INSERT INTO U_test.t_test (C2,C5) VALUES (' Test1 ', ' 2 ');
Sql> commit;
Sql> Delete from U_test.t_test;
Sql> commit;
Sql> Conn/as SYSDBA
sql> Col dest_name format A30
Col os_username format A15
Col USERNAME format A15
Col userhost format A15
Col TERMINAL format A15
Col obj_name format A30
Col sql_text format A60
Sql> Select Os_username,username,userhost,terminal,timestamp,owner,obj_name,action_name,sessionid,os_process, Sql_text from Dba_audit_trail;

Sql> Audit Select table by U_test by Access;
If you add by user after the command, only the user's operation is audited, and if the by user is omitted, all users in the system are audited (not including SYS users).

Cases:
AUDIT DELETE any TABLE; --Audit the Delete table operation
AUDIT DELETE any TABLE whenever not successful; --only audit the deletion failure condition
AUDIT DELETE any TABLE whenever successful; --Only audit deletion success
AUDIT Delete,update,insert on user.table by test; --Audit test user's Delete,update,insert operation on table user.table

8.3. Revocation of Audit
Sql> Noaudit all on t_test;

9. Audit statements

Audits in multi-tiered environments: appserve-Application Server, jackson-client
AUDIT SELECT TABLE by Appserve on BEHALF of Jackson;

To audit a connection or disconnect:
AUDIT SESSION;
AUDIT SESSION by Jeff, Lori; --Specify user

Audit permissions (actions that can be performed with this permission):
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;

Object audits:
AUDIT DELETE on Jeff.emp;
AUDIT SELECT, INSERT, DELETE on jward.dept by ACCESS whenever successful;

Canceling an audit:
Noaudit session;
Noaudit session by Jeff, Lori;
Noaudit DELETE any TABLE;
Noaudit SELECT table, INSERT table, DELETE table,execute PROCEDURE;
Noaudit all; --Cancel All statement audits
Noaudit all privileges; --Cancel All rights audits
Noaudit all on DEFAULT; --Canceling all object audits

10. Clear Audit Information

DELETE from SYS. aud$;
DELETE from SYS. aud$ WHERE obj$name= ' EMP ';

11. Audit View

Stmt_audit_option_map--Audit option type code
Audit_actions--Action code
All_def_audit_opts--Default object audit options when object is created
Dba_stmt_audit_opts--Current database system audit options
Dba_priv_audit_opts--rights audit options
Dba_obj_audit_opts
User_obj_audit_opts--Object audit options
Dba_audit_trail
User_audit_trail--Audit record
Dba_audit_object
User_audit_object--List of audit objects
Dba_audit_session
User_audit_session--SESSION Audit
Dba_audit_statement
User_audit_statement--Statement auditing
Dba_audit_exists--Auditing using the by AUDIT not EXISTS option
Dba_audit_policies--Audit policies
Dba_common_audit_trail--standard audit + fine Audit

12. Move the Audit results table from the system table space to a different table space

In fact, the sys.aud$ table contains two LOB fields, not a simple move table.
Here are the specific procedures:
ALTER TABLE sys.aud$ move tablespace users;
ALTER TABLE sys.aud$ move lob (SQLBind) store as (Tablespace USERS);
ALTER TABLE sys.aud$ move lob (SQLTEXT) store as (Tablespace USERS);
ALTER INDEX SYS. I_AUD1 Rebuild tablespace u

Oracle Audit--aud$ large footprint processing scheme