ORA-01017 Invalid Username/Password - Authentication Troubleshooting

ORA-01017: Invalid Username/Password

Error Overview

Error Text: ORA-01017: invalid username/password; logon denied

This error occurs when Oracle cannot authenticate a user due to incorrect credentials, expired passwords, locked accounts, or authentication configuration issues. It’s one of the most common connection errors and can indicate security, configuration, or user management problems.

Understanding Oracle Authentication

Authentication Methods

Oracle Authentication Types
├── Database Authentication - Passwords stored in Oracle
├── OS Authentication - Operating system validates user
├── External Authentication - LDAP, Active Directory, etc.
├── Strong Authentication - Kerberos, PKI certificates
└── Proxy Authentication - One user connecting as another

Common Scenarios

• Wrong credentials - Typos in username or password
• Expired passwords - Password aging policies
• Locked accounts - Failed login attempts or administrative action
• Case sensitivity - Username/password case issues
• Connection string errors - Wrong service name or database

Diagnostic Steps

1. Verify User Account Status

-- Check user account information
SELECT
  username,
  account_status,
  lock_date,
  expiry_date,
  default_tablespace,
  temporary_tablespace,
  profile,
  authentication_type,
  created,
  password_versions
FROM dba_users
WHERE username = UPPER('problem_username')  -- Replace with actual username
ORDER BY username;

-- Check user profile settings
SELECT
  u.username,
  u.profile,
  p.resource_name,
  p.limit
FROM dba_users u, dba_profiles p
WHERE u.profile = p.profile
  AND u.username = UPPER('problem_username')
  AND p.resource_name IN (
    'PASSWORD_LIFE_TIME',
    'PASSWORD_GRACE_TIME',
    'FAILED_LOGIN_ATTEMPTS',
    'PASSWORD_LOCK_TIME',
    'PASSWORD_VERIFY_FUNCTION'
  )
ORDER BY p.resource_name;

-- Check failed login attempts
SELECT
  username,
  timestamp,
  action_name,
  returncode,
  comment_text
FROM dba_audit_trail
WHERE username = UPPER('problem_username')
  AND action_name = 'LOGON'
  AND returncode != 0
  AND timestamp > SYSDATE - 1  -- Last 24 hours
ORDER BY timestamp DESC;

2. Check Password and Authentication Settings

-- Check password verification function
SELECT
  profile,
  resource_name,
  limit
FROM dba_profiles
WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION'
  AND profile IN (
    SELECT profile FROM dba_users WHERE username = UPPER('problem_username')
  );

-- Check authentication type
SELECT
  username,
  authentication_type,
  password_versions,
  account_status
FROM dba_users
WHERE username = UPPER('problem_username');

-- For external authentication users
SELECT
  username,
  external_name
FROM dba_users
WHERE username = UPPER('problem_username')
  AND authentication_type = 'EXTERNAL';

-- Check proxy authentication
SELECT
  proxy,
  client,
  authentication,
  authorization,
  role
FROM dba_proxies
WHERE client = UPPER('problem_username')
   OR proxy = UPPER('problem_username');

3. Analyze Connection Attempts

-- Check recent connection attempts from audit trail
SELECT
  username,
  userhost,
  terminal,
  timestamp,
  action_name,
  returncode,
  CASE returncode
    WHEN 0 THEN 'SUCCESS'
    WHEN 1017 THEN 'INVALID USERNAME/PASSWORD'
    WHEN 28000 THEN 'ACCOUNT LOCKED'
    WHEN 28001 THEN 'PASSWORD EXPIRED'
    WHEN 28002 THEN 'PASSWORD WILL EXPIRE'
    ELSE 'OTHER ERROR'
  END as error_description
FROM dba_audit_trail
WHERE action_name = 'LOGON'
  AND timestamp > SYSDATE - 7  -- Last week
  AND (username = UPPER('problem_username') OR returncode IN (1017, 28000, 28001))
ORDER BY timestamp DESC;

-- Check listener log for connection attempts (if accessible)
-- This requires access to listener log files on the server

-- Check current sessions for the user
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.program,
  s.machine,
  s.osuser,
  s.logon_time,
  s.last_call_et
FROM v$session s
WHERE s.username = UPPER('problem_username')
ORDER BY s.logon_time DESC;

4. Test Authentication Components

-- Check if database authentication is working
-- (Run as DBA user)
CREATE USER test_auth_user IDENTIFIED BY "TempPassword123";
GRANT CREATE SESSION TO test_auth_user;

-- Test connection (from client)
-- sqlplus test_auth_user/TempPassword123@database

-- Cleanup test user
DROP USER test_auth_user;

-- Check database links that might use the problematic credentials
SELECT
  owner,
  db_link,
  username,
  host,
  created
FROM dba_db_links
WHERE username = UPPER('problem_username')
   OR db_link LIKE '%' || UPPER('problem_username') || '%';

-- Check for case sensitivity issues
SELECT
  username,
  account_status
FROM dba_users
WHERE LOWER(username) = LOWER('problem_username')
ORDER BY username;

Immediate Solutions

Solution 1: Fix Basic Authentication Issues

Verify and Correct Credentials

-- Check if username exists (case-sensitive)
SELECT username, account_status
FROM dba_users
WHERE username = 'problem_username';  -- Try exact case

-- If case is wrong, use correct case
SELECT username, account_status
FROM dba_users
WHERE UPPER(username) = UPPER('problem_username');

-- Test with corrected credentials
-- sqlplus correct_username/correct_password@database_service

Reset Password (if authorized)

-- Reset user password (requires DBA privileges)
ALTER USER problem_username IDENTIFIED BY "NewPassword123";

-- If password verification function is causing issues, temporarily disable it
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;
ALTER USER problem_username IDENTIFIED BY "NewPassword123";
-- Re-enable verification function
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

-- For users with external authentication
ALTER USER problem_username IDENTIFIED EXTERNALLY;

-- Test the new password
-- sqlplus problem_username/NewPassword123@database_service

Solution 2: Unlock and Fix Account Status

Unlock Locked Accounts

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

-- Unlock account
ALTER USER problem_username ACCOUNT UNLOCK;

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

-- Check profile settings that might cause relocking
SELECT
  profile,
  resource_name,
  limit
FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = UPPER('problem_username'))
  AND resource_name IN ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME');

-- Temporarily modify profile to prevent immediate relocking
ALTER PROFILE user_profile LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Handle Expired Passwords

-- Check password expiration
SELECT
  username,
  account_status,
  expiry_date,
  ROUND(expiry_date - SYSDATE, 2) as days_until_expiry
FROM dba_users
WHERE username = UPPER('problem_username');

-- Reset expired password
ALTER USER problem_username IDENTIFIED BY "NewPassword123";

-- Extend password life if needed
ALTER PROFILE user_profile LIMIT PASSWORD_LIFE_TIME UNLIMITED;

-- Or set a longer password life time
ALTER PROFILE user_profile LIMIT PASSWORD_LIFE_TIME 90;  -- 90 days

Solution 3: Fix Connection String Issues

Verify Database Service Name

-- Check available services
SELECT
  name,
  network_name,
  enabled
FROM v$services
ORDER BY name;

-- Check listener status (from command line)
-- lsnrctl status

-- Check TNS names resolution
-- tnsping service_name

-- Verify database is open and available
SELECT
  instance_name,
  status,
  database_status,
  host_name
FROM v$instance;

Test Different Connection Methods

# Test with different connection formats

# Using service name
sqlplus username/password@//hostname:port/service_name

# Using SID
sqlplus username/password@//hostname:port:SID

# Using TNS alias
sqlplus username/password@tns_alias

# Local connection (if on database server)
sqlplus username/password

# Test with SQL*Plus /nolog to avoid immediate connection
sqlplus /nolog
SQL> connect username/password@service_name

Long-Term Solutions

1. Implement Proper Password Management

Configure Password Policies

-- Create comprehensive password verification function
CREATE OR REPLACE FUNCTION verify_password_policy(
  username VARCHAR2,
  password VARCHAR2,
  old_password VARCHAR2
) RETURN BOOLEAN IS
  n BOOLEAN;
  m INTEGER;
  differ INTEGER;
  isdigit BOOLEAN;
  ischar BOOLEAN;
  ispunct BOOLEAN;
  digitarray VARCHAR2(20);
  punctarray VARCHAR2(25);
  chararray VARCHAR2(52);
BEGIN
  digitarray:= '0123456789';
  chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  punctarray:='!"#$%&()``*+,-/:;<=>?_';

  -- Check password length
  IF LENGTH(password) < 8 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 8 characters long');
  END IF;

  -- Check password not same as username
  IF UPPER(password) = UPPER(username) THEN
    RAISE_APPLICATION_ERROR(-20002, 'Password cannot be the same as username');
  END IF;

  -- Check password complexity
  isdigit := FALSE;
  ischar := FALSE;
  ispunct := FALSE;

  FOR i IN 1..LENGTH(password) LOOP
    IF INSTR(digitarray, SUBSTR(password, i, 1)) > 0 THEN
      isdigit := TRUE;
    ELSIF INSTR(chararray, SUBSTR(password, i, 1)) > 0 THEN
      ischar := TRUE;
    ELSIF INSTR(punctarray, SUBSTR(password, i, 1)) > 0 THEN
      ispunct := TRUE;
    END IF;
  END LOOP;

  -- Must contain at least 3 different character types
  IF (isdigit AND ischar AND ispunct) OR
     (isdigit AND ischar AND LENGTH(password) >= 12) THEN
    NULL; -- Password meets complexity requirements
  ELSE
    RAISE_APPLICATION_ERROR(-20003, 'Password must contain letters, numbers, and special characters');
  END IF;

  -- Check password differs from old password
  IF old_password IS NOT NULL THEN
    differ := LENGTH(old_password) - LENGTH(password);
    IF ABS(differ) < 3 THEN
      IF LENGTH(password) < LENGTH(old_password) THEN
        m := LENGTH(password);
      ELSE
        m := LENGTH(old_password);
      END IF;

      differ := ABS(differ);
      FOR i IN 1..m LOOP
        IF SUBSTR(password, i, 1) != SUBSTR(old_password, i, 1) THEN
          differ := differ + 1;
        END IF;
      END LOOP;

      IF differ < 3 THEN
        RAISE_APPLICATION_ERROR(-20004, 'Password must differ from old password by at least 3 characters');
      END IF;
    END IF;
  END IF;

  RETURN TRUE;
END;
/

-- Create secure password profile
CREATE PROFILE secure_password_profile LIMIT
  PASSWORD_LIFE_TIME 90
  PASSWORD_GRACE_TIME 7
  PASSWORD_REUSE_TIME 365
  PASSWORD_REUSE_MAX 12
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 1/24  -- 1 hour
  PASSWORD_VERIFY_FUNCTION verify_password_policy;

-- Apply profile to users
ALTER USER problem_username PROFILE secure_password_profile;

Implement Password History

-- Create password history table
CREATE TABLE password_history (
  username VARCHAR2(30),
  password_hash VARCHAR2(128),
  change_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  changed_by VARCHAR2(30)
);

-- Create trigger to track password changes
CREATE OR REPLACE TRIGGER trg_password_history
AFTER ALTER ON DATABASE
WHEN (ora_dict_obj_type = 'USER' AND
      (ora_sysevent = 'ALTER' AND ora_sql_txt LIKE '%IDENTIFIED%'))
DECLARE
  v_username VARCHAR2(30);
  v_sql_text ORA_NAME_LIST_T;
  v_sql VARCHAR2(4000);
BEGIN
  v_username := ora_dict_obj_name;

  -- Get the SQL text
  FOR i IN 1..ora_sql_txt(sql_txt) LOOP
    v_sql := v_sql || ora_sql_txt(i);
  END LOOP;

  -- Log password change
  INSERT INTO password_history (username, password_hash, changed_by)
  VALUES (v_username, 'CHANGED', USER);

  COMMIT;
END;
/

2. Enhanced Authentication Security

Implement Account Monitoring

-- Create login monitoring table
CREATE TABLE login_monitoring (
  username VARCHAR2(30),
  login_time TIMESTAMP,
  source_ip VARCHAR2(15),
  program VARCHAR2(100),
  success CHAR(1),
  failure_reason VARCHAR2(100)
);

-- Create login trigger
CREATE OR REPLACE TRIGGER trg_login_monitor
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO login_monitoring (
    username, login_time, source_ip, program, success
  ) VALUES (
    USER,
    SYSTIMESTAMP,
    SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
    SYS_CONTEXT('USERENV', 'MODULE'),
    'Y'
  );
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    NULL; -- Don't fail login due to monitoring issues
END;
/

-- Monitor failed login attempts
CREATE OR REPLACE PROCEDURE monitor_failed_logins AS
  CURSOR failed_logins IS
    SELECT
      username,
      COUNT(*) as failed_attempts,
      MAX(timestamp) as last_attempt,
      userhost
    FROM dba_audit_trail
    WHERE action_name = 'LOGON'
      AND returncode = 1017
      AND timestamp > SYSDATE - 1/24  -- Last hour
    GROUP BY username, userhost
    HAVING COUNT(*) >= 3;  -- 3 or more failures

BEGIN
  FOR login_rec IN failed_logins LOOP
    -- Alert on suspicious activity
    DBMS_OUTPUT.PUT_LINE('Warning: ' || login_rec.failed_attempts ||
                        ' failed login attempts for user ' || login_rec.username ||
                        ' from ' || login_rec.userhost);

    -- Optionally lock account after many failures
    IF login_rec.failed_attempts >= 10 THEN
      EXECUTE IMMEDIATE 'ALTER USER ' || login_rec.username || ' ACCOUNT LOCK';
    END IF;
  END LOOP;
END;
/

3. Connection Management and Troubleshooting

Create Connection Testing Tools

-- Create connection test procedure
CREATE OR REPLACE PROCEDURE test_user_connection(
  p_username VARCHAR2,
  p_password VARCHAR2 DEFAULT NULL
) AS
  v_user_exists NUMBER;
  v_account_status VARCHAR2(32);
  v_profile VARCHAR2(30);
  v_auth_type VARCHAR2(30);
BEGIN
  -- Check if user exists
  BEGIN
    SELECT 1, account_status, profile, authentication_type
    INTO v_user_exists, v_account_status, v_profile, v_auth_type
    FROM dba_users
    WHERE username = UPPER(p_username);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('ERROR: User ' || p_username || ' does not exist');
      RETURN;
  END;

  DBMS_OUTPUT.PUT_LINE('User Information:');
  DBMS_OUTPUT.PUT_LINE('  Username: ' || UPPER(p_username));
  DBMS_OUTPUT.PUT_LINE('  Account Status: ' || v_account_status);
  DBMS_OUTPUT.PUT_LINE('  Profile: ' || v_profile);
  DBMS_OUTPUT.PUT_LINE('  Authentication Type: ' || v_auth_type);

  -- Check account status
  IF v_account_status != 'OPEN' THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: Account is not in OPEN status');

    IF v_account_status LIKE '%LOCKED%' THEN
      DBMS_OUTPUT.PUT_LINE('  Solution: ALTER USER ' || UPPER(p_username) || ' ACCOUNT UNLOCK;');
    END IF;

    IF v_account_status LIKE '%EXPIRED%' THEN
      DBMS_OUTPUT.PUT_LINE('  Solution: ALTER USER ' || UPPER(p_username) || ' IDENTIFIED BY new_password;');
    END IF;
  END IF;

  -- Check profile restrictions
  FOR profile_rec IN (
    SELECT resource_name, limit
    FROM dba_profiles
    WHERE profile = v_profile
      AND resource_name IN (
        'FAILED_LOGIN_ATTEMPTS',
        'PASSWORD_LIFE_TIME',
        'PASSWORD_LOCK_TIME',
        'PASSWORD_GRACE_TIME'
      )
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('  ' || profile_rec.resource_name || ': ' || profile_rec.limit);
  END LOOP;

  -- Check recent login failures
  FOR failure_rec IN (
    SELECT COUNT(*) as failure_count, MAX(timestamp) as last_failure
    FROM dba_audit_trail
    WHERE username = UPPER(p_username)
      AND action_name = 'LOGON'
      AND returncode != 0
      AND timestamp > SYSDATE - 1
  ) LOOP
    IF failure_rec.failure_count > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Recent login failures: ' || failure_rec.failure_count);
      DBMS_OUTPUT.PUT_LINE('Last failure: ' || failure_rec.last_failure);
    END IF;
  END LOOP;

END;
/

-- Create database connection health check
CREATE OR REPLACE PROCEDURE check_connection_health AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== Database Connection Health Check ===');

  -- Check database status
  FOR db_rec IN (
    SELECT instance_name, status, database_status, host_name
    FROM v$instance
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Database: ' || db_rec.instance_name);
    DBMS_OUTPUT.PUT_LINE('  Status: ' || db_rec.status);
    DBMS_OUTPUT.PUT_LINE('  Database Status: ' || db_rec.database_status);
    DBMS_OUTPUT.PUT_LINE('  Host: ' || db_rec.host_name);
  END LOOP;

  -- Check listener services
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('Available Services:');
  FOR svc_rec IN (
    SELECT name, network_name, enabled
    FROM v$services
    ORDER BY name
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('  ' || svc_rec.name || ' (' || svc_rec.enabled || ')');
  END LOOP;

  -- Check current connections
  SELECT COUNT(*) INTO v_connection_count
  FROM v$session
  WHERE username IS NOT NULL;

  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('Current Connections: ' || v_connection_count);

END;
/

4. Application Integration

Implement Connection Retry Logic

// Java example with comprehensive authentication error handling
public class OracleConnectionManager {
    private static final int MAX_RETRY_ATTEMPTS = 3;
    private static final int RETRY_DELAY_MS = 1000;

    public Connection getConnection(String username, String password, String url)
            throws SQLException {
        int attempts = 0;
        SQLException lastException = null;

        while (attempts < MAX_RETRY_ATTEMPTS) {
            try {
                Connection conn = DriverManager.getConnection(url, username, password);

                // Test connection is valid
                if (!conn.isValid(5)) {
                    conn.close();
                    throw new SQLException("Connection validation failed");
                }

                return conn;

            } catch (SQLException e) {
                lastException = e;
                attempts++;

                // Handle specific authentication errors
                if (e.getErrorCode() == 1017) { // ORA-01017
                    throw new AuthenticationException("Invalid username/password for user: " + username, e);
                } else if (e.getErrorCode() == 28000) { // Account locked
                    throw new AccountLockedException("Account locked for user: " + username, e);
                } else if (e.getErrorCode() == 28001) { // Password expired
                    throw new PasswordExpiredException("Password expired for user: " + username, e);
                } else if (e.getErrorCode() == 12541) { // TNS no listener
                    if (attempts < MAX_RETRY_ATTEMPTS) {
                        try {
                            Thread.sleep(RETRY_DELAY_MS * attempts);
                        } catch (InterruptedException ie) {
                            Thread.currentThread().interrupt();
                            throw new SQLException("Connection interrupted", ie);
                        }
                        continue; // Retry for network issues
                    }
                }

                // For other errors, don't retry
                break;
            }
        }

        throw new SQLException("Failed to connect after " + attempts + " attempts", lastException);
    }

    // Custom exception classes
    public static class AuthenticationException extends SQLException {
        public AuthenticationException(String message, Throwable cause) {
            super(message, cause);
        }
    }

    public static class AccountLockedException extends SQLException {
        public AccountLockedException(String message, Throwable cause) {
            super(message, cause);
        }
    }

    public static class PasswordExpiredException extends SQLException {
        public PasswordExpiredException(String message, Throwable cause) {
            super(message, cause);
        }
    }
}

Connection Pool Configuration

// Connection pool with authentication error handling
public class SecureConnectionPool {
    private HikariDataSource dataSource;

    public void configureConnectionPool() {
        HikariConfig config = new HikariConfig();

        // Basic connection settings
        config.setJdbcUrl("jdbc:oracle:thin:@//hostname:1521/service_name");
        config.setUsername("app_user");
        config.setPassword("encrypted_password");

        // Connection validation
        config.setConnectionTestQuery("SELECT 1 FROM DUAL");
        config.setValidationTimeout(5000);

        // Pool settings for authentication issues
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(10000);
        config.setIdleTimeout(300000);

        // Retry configuration
        config.setInitializationFailTimeout(60000);
        config.setRetryAttempts(3);

        // Health check
        config.setHealthCheckRegistry(new HealthCheckRegistry());

        dataSource = new HikariDataSource(config);
    }

    public Connection getConnection() throws SQLException {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            if (e.getErrorCode() == 1017) {
                // Log authentication failure and possibly refresh credentials
                logger.error("Authentication failed - checking credential configuration");
                refreshCredentials();
            }
            throw e;
        }
    }

    private void refreshCredentials() {
        // Implement credential refresh logic
        // This might involve reading from secure configuration,
        // rotating passwords, etc.
    }
}

Related Errors

• ORA-28000 - Account is locked
• ORA-28001 - Password has expired
• ORA-28002 - Password will expire within grace period
• ORA-01045 - User lacks CREATE SESSION privilege

Quick Reference

Emergency Response Steps

1. ✓ Verify username and password are correct
2. ✓ Check account status and unlock if necessary
3. ✓ Reset password if expired or forgotten
4. ✓ Verify connection string and service name
5. ✓ Check authentication method configuration
6. ✓ Test connection with different methods

Quick Commands

-- Check user status
SELECT username, account_status, expiry_date FROM dba_users
WHERE username = UPPER('problem_username');

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

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

-- Test connection
sqlplus problem_username/password@service_name

-- Check available services
SELECT name FROM v$services ORDER BY name;

Prevention Guidelines

• Use strong passwords - Follow complexity requirements
• Monitor account status - Regular checks for locked/expired accounts
• Implement retry logic - Handle temporary authentication issues
• Secure credential storage - Never hardcode passwords
• Regular password rotation - Implement automated password management
• Monitor failed attempts - Alert on suspicious authentication patterns