Oracle auditing details

Starting from Oracle Database 10 Gb, columns SQL _TEXT and SQL _BIND in DBA_AUDIT_TRAIL are filled only when the initial parameter AUDIT_TRAIL is set to DB_EXTENDED.
 
Oracle uses a large number of different audit methods to monitor which permissions are used and which objects are accessed. Auditing does not prevent the use of these permissions, but provides useful information to reveal abuse and misuse of permissions.
 
The following table summarizes different types of audits in Oracle databases.
 
Audit type
Description
 
Statement Audit
Audit SQL statements by statement type, regardless of the specific mode object to be accessed. You can also specify one or more users in the database to audit these users for specific statements.
 
Permission Audit
Audit system permissions, such as create table or alter index. Like Statement Audit, permission audit can specify one or more specific users as the audit target.
 
Pattern Object audit
Audits specific statements running on objects in a specific mode (for example, the UPDATE Statement on the orders tables ). Pattern Object audit is always applied to all users in the database
 
Fine-grained Audit
Audit table access and permissions based on the content of the access object. Use the package DBMS_FGA to create a policy for a specific table
 
The following sections describe how to manage the DBA system and audit the use of object permissions. When a certain granularity is required, DBA can use fine-grained audit to monitor access to certain rows or columns in the table, not just whether to access the table.
 
Audit-related parameters
 
SQL> show parameter audit
 

Audit_file_dest
Audit_sys_operations
Audit_trail
 

Audit_sys_operations:
The default value is false. When it is set to true, all operations on sys users (including users logging on as sysdba and sysoper) will be recorded, and audit trail will not be written in the aud $ table, this is easy to understand. If the database has not started aud $, connection information such as conn/as sysdba can only be recorded elsewhere. If it is a windows platform, aupd-trail will be recorded in windows event management. If it is a linux/unix platform, it will be recorded in the file specified by the audit_file_dest parameter.
 
Audit_trail:
None: the default value. No audit is performed;
DB: records the audit trail in the audit-related tables of the database, such as aud $. The audit results only contain connection information;
DB, Extended: in this way, in addition to the connection information, the audit results also contain the specific statements executed at that time;
OS: records the audit trail in the operating system file. The file name is specified by the audit_file_dest parameter;
XML: New in 10 Gb.
 
10g Values:
 
None
 
Disables database auditing.
 
OS
 
Enables database auditing and directs all audit records to the operating system's audit trail.
 
Db
 
Enables database auditing and directs all audit records to the database audit trail (the SYS. AUD $ table ).
 
Db_extended
 
Enables database auditing and directs all audit records to the database audit trail (the SYS. AUD $ table). In addition, populates the SQLBIND and sqltext clob columns of the SYS. AUD $ table.
 
Xml
 
Enables database auditing and writes all audit records to XML format OS files.
 
Xml, extended
 
Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.
 
Note: The AUDIT_TRAIL parameter is not dynamic. to make changes in the AUDIT_TRAIL parameter take effect, you must close the database and restart the database. When auditing the SYS. AUD $ table, you should monitor the table size to avoid affecting the space requirements of other objects in the SYS tablespace. We recommend that you periodically Archive rows in SYS. AUD $ and intercept the table. Oracle provides the DELETE_CATALOG_ROLE role, which is used with special accounts in batch processing jobs to archive and intercept audit tables.
 
Audit_file_dest: Audit_trail = OS file location

1 2 3 4 Next Page