Introduction to Oracle Audits

1. What is Audit

The audit (Audit) is used to monitor the database operations performed by the user, and ORACLE will deposit the audit trail results to the OS file (the default location is $oracle_base/admin/

$ORACLE _sid/adump/) or database (sys.aud$ tables stored in the system table space, available through View Dba_audit_trail view). Auditing is not turned on by default.

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.

2, and audit-related two main parameters

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, audit trail will not be written in the aud$ table

, this is well understood, if the database has not yet started aud$ is not available, then the connection information such as 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.

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).

Statement:

Audit by statement, such as audit table, which audits all the Create Table,drop table,truncate table statements in the database, and alter sessions by CMY

CMY all database connections to the user.

Privilege:

Audit by permission, when the user uses this permission is audited, such as executing grant select any table to a, after executing the audit Select any table statement, when user A

When a table with User B is accessed (such as SELECT * from b.t), the Select any table permission is used and is audited. Note that the user is the owner of their own table, so the user accesses the

Your own watch will not be audited.

Object:

Audit by object, only the related actions of the ON keyword-specified object, such as Aduit Alter,delete,drop,insert on cmy.t by Scott; This will be done on the CMY user's T-table

Audit, but the By clause is used at the same time, so only the actions initiated by the Scott user are audited. Note that Oracle does not provide audit capabilities for all objects in the schema, only one object is audited, and for subsequent objects, Oracle provides an on default clause for automatic auditing, such as executing audit drop on default by Access; , 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

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.

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.

5, and audit-related views

Dba_audit_trail: Save all the audit trail, it's actually just a aud$ based view. Other views

Dba_audit_session,dba_audit_object,dba_audit_statement are just a subset of Dba_audit_trail.

Dba_stmt_audit_opts: Can be used to view the audit options at the statement audit level, which statement level audits the database has set up.

The Dba_obj_audit_opts,dba_priv_audit_opts view feature is similar to the

All_def_audit_opts: Used to view which default object audits are set by the database with the on default clause.

6. Cancellation of Audit

The corresponding audit statement of the audit to Noaudit can be, such as audit session whenever successful corresponding cancellation of audit statements for Noaudit session whenever

successful;

7, 10g audit to inform all

Oracle Database 10g Audits capture user behavior at a very detailed level that eliminates manual, trigger-based auditing.

Assume that user Joe has permission to update that table and updates one row of data in the table as shown below:

Update SCOTT. EMP Set salary = 12000 where empno = 123456;

How do you track this behavior in the database? In an Oracle 9i database and its earlier versions, auditing can only capture "who" to do this and not capture what is executed

Content. For example, it lets you know that Joe has updated all of SCOTT's table EMP, but it does not show that he has updated the salary column with the 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 audits (FGA), introduced in Oracle 9i, can record the SCN number and row-level changes to reconstruct old data, but they can only be used for SELECT statements and not for DML, such as update, INSERT, and DELETE statements. Therefore, using triggers is not an attractive option for tracking user initial changes at the line level, but it is also the only reliable method for previous versions of Oracle database 10g.

8. Example explanation

8.1. Activate audit

Sqlplus/as SYSDBA

Sql> Show Parameter Audit

NAME TYPE VALUE

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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/