ORA-28000 Account Locked - User Account Management and Security

ORA-28000: Account is Locked

Error Overview

Error Text: ORA-28000: the account is locked

This error occurs when attempting to connect with a user account that has been locked by Oracle’s security mechanisms. Account locking is typically triggered by failed login attempts, administrative action, or password policy violations. Understanding Oracle’s account security features is essential for proper user management.

Understanding Oracle Account Locking

Account Lock Triggers

Oracle Account Locking Causes
├── Failed Login Attempts - Exceeding FAILED_LOGIN_ATTEMPTS limit
├── Administrative Lock - Manual account locking by DBA
├── Password Expiration - Account locked after grace period expires
├── Resource Limits - Profile resource limit violations
└── Security Policies - Custom security policy triggers

Account Status Types

• OPEN - Account is active and accessible
• LOCKED - Account locked due to failed logins or admin action
• EXPIRED - Password has expired but account not locked
• EXPIRED & LOCKED - Both password expired and account locked
• LOCKED(TIMED) - Automatically locked, will unlock after specified time

Diagnostic Steps

1. Check Account Status and Lock Details

-- Check specific user account status
SELECT
  username,
  account_status,
  lock_date,
  expiry_date,
  profile,
  default_tablespace,
  created,
  authentication_type
FROM dba_users
WHERE username = UPPER('locked_username');  -- Replace with actual username

-- Check all locked accounts
SELECT
  username,
  account_status,
  lock_date,
  expiry_date,
  ROUND(SYSDATE - lock_date, 2) as days_locked,
  profile
FROM dba_users
WHERE account_status LIKE '%LOCKED%'
ORDER BY lock_date DESC;

-- Check user's profile settings that control locking
SELECT
  u.username,
  u.profile,
  p.resource_name,
  p.limit,
  CASE p.resource_name
    WHEN 'FAILED_LOGIN_ATTEMPTS' THEN 'Number of failed attempts before lock'
    WHEN 'PASSWORD_LOCK_TIME' THEN 'Days account stays locked'
    WHEN 'PASSWORD_LIFE_TIME' THEN 'Days before password expires'
    WHEN 'PASSWORD_GRACE_TIME' THEN 'Days of grace period after expiration'
    ELSE p.resource_name
  END as description
FROM dba_users u, dba_profiles p
WHERE u.username = UPPER('locked_username')
  AND u.profile = p.profile
  AND p.resource_name IN (
    'FAILED_LOGIN_ATTEMPTS',
    'PASSWORD_LOCK_TIME',
    'PASSWORD_LIFE_TIME',
    'PASSWORD_GRACE_TIME',
    'PASSWORD_REUSE_TIME',
    'PASSWORD_REUSE_MAX'
  )
ORDER BY p.resource_name;

2. Analyze Failed Login Attempts

-- Check failed login audit trail
SELECT
  username,
  userhost,
  terminal,
  timestamp,
  returncode,
  CASE returncode
    WHEN 1017 THEN 'Invalid username/password'
    WHEN 28000 THEN 'Account locked'
    WHEN 28001 THEN 'Password expired'
    WHEN 28003 THEN 'Password verification failed'
    ELSE 'Other error: ' || returncode
  END as error_description,
  comment_text
FROM dba_audit_trail
WHERE username = UPPER('locked_username')
  AND action_name = 'LOGON'
  AND returncode != 0
  AND timestamp > SYSDATE - 7  -- Last week
ORDER BY timestamp DESC;

-- Count failed login attempts by source
SELECT
  userhost,
  terminal,
  COUNT(*) as failed_attempts,
  MIN(timestamp) as first_failure,
  MAX(timestamp) as last_failure,
  COUNT(DISTINCT TO_CHAR(timestamp, 'YYYY-MM-DD')) as failure_days
FROM dba_audit_trail
WHERE username = UPPER('locked_username')
  AND action_name = 'LOGON'
  AND returncode IN (1017, 28000, 28001)
  AND timestamp > SYSDATE - 30
GROUP BY userhost, terminal
ORDER BY failed_attempts DESC;

-- Check for brute force attack patterns
SELECT
  TO_CHAR(timestamp, 'YYYY-MM-DD HH24') as hour,
  userhost,
  COUNT(*) as attempts,
  COUNT(DISTINCT username) as different_users,
  LISTAGG(DISTINCT returncode, ',') WITHIN GROUP (ORDER BY returncode) as error_codes
FROM dba_audit_trail
WHERE action_name = 'LOGON'
  AND returncode != 0
  AND timestamp > SYSDATE - 1  -- Last 24 hours
  AND (userhost = (SELECT userhost FROM dba_audit_trail WHERE username = UPPER('locked_username') AND ROWNUM = 1)
       OR username = UPPER('locked_username'))
GROUP BY TO_CHAR(timestamp, 'YYYY-MM-DD HH24'), userhost
HAVING COUNT(*) > 5  -- More than 5 failures per hour
ORDER BY hour DESC, attempts DESC;

3. Check Current Login Sessions and Activity

-- Check if user has any active sessions (shouldn't if locked)
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.program,
  s.machine,
  s.osuser,
  s.logon_time,
  s.last_call_et as seconds_since_last_activity
FROM v$session s
WHERE s.username = UPPER('locked_username')
ORDER BY s.logon_time DESC;

-- Check recent successful logins before lock
SELECT
  username,
  userhost,
  terminal,
  timestamp,
  os_username,
  action_name
FROM dba_audit_trail
WHERE username = UPPER('locked_username')
  AND action_name = 'LOGON'
  AND returncode = 0  -- Successful logins
  AND timestamp > SYSDATE - 30
ORDER BY timestamp DESC;

-- Check what the user was doing before account got locked
SELECT
  username,
  action_name,
  obj_name,
  timestamp,
  userhost,
  returncode,
  CASE
    WHEN returncode = 0 THEN 'SUCCESS'
    ELSE 'FAILED: ' || returncode
  END as result
FROM dba_audit_trail
WHERE username = UPPER('locked_username')
  AND timestamp > (
    SELECT NVL(lock_date, SYSDATE - 1) - 1/24  -- 1 hour before lock
    FROM dba_users
    WHERE username = UPPER('locked_username')
  )
ORDER BY timestamp DESC;

4. Investigate Security Policies and Configuration

-- Check if account locking is due to resource limits
SELECT
  s.username,
  s.resource_name,
  s.limit,
  s.current_utilization,
  s.max_utilization,
  CASE
    WHEN s.current_utilization >= TO_NUMBER(s.limit) THEN 'LIMIT_EXCEEDED'
    ELSE 'WITHIN_LIMIT'
  END as status
FROM dba_profiles p, dba_resource_cost rc, v$session_longops s
WHERE s.username = UPPER('locked_username')
  AND p.profile = (SELECT profile FROM dba_users WHERE username = s.username)
  AND p.resource_name = rc.resource_name
ORDER BY s.resource_name;

-- Check password verification function (if any)
SELECT
  p.profile,
  p.resource_name,
  p.limit as verification_function
FROM dba_profiles p, dba_users u
WHERE u.username = UPPER('locked_username')
  AND u.profile = p.profile
  AND p.resource_name = 'PASSWORD_VERIFY_FUNCTION'
  AND p.limit != 'NULL';

-- Check for custom security policies
SELECT
  object_schema,
  object_name,
  policy_name,
  function_schema,
  function,
  policy_type,
  enabled
FROM dba_policies
WHERE object_schema = (SELECT default_tablespace FROM dba_users WHERE username = UPPER('locked_username'))
   OR policy_name LIKE '%SECURITY%'
   OR policy_name LIKE '%LOGIN%';

-- Check system parameters related to security
SELECT
  name,
  value,
  description
FROM v$parameter
WHERE name IN (
  'audit_trail',
  'remote_login_passwordfile',
  'sec_case_sensitive_logon',
  'sec_max_failed_login_attempts',
  'sec_return_server_release_banner'
)
ORDER BY name;

Immediate Solutions

Solution 1: Unlock User Account

Basic Account Unlock

-- Check current account status first
SELECT username, account_status, lock_date
FROM dba_users
WHERE username = UPPER('locked_username');

-- Unlock the account (requires DBA privileges)
ALTER USER locked_username ACCOUNT UNLOCK;

-- Verify account is unlocked
SELECT username, account_status, lock_date
FROM dba_users
WHERE username = UPPER('locked_username');

-- Test connection capability
-- sqlplus locked_username/password@database_service

-- If password is also expired, reset it during unlock
ALTER USER locked_username IDENTIFIED BY "NewPassword123" ACCOUNT UNLOCK;

-- Check connection after unlock
SELECT
  'Account Status: ' || account_status as status,
  'Lock Date: ' || NVL(TO_CHAR(lock_date, 'YYYY-MM-DD HH24:MI:SS'), 'Not locked') as lock_info,
  'Expiry Date: ' || NVL(TO_CHAR(expiry_date, 'YYYY-MM-DD'), 'No expiry') as expiry_info
FROM dba_users
WHERE username = UPPER('locked_username');

Advanced Unlock with Security Validation

-- Comprehensive unlock procedure with logging
CREATE OR REPLACE PROCEDURE unlock_user_account(
  p_username VARCHAR2,
  p_new_password VARCHAR2 DEFAULT NULL,
  p_reason VARCHAR2 DEFAULT 'Administrative unlock'
) AS
  v_current_status VARCHAR2(32);
  v_lock_date DATE;
  v_profile VARCHAR2(30);
  v_failed_attempts NUMBER;
BEGIN
  -- Get current account information
  SELECT account_status, lock_date, profile
  INTO v_current_status, v_lock_date, v_profile
  FROM dba_users
  WHERE username = UPPER(p_username);

  -- Log the unlock attempt
  INSERT INTO user_unlock_log (
    username, unlock_date, unlocked_by, previous_status,
    lock_date, reason, new_password_set
  ) VALUES (
    UPPER(p_username), SYSDATE, USER, v_current_status,
    v_lock_date, p_reason, CASE WHEN p_new_password IS NOT NULL THEN 'Y' ELSE 'N' END
  );

  -- Check if unlock is appropriate
  IF v_current_status NOT LIKE '%LOCKED%' THEN
    DBMS_OUTPUT.PUT_LINE('Warning: Account ' || p_username || ' is not locked (status: ' || v_current_status || ')');
    RETURN;
  END IF;

  -- Count recent failed attempts
  SELECT COUNT(*)
  INTO v_failed_attempts
  FROM dba_audit_trail
  WHERE username = UPPER(p_username)
    AND action_name = 'LOGON'
    AND returncode IN (1017, 28000)
    AND timestamp > SYSDATE - 1;  -- Last 24 hours

  IF v_failed_attempts > 50 THEN
    DBMS_OUTPUT.PUT_LINE('Warning: ' || v_failed_attempts || ' failed login attempts in last 24 hours');
    DBMS_OUTPUT.PUT_LINE('Consider investigating for security breach before unlocking');
  END IF;

  -- Perform unlock
  IF p_new_password IS NOT NULL THEN
    EXECUTE IMMEDIATE 'ALTER USER ' || UPPER(p_username) || ' IDENTIFIED BY "' || p_new_password || '" ACCOUNT UNLOCK';
    DBMS_OUTPUT.PUT_LINE('Account ' || p_username || ' unlocked with new password');
  ELSE
    EXECUTE IMMEDIATE 'ALTER USER ' || UPPER(p_username) || ' ACCOUNT UNLOCK';
    DBMS_OUTPUT.PUT_LINE('Account ' || p_username || ' unlocked');
  END IF;

  -- Update log with success
  UPDATE user_unlock_log
  SET unlock_successful = 'Y', completion_time = SYSDATE
  WHERE username = UPPER(p_username)
    AND unlock_date = (SELECT MAX(unlock_date) FROM user_unlock_log WHERE username = UPPER(p_username));

  COMMIT;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'User ' || p_username || ' not found');
  WHEN OTHERS THEN
    ROLLBACK;
    -- Update log with failure
    UPDATE user_unlock_log
    SET unlock_successful = 'N', error_message = SQLERRM, completion_time = SYSDATE
    WHERE username = UPPER(p_username)
      AND unlock_date = (SELECT MAX(unlock_date) FROM user_unlock_log WHERE username = UPPER(p_username));
    COMMIT;
    RAISE;
END;
/

-- Create unlock log table
CREATE TABLE user_unlock_log (
  log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username VARCHAR2(30),
  unlock_date TIMESTAMP,
  unlocked_by VARCHAR2(30),
  previous_status VARCHAR2(32),
  lock_date DATE,
  reason VARCHAR2(200),
  new_password_set CHAR(1),
  unlock_successful CHAR(1) DEFAULT 'N',
  completion_time TIMESTAMP,
  error_message VARCHAR2(1000)
);

Solution 2: Modify Password Policy Settings

Adjust Profile Settings to Prevent Frequent Locks

-- Check current profile settings
SELECT
  profile,
  resource_name,
  limit,
  CASE resource_name
    WHEN 'FAILED_LOGIN_ATTEMPTS' THEN
      CASE
        WHEN limit = 'UNLIMITED' THEN 'No limit on failed attempts'
        WHEN TO_NUMBER(limit) < 5 THEN 'Very restrictive - consider increasing'
        WHEN TO_NUMBER(limit) < 10 THEN 'Moderately restrictive'
        ELSE 'Reasonable setting'
      END
    WHEN 'PASSWORD_LOCK_TIME' THEN
      CASE
        WHEN limit = 'UNLIMITED' THEN 'Account locked permanently until manual unlock'
        WHEN TO_NUMBER(limit) < 1/24 THEN 'Very short lock time - less than 1 hour'
        WHEN TO_NUMBER(limit) = 1 THEN 'Locked for 1 day'
        ELSE limit || ' days lock time'
      END
    ELSE 'Other setting'
  END as interpretation
FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = UPPER('locked_username'))
  AND resource_name IN ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME', 'PASSWORD_LIFE_TIME')
ORDER BY resource_name;

-- Create more lenient profile for problematic accounts
CREATE PROFILE lenient_login_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 10        -- Allow more attempts
  PASSWORD_LOCK_TIME 1/24          -- Lock for 1 hour instead of permanently
  PASSWORD_LIFE_TIME 180           -- 6 months password life
  PASSWORD_GRACE_TIME 10           -- 10-day grace period
  PASSWORD_REUSE_TIME 365          -- Can reuse password after 1 year
  PASSWORD_REUSE_MAX 5;            -- Must use 5 different passwords before reuse

-- Apply lenient profile to user
ALTER USER locked_username PROFILE lenient_login_profile;

-- Temporarily disable login failure tracking (emergency use only)
CREATE PROFILE emergency_profile LIMIT
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED;

-- Apply emergency profile and unlock
ALTER USER locked_username PROFILE emergency_profile ACCOUNT UNLOCK;

-- Remember to change back to secure profile later
-- ALTER USER locked_username PROFILE default_profile;

Solution 3: Reset Password and Security Settings

Complete Password and Security Reset

-- Reset password and unlock account
ALTER USER locked_username IDENTIFIED BY "NewSecurePassword123" ACCOUNT UNLOCK;

-- If using password verification function, temporarily disable for reset
ALTER PROFILE user_profile LIMIT PASSWORD_VERIFY_FUNCTION NULL;
ALTER USER locked_username IDENTIFIED BY "TempPassword123" ACCOUNT UNLOCK;
-- Re-enable verification function
ALTER PROFILE user_profile LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11g;

-- Force user to change password on next login
ALTER USER locked_username PASSWORD EXPIRE;

-- Verify account status after reset
SELECT
  username,
  account_status,
  lock_date,
  expiry_date,
  profile
FROM dba_users
WHERE username = UPPER('locked_username');

-- Create procedure for secure password reset with notification
CREATE OR REPLACE PROCEDURE reset_locked_account(
  p_username VARCHAR2,
  p_temp_password VARCHAR2,
  p_notify_user BOOLEAN DEFAULT TRUE
) AS
  v_old_status VARCHAR2(32);
  v_user_email VARCHAR2(255);
BEGIN
  -- Get current status
  SELECT account_status INTO v_old_status
  FROM dba_users WHERE username = UPPER(p_username);

  -- Reset password and unlock
  EXECUTE IMMEDIATE 'ALTER USER ' || UPPER(p_username) ||
                   ' IDENTIFIED BY "' || p_temp_password || '" ACCOUNT UNLOCK';

  -- Force password change on next login
  EXECUTE IMMEDIATE 'ALTER USER ' || UPPER(p_username) || ' PASSWORD EXPIRE';

  -- Log the reset
  INSERT INTO password_reset_log (
    username, reset_date, reset_by, old_status,
    temp_password_set, password_expired
  ) VALUES (
    UPPER(p_username), SYSDATE, USER, v_old_status, 'Y', 'Y'
  );

  DBMS_OUTPUT.PUT_LINE('Account ' || p_username || ' has been reset');
  DBMS_OUTPUT.PUT_LINE('Previous status: ' || v_old_status);
  DBMS_OUTPUT.PUT_LINE('Current status: Account unlocked, password expired');
  DBMS_OUTPUT.PUT_LINE('User must change password on next login');

  -- Optional: Send notification (implement email logic here)
  IF p_notify_user THEN
    -- This would integrate with your notification system
    DBMS_OUTPUT.PUT_LINE('Notification should be sent to user about password reset');
  END IF;

  COMMIT;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'User ' || p_username || ' not found');
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20002, 'Error resetting account: ' || SQLERRM);
END;
/

Long-Term Solutions

1. Implement Comprehensive Account Security Monitoring

Account Security Monitoring System

-- Create comprehensive security monitoring tables
CREATE TABLE account_security_events (
  event_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username VARCHAR2(30),
  event_type VARCHAR2(50), -- LOCK, UNLOCK, FAILED_LOGIN, PASSWORD_CHANGE, etc.
  event_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  source_ip VARCHAR2(45),
  user_agent VARCHAR2(500),
  success CHAR(1),
  failure_reason VARCHAR2(200),
  additional_info CLOB
);

CREATE TABLE account_risk_scores (
  username VARCHAR2(30) PRIMARY KEY,
  risk_score NUMBER(3,0) DEFAULT 0, -- 0-100 risk score
  failed_login_count_24h NUMBER DEFAULT 0,
  failed_login_count_7d NUMBER DEFAULT 0,
  last_successful_login TIMESTAMP,
  last_failed_login TIMESTAMP,
  suspicious_activity_count NUMBER DEFAULT 0,
  account_status VARCHAR2(32),
  last_updated TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Comprehensive security monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_account_security AS
  CURSOR high_risk_accounts IS
    SELECT
      u.username,
      u.account_status,
      u.lock_date,
      ars.risk_score,
      ars.failed_login_count_24h,
      ars.failed_login_count_7d
    FROM dba_users u
    LEFT JOIN account_risk_scores ars ON u.username = ars.username
    WHERE u.account_status LIKE '%LOCKED%'
       OR ars.risk_score > 70
       OR ars.failed_login_count_24h > 10
    ORDER BY ars.risk_score DESC NULLS LAST;

  v_failed_24h NUMBER;
  v_failed_7d NUMBER;
  v_risk_score NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== Account Security Monitoring Report ===');
  DBMS_OUTPUT.PUT_LINE('Generated: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
  DBMS_OUTPUT.PUT_LINE('');

  -- Update risk scores for all users
  FOR user_rec IN (SELECT username FROM dba_users WHERE account_status != 'LOCKED') LOOP
    -- Calculate failed login attempts
    SELECT COUNT(*) INTO v_failed_24h
    FROM dba_audit_trail
    WHERE username = user_rec.username
      AND action_name = 'LOGON'
      AND returncode != 0
      AND timestamp > SYSDATE - 1;

    SELECT COUNT(*) INTO v_failed_7d
    FROM dba_audit_trail
    WHERE username = user_rec.username
      AND action_name = 'LOGON'
      AND returncode != 0
      AND timestamp > SYSDATE - 7;

    -- Calculate risk score
    v_risk_score := LEAST(100,
      (v_failed_24h * 10) +     -- 10 points per failed login in 24h
      (v_failed_7d * 2) +       -- 2 points per failed login in 7d
      CASE
        WHEN v_failed_24h > 5 THEN 20  -- Bonus for multiple recent failures
        ELSE 0
      END
    );

    -- Update or insert risk score
    MERGE INTO account_risk_scores ars
    USING (SELECT user_rec.username as username FROM dual) u
    ON (ars.username = u.username)
    WHEN MATCHED THEN
      UPDATE SET
        risk_score = v_risk_score,
        failed_login_count_24h = v_failed_24h,
        failed_login_count_7d = v_failed_7d,
        last_updated = SYSTIMESTAMP
    WHEN NOT MATCHED THEN
      INSERT (username, risk_score, failed_login_count_24h, failed_login_count_7d)
      VALUES (user_rec.username, v_risk_score, v_failed_24h, v_failed_7d);
  END LOOP;

  -- Report on high-risk accounts
  FOR risk_rec IN high_risk_accounts LOOP
    DBMS_OUTPUT.PUT_LINE('HIGH RISK ACCOUNT: ' || risk_rec.username);
    DBMS_OUTPUT.PUT_LINE('  Status: ' || risk_rec.account_status);
    DBMS_OUTPUT.PUT_LINE('  Risk Score: ' || NVL(risk_rec.risk_score, 0));
    DBMS_OUTPUT.PUT_LINE('  Failed Logins (24h): ' || NVL(risk_rec.failed_login_count_24h, 0));
    DBMS_OUTPUT.PUT_LINE('  Failed Logins (7d): ' || NVL(risk_rec.failed_login_count_7d, 0));

    IF risk_rec.lock_date IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('  Locked Since: ' || TO_CHAR(risk_rec.lock_date, 'YYYY-MM-DD HH24:MI:SS'));
    END IF;

    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;

  COMMIT;
END;
/

-- Schedule security monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'ACCOUNT_SECURITY_MONITOR',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_account_security',
    repeat_interval => 'FREQ=HOURLY',  -- Run every hour
    enabled => TRUE
  );
END;
/

2. Implement Smart Account Unlock Policies

Automated Account Management

-- Create smart unlock policy system
CREATE TABLE auto_unlock_policies (
  policy_id NUMBER PRIMARY KEY,
  policy_name VARCHAR2(100),
  user_pattern VARCHAR2(100), -- Username pattern or specific user
  max_auto_unlocks_per_day NUMBER DEFAULT 3,
  unlock_after_minutes NUMBER DEFAULT 60,
  require_password_reset CHAR(1) DEFAULT 'N',
  notify_admin CHAR(1) DEFAULT 'Y',
  active CHAR(1) DEFAULT 'Y'
);

-- Insert sample policies
INSERT INTO auto_unlock_policies VALUES (1, 'Standard Users', '%', 3, 60, 'N', 'Y', 'Y');
INSERT INTO auto_unlock_policies VALUES (2, 'Service Accounts', 'SVC_%', 5, 30, 'N', 'Y', 'Y');
INSERT INTO auto_unlock_policies VALUES (3, 'Test Users', 'TEST_%', 10, 15, 'N', 'N', 'Y');

-- Smart unlock procedure
CREATE OR REPLACE PROCEDURE smart_unlock_accounts AS
  CURSOR locked_accounts IS
    SELECT
      u.username,
      u.account_status,
      u.lock_date,
      u.profile,
      ROUND((SYSDATE - u.lock_date) * 24 * 60) as minutes_locked
    FROM dba_users u
    WHERE u.account_status = 'LOCKED'
      AND u.lock_date IS NOT NULL
    ORDER BY u.lock_date;

  v_policy_found BOOLEAN;
  v_can_unlock BOOLEAN;
  v_unlock_count NUMBER;
  v_policy auto_unlock_policies%ROWTYPE;
BEGIN
  FOR locked_rec IN locked_accounts LOOP
    v_policy_found := FALSE;
    v_can_unlock := FALSE;

    -- Find applicable policy
    FOR policy_rec IN (
      SELECT * FROM auto_unlock_policies
      WHERE active = 'Y'
        AND (user_pattern = '%' OR locked_rec.username LIKE user_pattern)
      ORDER BY CASE WHEN user_pattern = locked_rec.username THEN 1 ELSE 2 END
    ) LOOP
      v_policy := policy_rec;
      v_policy_found := TRUE;
      EXIT; -- Use first matching policy
    END LOOP;

    IF v_policy_found THEN
      -- Check if enough time has passed
      IF locked_rec.minutes_locked >= v_policy.unlock_after_minutes THEN
        -- Check daily unlock limit
        SELECT COUNT(*)
        INTO v_unlock_count
        FROM user_unlock_log
        WHERE username = locked_rec.username
          AND unlock_date >= TRUNC(SYSDATE)
          AND unlock_successful = 'Y';

        IF v_unlock_count < v_policy.max_auto_unlocks_per_day THEN
          v_can_unlock := TRUE;
        END IF;
      END IF;

      IF v_can_unlock THEN
        BEGIN
          -- Perform unlock
          IF v_policy.require_password_reset = 'Y' THEN
            EXECUTE IMMEDIATE 'ALTER USER ' || locked_rec.username || ' ACCOUNT UNLOCK PASSWORD EXPIRE';
          ELSE
            EXECUTE IMMEDIATE 'ALTER USER ' || locked_rec.username || ' ACCOUNT UNLOCK';
          END IF;

          -- Log the unlock
          INSERT INTO user_unlock_log (
            username, unlock_date, unlocked_by, previous_status,
            lock_date, reason, unlock_successful
          ) VALUES (
            locked_rec.username, SYSDATE, 'AUTO_UNLOCK_SYSTEM',
            locked_rec.account_status, locked_rec.lock_date,
            'Automatic unlock after ' || locked_rec.minutes_locked || ' minutes', 'Y'
          );

          DBMS_OUTPUT.PUT_LINE('Auto-unlocked: ' || locked_rec.username ||
                              ' (locked for ' || locked_rec.minutes_locked || ' minutes)');

          -- Notify admin if required
          IF v_policy.notify_admin = 'Y' THEN
            -- This would integrate with your notification system
            DBMS_OUTPUT.PUT_LINE('Admin notification sent for: ' || locked_rec.username);
          END IF;

        EXCEPTION
          WHEN OTHERS THEN
            -- Log failure
            INSERT INTO user_unlock_log (
              username, unlock_date, unlocked_by, previous_status,
              lock_date, reason, unlock_successful, error_message
            ) VALUES (
              locked_rec.username, SYSDATE, 'AUTO_UNLOCK_SYSTEM',
              locked_rec.account_status, locked_rec.lock_date,
              'Auto unlock attempt failed', 'N', SQLERRM
            );

            DBMS_OUTPUT.PUT_LINE('Failed to unlock: ' || locked_rec.username || ' - ' || SQLERRM);
        END;
      ELSE
        DBMS_OUTPUT.PUT_LINE('Cannot unlock ' || locked_rec.username || ': ' ||
          CASE
            WHEN locked_rec.minutes_locked < v_policy.unlock_after_minutes THEN 'Not enough time passed'
            WHEN v_unlock_count >= v_policy.max_auto_unlocks_per_day THEN 'Daily unlock limit reached'
            ELSE 'Unknown reason'
          END);
      END IF;
    ELSE
      DBMS_OUTPUT.PUT_LINE('No unlock policy found for: ' || locked_rec.username);
    END IF;
  END LOOP;

  COMMIT;
END;
/

3. Application Integration and User Experience

Application-Level Account Lock Handling

// Java example with sophisticated account lock handling
@Service
public class AccountLockService {

    private final UserRepository userRepository;
    private final AuditService auditService;
    private final NotificationService notificationService;

    public AuthenticationResult authenticate(String username, String password) {
        try {
            // Attempt authentication
            Connection connection = getConnection(username, password);

            // Authentication successful
            auditService.logSuccessfulLogin(username);
            return AuthenticationResult.success(username);

        } catch (SQLException e) {
            return handleAuthenticationError(username, e);
        }
    }

    private AuthenticationResult handleAuthenticationError(String username, SQLException e) {
        switch (e.getErrorCode()) {
            case 28000: // ORA-28000: Account locked
                return handleAccountLocked(username, e);

            case 1017: // ORA-01017: Invalid username/password
                auditService.logFailedLogin(username, "Invalid credentials");
                return AuthenticationResult.failure("Invalid username or password");

            case 28001: // ORA-28001: Password expired
                return AuthenticationResult.passwordExpired(username);

            case 28003: // ORA-28003: Password verification failed
                auditService.logFailedLogin(username, "Password policy violation");
                return AuthenticationResult.failure("Password does not meet security requirements");

            default:
                auditService.logFailedLogin(username, "Database error: " + e.getErrorCode());
                return AuthenticationResult.failure("Authentication service unavailable");
        }
    }

    private AuthenticationResult handleAccountLocked(String username, SQLException e) {
        AccountLockInfo lockInfo = getAccountLockInfo(username);

        auditService.logFailedLogin(username, "Account locked");

        if (lockInfo.isTemporaryLock()) {
            long minutesUntilUnlock = lockInfo.getMinutesUntilAutoUnlock();

            if (minutesUntilUnlock > 0) {
                return AuthenticationResult.temporaryLock(
                    "Account is temporarily locked. Please try again in " +
                    minutesUntilUnlock + " minutes."
                );
            } else {
                // Try to trigger auto-unlock
                if (attemptAutoUnlock(username)) {
                    return AuthenticationResult.unlocked(
                        "Account has been unlocked. Please try logging in again."
                    );
                }
            }
        }

        // Permanent lock or auto-unlock failed
        notificationService.notifyAdminOfLockedAccount(username, lockInfo);

        return AuthenticationResult.permanentLock(
            "Account is locked. Please contact your administrator for assistance."
        );
    }

    private AccountLockInfo getAccountLockInfo(String username) {
        String query = """
            SELECT account_status, lock_date, profile,
                   CASE
                     WHEN p.limit = 'UNLIMITED' THEN -1
                     ELSE TO_NUMBER(p.limit) * 24 * 60
                   END as lock_duration_minutes
            FROM dba_users u, dba_profiles p
            WHERE u.username = ?
              AND u.profile = p.profile
              AND p.resource_name = 'PASSWORD_LOCK_TIME'
            """;

        try (PreparedStatement pstmt = adminConnection.prepareStatement(query)) {
            pstmt.setString(1, username.toUpperCase());

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return new AccountLockInfo(
                        rs.getString("account_status"),
                        rs.getTimestamp("lock_date"),
                        rs.getInt("lock_duration_minutes")
                    );
                }
            }
        } catch (SQLException e) {
            logger.error("Error getting account lock info for user: " + username, e);
        }

        return AccountLockInfo.unknown();
    }

    private boolean attemptAutoUnlock(String username) {
        try {
            String unlockSql = "ALTER USER " + username + " ACCOUNT UNLOCK";

            try (PreparedStatement pstmt = adminConnection.prepareStatement(unlockSql)) {
                pstmt.executeUpdate();

                auditService.logAccountUnlock(username, "Auto-unlock via application");
                logger.info("Successfully auto-unlocked account: " + username);

                return true;
            }
        } catch (SQLException e) {
            logger.error("Failed to auto-unlock account: " + username, e);
            return false;
        }
    }

    public void requestAccountUnlock(String username, String requestedBy, String reason) {
        UnlockRequest request = new UnlockRequest(username, requestedBy, reason);

        // Store unlock request
        unlockRequestRepository.save(request);

        // Notify administrators
        notificationService.notifyAdminOfUnlockRequest(request);

        auditService.logUnlockRequest(username, requestedBy, reason);
    }
}

4. Security Best Practices Implementation

Comprehensive Security Framework

-- Create security best practices framework
CREATE TABLE security_policies (
  policy_id NUMBER PRIMARY KEY,
  policy_name VARCHAR2(100),
  policy_type VARCHAR2(50), -- PASSWORD, LOGIN, ACCOUNT, etc.
  policy_description VARCHAR2(1000),
  implementation_sql VARCHAR2(4000),
  active CHAR(1) DEFAULT 'Y',
  created_date TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Insert security best practices
INSERT INTO security_policies VALUES (1, 'Strong Password Policy', 'PASSWORD',
  'Enforce strong passwords with complexity requirements',
  'CREATE PROFILE secure_profile LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11g PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 7',
  'Y', SYSTIMESTAMP);

INSERT INTO security_policies VALUES (2, 'Account Lock Policy', 'LOGIN',
  'Lock accounts after 5 failed attempts for 1 hour',
  'ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1/24',
  'Y', SYSTIMESTAMP);

-- Security compliance checker
CREATE OR REPLACE PROCEDURE check_security_compliance AS
  CURSOR weak_profiles IS
    SELECT profile, resource_name, limit
    FROM dba_profiles
    WHERE (resource_name = 'FAILED_LOGIN_ATTEMPTS' AND (limit = 'UNLIMITED' OR TO_NUMBER(limit) > 10))
       OR (resource_name = 'PASSWORD_LIFE_TIME' AND limit = 'UNLIMITED')
       OR (resource_name = 'PASSWORD_LOCK_TIME' AND limit = 'UNLIMITED');

  CURSOR users_with_weak_profiles IS
    SELECT u.username, u.profile, u.account_status
    FROM dba_users u, dba_profiles p
    WHERE u.profile = p.profile
      AND ((p.resource_name = 'FAILED_LOGIN_ATTEMPTS' AND (p.limit = 'UNLIMITED' OR TO_NUMBER(p.limit) > 10))
        OR (p.resource_name = 'PASSWORD_LIFE_TIME' AND p.limit = 'UNLIMITED'))
      AND u.username NOT IN ('SYS', 'SYSTEM', 'ANONYMOUS');

  v_compliance_score NUMBER := 100;
  v_issues_found NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== Security Compliance Report ===');
  DBMS_OUTPUT.PUT_LINE('Generated: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
  DBMS_OUTPUT.PUT_LINE('');

  -- Check for weak profiles
  DBMS_OUTPUT.PUT_LINE('Checking profile security settings...');
  FOR profile_rec IN weak_profiles LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: Profile ' || profile_rec.profile ||
                        ' has weak setting for ' || profile_rec.resource_name ||
                        ' = ' || profile_rec.limit);
    v_compliance_score := v_compliance_score - 10;
    v_issues_found := v_issues_found + 1;
  END LOOP;

  -- Check for users with weak profiles
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('Checking user profile assignments...');
  FOR user_rec IN users_with_weak_profiles LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: User ' || user_rec.username ||
                        ' assigned to potentially weak profile: ' || user_rec.profile);
    v_compliance_score := v_compliance_score - 5;
    v_issues_found := v_issues_found + 1;
  END LOOP;

  -- Check for accounts that should be locked
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('Checking for inactive accounts...');
  FOR inactive_rec IN (
    SELECT username, account_status,
           ROUND(SYSDATE - NVL(last_login_date, created)) as days_inactive
    FROM (
      SELECT u.username, u.account_status, u.created,
             MAX(a.timestamp) as last_login_date
      FROM dba_users u
      LEFT JOIN dba_audit_trail a ON u.username = a.username
        AND a.action_name = 'LOGON' AND a.returncode = 0
      WHERE u.username NOT IN ('SYS', 'SYSTEM', 'ANONYMOUS')
        AND u.account_status = 'OPEN'
      GROUP BY u.username, u.account_status, u.created
    )
    WHERE days_inactive > 90  -- Inactive for 90+ days
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: User ' || inactive_rec.username ||
                        ' inactive for ' || inactive_rec.days_inactive || ' days');
    v_compliance_score := v_compliance_score - 5;
    v_issues_found := v_issues_found + 1;
  END LOOP;

  -- Calculate final compliance score
  v_compliance_score := GREATEST(0, v_compliance_score);

  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('=== Compliance Summary ===');
  DBMS_OUTPUT.PUT_LINE('Issues Found: ' || v_issues_found);
  DBMS_OUTPUT.PUT_LINE('Compliance Score: ' || v_compliance_score || '/100');

  CASE
    WHEN v_compliance_score >= 90 THEN
      DBMS_OUTPUT.PUT_LINE('Status: EXCELLENT - Security configuration is strong');
    WHEN v_compliance_score >= 70 THEN
      DBMS_OUTPUT.PUT_LINE('Status: GOOD - Minor security improvements recommended');
    WHEN v_compliance_score >= 50 THEN
      DBMS_OUTPUT.PUT_LINE('Status: FAIR - Several security issues need attention');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Status: POOR - Immediate security improvements required');
  END CASE;

END;
/

Related Errors

• ORA-01017 - Invalid username/password; logon denied
• ORA-28001 - Password has expired
• ORA-28002 - Password will expire within grace period
• ORA-28003 - Password verification failed

Quick Reference

Emergency Response Steps

1. ✓ Verify account is actually locked and check lock reason
2. ✓ Review recent failed login attempts and sources
3. ✓ Unlock account using ALTER USER command
4. ✓ Reset password if needed or if security is compromised
5. ✓ Check and adjust profile settings if locks are too frequent
6. ✓ Monitor account for continued issues

Quick Commands

-- Check account status
SELECT username, account_status, lock_date FROM dba_users
WHERE username = UPPER('locked_username');

-- Unlock account
ALTER USER locked_username ACCOUNT UNLOCK;

-- Unlock and reset password
ALTER USER locked_username IDENTIFIED BY "NewPassword123" ACCOUNT UNLOCK;

-- Check profile settings
SELECT resource_name, limit FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = 'USERNAME')
AND resource_name LIKE '%LOGIN%' OR resource_name LIKE '%LOCK%';

-- Check failed login attempts
SELECT timestamp, userhost, returncode FROM dba_audit_trail
WHERE username = 'USERNAME' AND action_name = 'LOGON' AND returncode != 0
ORDER BY timestamp DESC;

-- Modify profile to be less restrictive
ALTER PROFILE profile_name LIMIT FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME 1/24;

Prevention Guidelines

• Monitor failed login patterns - Regular review of audit trails
• Set appropriate lock policies - Balance security with usability
• Implement auto-unlock for temporary locks - Reduce admin overhead
• Use strong password policies - Prevent brute force attacks
• Regular security reviews - Audit account settings and access
• User education - Train users on password security best practices