Oracle11g audit configuration process

1. What is audit?

Oracle's auditing capabilities allow database administrators to track and record operations on databases to ensure database security, compliance and integrity.

Oracle provides a variety of audit functions and methods that can be configured and used for different needs and scenarios, mainly including:

• Standard Auditing: used to audit specific SQL statements, privileges, objects, and operations.
• Fine-Grained Auditing (FGA): allows fine-grained control and auditing of table-level data access.
• Unified Auditing: a new feature introduced by Oracle 12c, integrating various audit methods such as standard auditing and FGA.

2. Enable audit

To enable database auditing, the parameter AUDIT_TRAIL needs to be initialized:

In addition, the following database parameters should be set:

• AUDIT_FILE_DEST: Specifies the dynamic parameters of the operating system audit trail location. The default location is $ORACLE_BASE/admin/$ORACLE_SID/adump.
• AUDIT_SYS_OPERATIONS: Enables auditing of actions issued by users SYS and users who connect with SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSKM, and SYSDG permissions. This parameter should be set to true.

You can modify the parameters through the following command

alter system set audit_sys_operations=TRUE scope=spfile;
alter system set audit_trail=db_extended scope=spfile;

Restart the database to take effect

shutdown immediate;
startup;

Check whether the audit function has been activated

show parameter audit;

3. Configuration standard audit

Standard audit is the basic audit level of Oracle databases, which can audit the operations of database objects (such as tables, views, procedures, etc.).

Audit records include records of DDL and DML operations, such as recording SELECT, UPDATE, INSERT, or DELETE operations of a table.

Standard audits can be configured with the AUDIT and NOAUDIT commands.

AUDIT syntax

AUDIT action [, action, ...] on object_name [by user] [whenever condition];

• action: Specifies the action to be audited, which can be a single action (such as SELECT, INSERT, UPDATE, DELETE) or a combination of multiple actions.
• object_name: Specifies the object to be audited, which can be a database object such as tables, views, and procedures.
• BY user: Optional, specifying the user to be audited. If not specified, the default is the current user.
• WHENEVER condition: Optional, specifying the conditions that trigger the audit, such as success or failure.
  AUDIT Parameter Description
• action：
  Single operation: SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, SYSTEM AUDIT, SESSION AUDIT, etc.
  Multiple operations combinations: separated by commas, such as SELECT, INSERT, and UPDATE to indicate that these three operations are audited simultaneously.
• object_name：
  It indicates that the object to be audited can be a database object such as table name, view name, and process name.
  You can also use ALL to audit all objects, or use the wildcard % to match multiple objects.
• by user：
  Specify the user to be audited, which can be a specific username or PUBLIC (effective for all users).
  If BY user is not specified, the default is the current user.
• whenever condition：
  You can use to specify the conditions that trigger audits, common conditions include SUCCESSFUL (successful operation) and NOT SUCCESSFUL (failed operation).
  For example, WHENEVER SUCCESSFUL means only successful operations audited, while WHENEVER NOT SUCCESSFUL means only failed operations audited.

Example:

Audit SELECT operations on specific tables:

AUDIT SELECT ON employees;

Audit SELECT operations on specific tables by specific users:

AUDIT SELECT ON employees BY hr_user;

Audit failed SELECT operations on a specific table:

AUDIT SELECT ON employees WHENEVER NOT SUCCESSFUL;

The enabled audit options and configurations, as well as the status information of the audit, can be displayed through the following query.

SELECT * FROM DBA_PRIV_AUDIT_OPTS; -- View audit configuration for privileged operations
SELECT * FROM DBA_STMT_AUDIT_OPTS; -- View audit configuration for statement-level operations
SELECT * FROM DBA_OBJ_AUDIT_OPTS;  -- View the audit configuration for object-level operations

By using AUDIT statements, administrators can have the flexibility to configure audit policies to track and record the execution of specific operations in the database to ensure database security and compliance.

4. Audit record management

Audit record management includes viewing, archiving and cleaning audit records.

1. View audit records

Audit records can be viewed through the following views:

DBA_AUDIT_TRAIL：View standard audit records。
DBA_FGA_AUDIT_TRAIL：View fine-grained audit records。
UNIFIED_AUDIT_TRAIL：View unified audit records。

2. Clean up audit records

Expired audit records can be cleaned regularly to avoid taking up too much storage space.

For example:

• Delete standard audit records 30 days ago:

DELETE FROM $ WHERE TIMESTAMP# < SYSDATE - 30;

• Delete fine-grained audit records 30 days ago:

DELETE FROM DBA_FGA_AUDIT_TRAIL WHERE TIMESTAMP < SYSDATE - 30;

Please note that turning on the audit function may have a certain impact on database performance, so audit options should be configured according to actual needs and security policies, and audit records should be maintained regularly to ensure the security and compliance of the database.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.