Automate Oracle APEX Account Unlocking

In the world of Oracle Application Express (APEX) development, maintaining smooth operations is crucial. One common challenge faced by APEX administrators is dealing with locked service accounts. Today, we'll explore an elegant solution that automates the process of unlocking these accounts, ensuring uninterrupted service and reducing manual intervention. Importantly, we'll also discuss why this solution avoids creating objects directly under the SYS schema, adhering to best practices for Oracle database security.

The Problem

APEX service accounts can become locked due to various reasons, such as failed login attempts or security policies. When this happens, it can disrupt operations and require immediate attention from database administrators. Manually unlocking these accounts can be time-consuming and prone to oversight, especially in large-scale environments.

The Solution

We've developed a PL/SQL procedure that automatically identifies and unlocks APEX service accounts. This solution is then scheduled to run at regular intervals, providing a hands-off approach to maintaining account accessibility. Crucially, our approach creates a dedicated schema for this operation, avoiding the common pitfall of creating objects under the SYS schema.

Let's break down the solution step by step:

1. Creating a Dedicated User

CREATE USER apex_ops_admin NO AUTHENTICATION;
GRANT READ ON dba_users TO apex_ops_admin;

We start by creating a dedicated user apex_ops_admin with no authentication. This user is granted read access to the dba_users view, allowing it to query user account information.

Important Note: Creating a separate user for this operation, rather than using the SYS schema, is a critical security best practice. The SYS schema is reserved for core database objects and should not be used for custom applications or procedures. By using a dedicated schema:

We maintain a clear separation of concerns.
We reduce the risk of accidentally modifying or dropping critical system objects.
We can more easily manage and audit the privileges granted for this specific task.

2. The Unlocking Procedure

CREATE OR REPLACE PROCEDURE apex_ops_admin.unlock_service_accounts
    AUTHID current_user
IS
BEGIN
    FOR u IN (
        SELECT username
        FROM dba_users
        WHERE profile = 'APEX_SYSTEM_USER'
          AND account_status = 'LOCKED'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER USER ' || u.username || ' ACCOUNT UNLOCK';
    END LOOP;
END;

This procedure does the heavy lifting. It identifies locked APEX service accounts by querying the dba_users view and unlocks them using dynamic SQL. Note that this procedure is created under the apex_ops_admin schema, not SYS.

3. Granting Necessary Privileges

GRANT INHERIT PRIVILEGES ON USER sys TO apex_ops_admin;

This grant allows apex_ops_admin to inherit privileges from SYS, ensuring it has the necessary permissions to unlock accounts when executed by SYS. This approach is more secure than creating the procedure directly under SYS, as it allows for more granular control over the privileges.

4. Scheduling the Job

BEGIN
    dbms_scheduler.create_job(
        job_name => 'APEX$_UNLOCK_SERVICE_ACCOUNTS',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN apex_ops_admin.unlock_service_accounts; END;',
        start_date => systimestamp,
        repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
        enabled => TRUE
    );
END;

Finally, we create a scheduler job under SYS that runs our procedure every 15 minutes. This ensures that locked accounts are promptly unlocked without manual intervention.

Benefits and Considerations

This solution offers several advantages:

Automation: Reduces the need for manual checks and interventions.
Timeliness: Ensures that locked accounts are quickly unlocked, minimizing downtime.
Scalability: Works efficiently regardless of the number of APEX service accounts.
Security: By avoiding the creation of objects under the SYS schema, we adhere to Oracle security best practices.

However, it's important to note that while this solution addresses the symptom (locked accounts), it doesn't address the root cause of why accounts are being locked. It's crucial to monitor and investigate the reasons behind frequent account lockouts to enhance overall system security and stability.

Conclusion

By implementing this automated solution, APEX administrators can significantly reduce the operational overhead associated with managing service account lockouts. This not only improves system reliability but also frees up valuable time for more strategic tasks.

Remember, while automation is powerful, it should be implemented thoughtfully. Always ensure that your automation scripts align with your organization's security policies and best practices. In this case, by creating a dedicated schema for our automation task, we've demonstrated how to balance efficiency with security, avoiding the pitfalls of working directly within the SYS schema.

DevKata Dojo