DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01017 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01017: invalid username/password; logon denied — Complete Troubleshooting Guide

ORA-01017 is one of the most common Oracle errors, occurring when a client attempts to connect to an Oracle database with an incorrect username or password. While it sounds straightforward, this error can be triggered by several non-obvious causes beyond a simple typo — including case sensitivity changes introduced in Oracle 11g, locked or expired accounts, and misconfigured connection strings. Understanding the root cause quickly is essential to minimize downtime in production environments.

Top 3 Causes and SQL Examples

Cause 1: Password Case Sensitivity (Most Common After Upgrades)

Starting with Oracle 11g, passwords are case-sensitive by default. This is the number one cause of ORA-01017 after a database upgrade. If your application was connecting with Tiger but the password was set as tiger, it will fail on 11g and above.

-- Check current case sensitivity setting
SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON;

-- Check password versions for a user
SELECT username, password_versions
FROM dba_users
WHERE username = 'SCOTT';
-- Expected: 10G 11G 12C (all versions for compatibility)

-- Reset password with correct case (recommended fix)
ALTER USER scott IDENTIFIED BY "Tiger123";

-- Temporary workaround only — not recommended for production security
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Locked or Expired Account

Oracle's password profile policies (FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME) can lock or expire accounts automatically. Some clients display ORA-01017 instead of ORA-28000 for locked accounts, so always verify account status when troubleshooting.

-- Check account status
SELECT username,
       account_status,
       lock_date,
       expiry_date,
       profile
FROM dba_users
WHERE username = 'SCOTT';

-- Unlock a locked account
ALTER USER scott ACCOUNT UNLOCK;

-- Reset expired password and unlock simultaneously
ALTER USER scott IDENTIFIED BY "NewSecure#Pass2024" ACCOUNT UNLOCK;

-- Check profile policies
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT'
  AND resource_name IN (
      'FAILED_LOGIN_ATTEMPTS',
      'PASSWORD_LOCK_TIME',
      'PASSWORD_LIFE_TIME'
  );
Enter fullscreen mode Exit fullscreen mode

Cause 3: Wrong Credentials in Connection String or Config Files

Hardcoded credentials in application config files, tnsnames.ora, or JDBC connection pools are a frequent culprit — especially when managing multiple environments (dev/staging/prod). Always verify the exact username exists in the target database.

-- Verify user exists and is OPEN
SELECT username, account_status
FROM dba_users
WHERE username = UPPER('scott');

-- If locked out of everything, connect as sysdba via OS authentication
-- Run from the database server OS:
-- sqlplus / as sysdba

-- Then identify all non-OPEN accounts
SELECT username, account_status, lock_date
FROM dba_users
WHERE account_status != 'OPEN'
ORDER BY account_status;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Fix
Wrong password ALTER USER <name> IDENTIFIED BY "<new_password>";
Account locked ALTER USER <name> ACCOUNT UNLOCK;
Password expired ALTER USER <name> IDENTIFIED BY "<new_password>";
Case sensitivity issue Reset password or set SEC_CASE_SENSITIVE_LOGON=FALSE (temporary)
Can't connect at all sqlplus / as sysdba via OS, then diagnose

Prevention Tips

1. Enable Login Failure Auditing

Set up auditing to detect brute-force attempts or misconfigured apps hitting the login failure threshold before accounts get locked.

-- Unified Auditing (12c and above)
AUDIT POLICY ORA_LOGON_FAILURES;

-- Traditional auditing
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
Enter fullscreen mode Exit fullscreen mode

2. Monitor Expiring Accounts Proactively

Run this query regularly (or schedule it with DBMS_SCHEDULER) to catch accounts expiring within 30 days before they cause application outages.

SELECT username,
       expiry_date,
       ROUND(expiry_date - SYSDATE) AS days_left
FROM dba_users
WHERE expiry_date BETWEEN SYSDATE AND SYSDATE + 30
  AND account_status = 'OPEN'
ORDER BY expiry_date;
Enter fullscreen mode Exit fullscreen mode

Also, store credentials in a secure vault (Oracle Wallet, HashiCorp Vault) rather than plaintext config files — this eliminates stale password issues and reduces the attack surface significantly.

Related Errors

  • ORA-28000 — Account is locked. Use ALTER USER ... ACCOUNT UNLOCK.
  • ORA-28001 — Password has expired. Reset with ALTER USER ... IDENTIFIED BY.
  • ORA-28002 — Password expiring soon. Change it proactively to avoid ORA-01017.
  • ORA-01031 — Insufficient privileges (correct credentials, wrong permissions).

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)