How to Audit User Activity in Oracle Database

 

As a DBA, monitoring database performance and availability is only one part of our responsibility. We also need to understand what is happening inside the database, who is accessing it, what changes are being made, and whether any activity could potentially impact the database environment. 
 
There are situations where we need to investigate questions like: 
 
- Who logged in to the database at a particular time?
- Which user modified or deleted a table?
- Who executed a particular SQL statement?
- Was there any unauthorized access?
- Which user made changes to database objects?
- Are there repeated failed login attempts? 
  
This is where Oracle Database Auditing becomes useful. 
 
Oracle provides different auditing mechanisms that help DBAs track user activity, monitor database operations, and investigate security-related incidents. In this blog, we will look at how to audit user activity in Oracle Database using practical DBA queries and examples.

1. What Is Auditing in Oracle Database? 

Auditing is the process of recording database activities performed by users and applications. 
 
It helps DBAs maintain visibility into database operations and provides an audit trail that can be used during security reviews, compliance checks, and incident investigations. 
 
For example, if a user accidentally drops an important table, auditing can help identify which account performed the operation and when it happened. 
 
Oracle supports different auditing approaches, including: 
 
- Unified Auditing
- Traditional Auditing
- Fine-Grained Auditing (FGA) 
 
For most modern Oracle environments, Unified Auditing is the recommended approach.

2. Understanding Unified Auditing 

Unified Auditing was introduced in Oracle Database 12c and provides a centralized way to manage and review audit records. 
 
Instead of checking multiple audit locations, DBAs can use a common audit trail to review different database activities. 
 
Some activities that can be audited include: 
 
- Successful and failed logins
- User creation and deletion
- Privilege grants and revokes
- DDL operations such as CREATE, ALTER, and DROP
- DML operations such as INSERT, UPDATE, and DELETE
- Access to sensitive database objects 
 
To check whether Unified Auditing is enabled, execute:  
 
SELECT VALUE
FROM V$OPTION
WHERE PARAMETER = 'Unified Auditing';
A value of TRUE indicates that pure Unified Auditing is enabled. 
 
Note: Oracle Database can also operate in mixed auditing mode, where unified and traditional auditing capabilities coexist. Therefore, this query alone should not be treated as a complete assessment of the auditing configuration.

 3. Check User Login Activity

One of the most common requirements during a security review is identifying who accessed the database and when. 
 
Oracle provides the UNIFIED_AUDIT_TRAIL view to retrieve login-related audit information. 
 
To check successful and failed login attempts:

SELECT
    EVENT_TIMESTAMP,
    DBUSERNAME,
    USERHOST,
    ACTION_NAME,
    RETURN_CODE,
    CLIENT_PROGRAM_NAME,
    AUTHENTICATION_TYPE
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME = 'LOGON'
ORDER BY EVENT_TIMESTAMP DESC;

What should DBAs look for? 

- Unexpected login attempts from unknown hosts.
- Multiple failed login attempts.
- Database access during unusual hours.
- Connections using unexpected client applications.
- Logins from accounts that should no longer be active. 
 
A RETURN_CODE of 0 generally indicates a successful operation. Non-zero return codes indicate errors, which should be interpreted based on the specific Oracle error.

4. Identify Failed Login Attempts 

Repeated failed login attempts can indicate incorrect application credentials, configuration issues, or potentially suspicious activity.

To identify failed logins:

SELECT
    EVENT_TIMESTAMP,
    DBUSERNAME,
    USERHOST,
    RETURN_CODE,
    CLIENT_PROGRAM_NAME
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME = 'LOGON'
AND RETURN_CODE <> 0
ORDER BY EVENT_TIMESTAMP DESC;
This query is particularly useful when investigating account lockouts or unexpected authentication failures. 
 
For example, if an application account generates hundreds of failed login attempts within a short period, the DBA should check the application connection configuration and investigate the source of those attempts.

 5. Audit DDL Activities

DDL operations can directly impact database objects. Commands such as CREATE, ALTER, DROP, and TRUNCATE should be monitored, especially in production environments. 
 
To review DDL activity:

SELECT
    EVENT_TIMESTAMP,
    DBUSERNAME,
    ACTION_NAME,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    SQL_TEXT,
    USERHOST
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME IN (
    'CREATE TABLE',
    'ALTER TABLE',
    'DROP TABLE',
    'TRUNCATE TABLE'
)
ORDER BY EVENT_TIMESTAMP DESC;
This helps identify which user performed a particular DDL operation and which database object was affected. 
 
For example, a production table is unexpectedly dropped. The DBA can review the audit trail to identify the account responsible, the execution time, and the SQL statement, provided the relevant action was audited and the SQL text was captured.

This information can significantly reduce the time required for incident investigation. 

 6. Audit DML Operations

Tracking INSERT, UPDATE, and DELETE operations is another important requirement, especially when dealing with business-critical tables. 
 
However, auditing every DML operation across the entire database can generate a large volume of audit records. 
 
A better approach is to identify critical tables and configure auditing only for the required activities. 
 
For example, to create an audit policy for DML operations: 

CREATE AUDIT POLICY audit_dml_policy
ACTIONS INSERT, UPDATE, DELETE;
Enable the policy for a specific user:

AUDIT POLICY audit_dml_policy
BY <<username>>;
 To review the captured activity:

SELECT
    EVENT_TIMESTAMP,
    DBUSERNAME,
    ACTION_NAME,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME IN (
    'INSERT',
    'UPDATE',
    'DELETE'
)
ORDER BY EVENT_TIMESTAMP DESC;
One important point: DML auditing can have an impact on audit volume and database performance depending on the workload and audit configuration. Always test the policy before implementing it in production. 

7. Audit User Creation and Privilege Changes 

User management activities should always be monitored in a production database. 
 
Changes such as creating users, granting DBA privileges, or modifying system privileges can introduce security risks if they are not properly controlled. 
 
To review user and privilege-related activities:

SELECT
EVENT_TIMESTAMP,
DBUSERNAME,
ACTION_NAME,
SQL_TEXT,
RETURN_CODE
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME IN (
'CREATE USER',
'ALTER USER',
'DROP USER',
'GRANT',
'REVOKE'
)
ORDER BY EVENT_TIMESTAMP DESC;
This is useful during periodic access reviews and security audits. 
 
As a DBA, I would particularly recommend monitoring privileged accounts and reviewing any unexpected grants of roles such as DBA.

8. Enable Auditing Using Audit Policies 

Oracle Unified Auditing uses audit policies to define which activities should be recorded. 
 
For example, if the requirement is to monitor database logins, a dedicated policy can be created.

CREATE AUDIT POLICY audit_login_policy
ACTIONS LOGON;
Enable the policy:

AUDIT POLICY audit_login_policy;
Similarly, a policy can be created to monitor administrative activities.

CREATE AUDIT POLICY audit_user_changes
ACTIONS CREATE USER,
        ALTER USER,
        DROP USER;
Enable it:

AUDIT POLICY audit_user_changes;
To check the configured audit policies:

SELECT
    POLICY_NAME,
    ENABLED_OPTION,
    ENTITY_NAME,
    ENTITY_TYPE
FROM AUDIT_UNIFIED_ENABLED_POLICIES;
To check available audit policies: 

SELECT POLICY_NAME
FROM AUDIT_UNIFIED_POLICIES
ORDER BY POLICY_NAME;
These queries help DBAs understand which auditing policies are configured and enabled.

 9. Fine-Grained Auditing (FGA)

Sometimes, auditing all access to a table is unnecessary. The requirement may be to monitor access to specific sensitive columns or only when certain conditions are met. 
 
Oracle provides Fine-Grained Auditing for these scenarios. 
 
For example, consider an EMPLOYEES table containing salary information. The DBA may want to audit access to the SALARY column. 
 
A sample FGA configuration: 

BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'AUDIT_SALARY_ACCESS',
audit_column => 'SALARY',
statement_types => 'SELECT,UPDATE'
);
END;
/
This creates an FGA policy to monitor access involving the SALARY column for the specified statement types. 
 
FGA is particularly useful for sensitive business information such as employee salaries, customer details, and financial records. 
 
To review FGA-related audit records, DBAs can query the unified audit trail and filter for the relevant policy name.

SELECT
    EVENT_TIMESTAMP,
    DBUSERNAME,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    FGA_POLICY_NAME,
    SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE FGA_POLICY_NAME = 'AUDIT_SALARY_ACCESS'
ORDER BY EVENT_TIMESTAMP DESC;

10. Check Current Database Sessions 

Audit records provide historical information, but sometimes DBAs need to identify users currently connected to the database. 
 
The V$SESSION view is useful for checking active database sessions.

SELECT
    SID,
    SERIAL#,
    USERNAME,
    STATUS,
    MACHINE,
    PROGRAM,
    LOGON_TIME,
    SQL_ID
FROM V$SESSION
WHERE USERNAME IS NOT NULL
ORDER BY LOGON_TIME DESC;
This helps identify currently connected users, their client machines, and the applications they are using. 
 
Remember that V$SESSION provides current session information, not a permanent historical audit trail.

11. Purge and Manage Audit Records

Auditing is important, but audit records also consume storage. In busy production environments, audit data can grow significantly over time. 
 
DBAs should regularly monitor the audit trail size and define an appropriate retention policy.
Oracle provides the DBMS_AUDIT_MGMT package to manage audit trail maintenance. 
 
For example, the following query checks the audit trail cleanup configuration:

SELECT *
FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
To initialize unified audit trail cleanup management:

BEGIN
    DBMS_AUDIT_MGMT.INIT_CLEANUP(
        audit_trail_type =>
            DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        default_cleanup_interval => 24
    );
END;
/
A cleanup process can then be scheduled based on the organization's audit retention requirements. 
 
Do not purge audit records simply to reclaim space. Follow the organization's compliance and retention requirements, preserve records required for investigations, and ensure audit data is backed up or archived appropriately. 
 
Auditing user activity is an important part of Oracle Database administration. It gives DBAs better visibility into database access, user operations, privilege changes, and activities performed on critical database objects. 
 
Oracle Unified Auditing provides a centralized approach to monitoring these activities, while Fine-Grained Auditing helps when more specific monitoring is required. 
 
In my experience, auditing should not be treated as something that is configured only during compliance reviews. It should be part of regular database security and operational practices.
Having the right audit policies in place can make a big difference when investigating unexpected database changes, identifying suspicious activity, or answering the simple but important question: Who did what in the database, and when?

Comments