Overview of Oracle's audit capabilities

1. What is Audit

Auditing (Audit) is used to monitor database operations performed by a user, and an audit record can exist in a data dictionary table called an Audit record: The SYS stored in the system tablespace. aud$ table, available through View Dba_audit_trail view) or operating system audit records (the default location is $oracle_base/admin/$ORACLE _sid/adump/). Auditing is not turned on by default.

When the audit of the database is enabled, an audit record is generated during the execution phase of the statement. Audit records contain information such as audit operations, actions performed by the user, date and time of operation, and so on.

Regardless of whether you open the audit function of the database, the following operating systems will force logging: Connect instance with administrator rights; start the database; close the database.

(1) Oracle Audit function

Auditing is the monitoring and recording of selected user actions, typically used to:

Review of suspicious activities. For example, when data is deleted by an unauthorized user, the security administrator can then decide to audit all connections to the database, as well as the successful or unsuccessful deletion of all tables in the database.

Monitor and collect data about the specified database activity. For example, a DBA can collect statistics such as what has been modified, how many times the logical I/O has been executed.

The audit choices allowed by Oracle are limited to the following areas:

Successful execution of an audit statement, unsuccessful execution, or both.

Execute once for each user session audit statement or once for each execution of the statement.

An audit of the activities of all users or specified users.

(2) Audit related table installation

Sqlplus> Connect/as SYSDBA

Sqlplus> select * from sys.aud$; --No records returned

Sqlplus> select * from Dba_audit_trail; -No records returned

If you find that the table does not exist when you make the above query, the audit-related table is not installed and needs to be installed.

Sqlplus> Connect/as SYSDBA

sqlplus> @ $ORACLE _home/rdbms/admin/cataudit.sql

The audit table is installed in the system table space. So make sure that the system tablespace has enough space to store the audit information.

To restart the database after installation

To move an audit-related table to another table space

Because audit-related tables such as aud$ tables are stored in the system tablespace, it is best to move aud$ to other tablespaces in order not to affect the performance of the system and to protect the table space. You can use the following statement to move:

Sql>connect/as Sysdba;

Sql>alter table aud$ move tablespace <new tablespace>;

Sql>alter index I_AUD1 rebuild online tablespace <new tablespace>;

Sql> ALTER TABLE audit$ move tablespace <new tablespace>;

sql> ALTER index I_audit rebuild online tablespace <new tablespace>;

Sql> ALTER TABLE audit_actions move tablespace <new tablespace>;

sql> ALTER index i_audit_actions rebuild online tablespace <new tablespace>;

2, and audit-related two main parameters

(1) Audit_sys_operations:

The default is false, and when set to True, all SYS users (including users logged in as SYSDBA, Sysoper) will be logged, and audit trail will not be written in the aud$ table, which is well understood, if the database has not yet started aud$ is not available. So the connection information like Conn/as SYSDBA can only be recorded elsewhere. If it is a Windows platform, Audti Trail is recorded in Windows Event management, and if the Linux/unix platform is recorded in the file specified by the Audit_file_dest parameter.

(2) Audit_trail:

None: is the default value, does not audit;

DB: Audit trail is recorded in the Audit related table of the database, such as aud$, the result of audit is only connection information;

Db,extended: In this audit result, in addition to the connection information also contains the specific statements executed at that time;

OS: The audit trail is recorded in the operating system file, and the filename is specified by the audit_file_dest parameter;

New in the xml:10g.

Note: These two parameters are static parameters that require a restart of the database to take effect.

3. Audit level

When the audit function is turned on, the database can be audited at three levels: Statement (statement), Privilege (permissions), Object (objects).

(1) Statement:

Statement audits, auditing some type of SQL statement, and not specifying a structure or object. 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 CMY users.

(2) Privilege:

Permission audits, which are audited when the user uses the permission, such as executing grant select any table to a, and when user a accesses the table of User B (for example, select * from B.T) when the audit Select any table statement is executed, Selec T any table permission, so it will be audited. Note that users are the owners of their own tables, so users accessing their tables will not be audited. Privilege audits.

(3) Object:

Object auditing, an audit of a specified statement on a particular pattern object. For example, the audit on keyword specifies the related operation of the object, such as Aduit Alter,delete,drop,insert on cmy.t by Scott; The CMY user's T-table is audited, but the By clause is used, so only the actions initiated by the Scott user are audited.

Note: Oracle does not provide audit capabilities for all objects in the schema, only one object audit, and Oracle provides an on default clause for automatic auditing of the objects created later, such as executing audit drop on default by Access , and the drop operation for the object that is subsequently created will be audited. However, this default will be valid for all database objects created after that, and there seems to be no way to specify that only objects created by a user are valid, and that it is less likely than trigger to "audit" the schema's DDL.

4. Some other audit options

(1) by access/by session:

By access each of the audited operations will generate a audit trail.

An operation of the same type in session one will only generate a audit trail, which defaults to by.

(2) whenever [NOT] successful:

The whenever successful operation was successful (the ReturnCode field in Dba_audit_trail was 0) before auditing,

Whenever not successful vice versa. If this clause is omitted, it will be audited regardless of the success of the operation.

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45406.htm