Oracle Audit Capabilities

Oracle Audit Capabilities

I. INTRODUCTION

Auditing (Audit) is used to monitor database operations performed by users, and audit records can exist in a data dictionary table (known as Audit records: tables stored in the system tablespace SYS.AUD$ , viewable through view dba_audit_trail) or operating system audit records (the default location is $ORACLE_BASE/admin/$ORACLE_SID/adump/).. Auditing is not turned on by default.

Audit records contain information such as audited actions, actions performed by the user, date and time of the operation, and so on.

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.

Two. Configuration 2.1. Audit is installed

Sqlplus> Connect/as SYSDBA
Sqlplus> select * from sys.aud$; -No records returned
Sqlplus> select * from Dba_audit_trail; -No records returned

If you do the above query when the table does not exist, indicating that the audit-related tables are not installed, need to install, after installation to restart the database

Sqlplus> Connect/as SYSDBA
sqlplus> @ $ORACLE _home/rdbms/admin/cataudit.sql

2.2. Audit Table Space Migration

The audit table is installed by default in the system table space. You can migrate related tables to other table spaces

Sql>connect/ as SYSDBA; Sql>Alter Tableaud$Move tablespaceNew_tablespace; Sql>Alter IndexI_aud1Rebuild Online tablespaceNew_tablespace; Sql>Alter Tableaudit$Move tablespaceNew_tablespace; Sql>Alter IndexI_auditRebuild Online tablespaceNew_tablespace; Sql>Alter TableAudit_actionsMove tablespaceNew_tablespace; Sql>Alter IndexI_audit_actionsRebuild Online tablespaceNew_tablespace;

2.3. Audit parameters

Audit_sys_operations：

Whether to audit the SYSDBA users, the results of the SYSDBA user audit, Linux stored in the audit_file_dest parameter specified location of the AUD file, windows in the Event Viewer system log

Audit_trail：

None: Is 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.
注：这两个参数是static参数，需要重新启动数据库才能生效。

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

2.5. Other Audit options

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

2.6. Audit-related views

dba_audit_trail: Save all audit trail, in fact it's just a aud$-based view. Other views 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 for 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 audits are set by the on the default clause in the database.

Three. Use 3.1. Open/Close Audits

Open Audit
Alter system set AUDIT_SYS_OPERATIONS=TRUE Scope=spfile;

Alter system set audit_trail=db,extended Scope=spfile;

Close Audit
alter system set Audit_trail=none;

3.2. Table Operation Audit

Set up audit content
Audit all by Lyj access;
Audit Select table, Update table, insert table, delete table by Lyj by Access;
Audit Execute procedure by LYJ access;

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 SYSTEM; --Audit System user's Delete,update,insert operation to table user.table

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

Noaudit all on t_test; Revocation Audit

TRUNCATE TABLE aud$; Clear the contents of the audit table

Appendix

Http://www.cnblogs.com/CodingArt/articles/1653315.html
http://www.dbase.cc/2017/09/13/oracle/oracle%E5%AE%A1%E8%AE%A1/

Oracle Audit Capabilities