How to Identify Inactive Users in Oracle Databases and Applications

Identifying inactive users in Oracle involves examining both application-level login records within Oracle's administrative consoles and database-level account statuses and activity logs. The method you use depends on whether you're managing users within an Oracle application (like Oracle Cloud Infrastructure or Oracle Identity Cloud Service) or directly within an Oracle Database.

1. Identifying Inactive Users in Oracle Applications and Cloud Consoles

Many Oracle applications and cloud services provide built-in tools within their administrative interfaces to help you find inactive users. This is often the most straightforward method for application-level user management.

• Navigate to User Management: Typically, you'll start by navigating to the "Users" or "Identity & Access Management" section within the application's navigation pane.
• Access Inactive User Reports: Select the "Inactive Users" option or a similar feature from the navigation pane, which presents a list of users based on their activity.
• Define Inactivity Period: The "View settings" within these interfaces usually allow you to specify the duration of inactivity (e.g., last login over 30, 60, or 90 days) to determine which users are considered inactive.
• Filter by Specific Date: If you need to ascertain the number of inactive users for a particular date, you can often click "View," then click the calendar icon (typically located below a "Last login" column) to select the desired date, and finally click "Apply" to filter the results.

This approach is common in environments such as:

• Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM)
• Oracle Identity Cloud Service (IDCS)
• Oracle Identity Manager (OIM)
• Other Oracle SaaS applications with integrated user management.

2. Identifying Inactive Users in Oracle Databases

Finding inactive users directly within an Oracle Database requires querying system views, often necessitating specific auditing configurations for comprehensive results.

2.1 Checking Account Status and Last Login

The DBA_USERS data dictionary view provides crucial information about all user accounts in the database, including their status.

• ACCOUNT_STATUS: Indicates if an account is OPEN, LOCKED, EXPIRED, or EXPIRED & LOCKED. Locked or expired accounts are inherently inactive.
• LAST_LOGIN: (Available from Oracle Database 12c Release 2 onwards, and requires AUDIT_TRAIL parameter set to DB or OS for password-authenticated sessions). This column records the date of the last successful login. A NULL value or a very old date can indicate inactivity.

SQL Query Example:

SELECT
    USERNAME,
    ACCOUNT_STATUS,
    CREATED,
    EXPIRY_DATE,
    LOCK_DATE,
    LAST_LOGIN
FROM
    DBA_USERS
WHERE
    ACCOUNT_STATUS NOT IN ('OPEN', 'EXPIRED(GRACE)') -- Accounts that are locked or fully expired
    OR (LAST_LOGIN IS NULL AND ACCOUNT_STATUS = 'OPEN') -- Open accounts that have never logged in (subject to LAST_LOGIN limitations)
    OR (LAST_LOGIN < SYSDATE - INTERVAL '90' DAY AND ACCOUNT_STATUS = 'OPEN'); -- Open accounts inactive for more than 90 days

Explanation of Query Conditions:

• ACCOUNT_STATUS NOT IN ('OPEN', 'EXPIRED(GRACE)'): This identifies users whose accounts are explicitly locked or have fully expired (beyond any grace period), indicating they cannot log in.
• (LAST_LOGIN IS NULL AND ACCOUNT_STATUS = 'OPEN'): This clause helps find open accounts that have never recorded a login. It's important to remember the LAST_LOGIN column's availability and configuration requirements.
• (LAST_LOGIN < SYSDATE - INTERVAL '90' DAY AND ACCOUNT_STATUS = 'OPEN'): This condition helps identify open accounts that have not been logged into for a specified period (e.g., 90 days). Adjust the INTERVAL as per your organization's definition of inactivity.

2.2 Leveraging Audit Trails for Login Activity

For a more robust and historical view of user activity, especially for database versions prior to 12cR2 or when the LAST_LOGIN column isn't reliable, you must enable and utilize database auditing.

• DBA_AUDIT_SESSION: This view stores records of successful and unsuccessful login/logout attempts if traditional session auditing is enabled (e.g., using AUDIT SESSION statement).
• UNIFIED_AUDIT_TRAIL: (From Oracle Database 12c onwards) This view consolidates all audit records, offering a more comprehensive auditing solution.

Steps for Using Audit Trails:

1. Ensure Auditing is Enabled: For DBA_AUDIT_SESSION, execute AUDIT SESSION; and ensure the AUDIT_TRAIL initialization parameter is set (e.g., DB or DB,EXTENDED). For UNIFIED_AUDIT_TRAIL, unified auditing must be enabled.

2. Query Audit Data:

   -- Example for DBA_AUDIT_SESSION (for older versions or traditional auditing)
   SELECT
       USERNAME,
       MAX(TIMESTAMP) AS LAST_ACTIVITY_DATE
   FROM
       DBA_AUDIT_SESSION
   GROUP BY
       USERNAME
   HAVING
       MAX(TIMESTAMP) < SYSDATE - INTERVAL '90' DAY; -- Finds users with no recorded login activity for 90+ days

   -- Example for UNIFIED_AUDIT_TRAIL (for Oracle 12cR1 and later with unified auditing)
   SELECT
       USER_NAME,
       MAX(EVENT_TIMESTAMP) AS LAST_ACTIVITY_DATE
   FROM
       UNIFIED_AUDIT_TRAIL
   WHERE
       ACTION_NAME = 'LOGON'
   GROUP BY
       USER_NAME
   HAVING
       MAX(EVENT_TIMESTAMP) < SYSDATE - INTERVAL '90' DAY; -- Finds users with no recorded logon event for 90+ days

   Note: Auditing generates a significant amount of data, requiring proper management and retention policies.

2.3 Checking Password Last Changed Date

While not a direct indicator of login, an OPEN account whose password hasn't been changed for an extended period can signal a dormant or forgotten account that should be reviewed.

SQL Query Example:

SELECT
    USERNAME,
    PASSWORD_CHANGE_DATE
FROM
    DBA_USERS
WHERE
    ACCOUNT_STATUS = 'OPEN'
    AND PASSWORD_CHANGE_DATE < SYSDATE - INTERVAL '365' DAY; -- Finds open accounts with passwords unchanged for over a year

Table: Key DBA_USERS Columns for Inactivity Checks

Column Name	Description	Relevance to Inactivity
USERNAME	The name of the database user.	Essential for identifying the user.
ACCOUNT_STATUS	Current status of the account (e.g., OPEN, LOCKED, EXPIRED).	Direct indicator: LOCKED or EXPIRED accounts are inactive.
CREATED	Date when the user account was created.	Helps identify old, potentially unused accounts.
EXPIRY_DATE	Date when the user's password is set to expire.	An expired password often precedes or indicates inactivity if not reset.
LOCK_DATE	Date when the account was last locked.	Indicates a previously or currently locked, thus inactive, account.
LAST_LOGIN	Date/time of the last successful login (12cR2+ with AUDIT_TRAIL=DB/OS).	Direct indicator of recent activity. NULL or an old date suggests inactivity.
PASSWORD_CHANGE_DATE	Date when the user's password was last changed.	An old date for an OPEN account can suggest dormancy or a forgotten account.

Why Find Inactive Users?

Identifying and managing inactive users is a critical aspect of database and application administration for several reasons:

• Enhanced Security: Unused accounts present a significant security risk, serving as potential targets for unauthorized access.
• Compliance Requirements: Many regulatory frameworks (e.g., GDPR, SOX, HIPAA) mandate regular review and remediation of user access to maintain compliance.
• Resource Optimization: Cleaning up inactive accounts helps manage database resources and licensing more efficiently.
• Improved Audit Readiness: Demonstrates a proactive security posture and simplifies audit processes by ensuring user lists are current.

Best Practices for Managing Inactive Users

• Define Inactivity Policy: Establish clear organizational policies that define what constitutes "inactive" (e.g., no login for 60 or 90 days).
• Automate Reporting: Schedule regular reports to automatically identify accounts that meet your inactivity criteria.
• Review and Action: Periodically review identified inactive accounts and take appropriate action:
  • Disable/Lock: Temporarily disable accounts for users on extended leave or until their status is clarified.
  • Archive/Delete: Permanently remove accounts for users who have left the organization or if the account is no longer needed.
  • Communicate: If appropriate, notify users before taking action on their accounts.
• Implement Auditing: Ensure robust auditing is enabled and configured to capture all necessary login and activity records for comprehensive tracking.
• Regular Audits: Conduct regular security audits of user accounts and their associated permissions to maintain a secure environment.

By combining application-level console checks with detailed database queries and adhering to these best practices, you can effectively identify and manage inactive users across your Oracle environments.