Oracle ORA-01040 Error: Causes and Solutions Complete Guide

ORA-01040: Invalid Character in Password; Logon Denied

ORA-01040 is an Oracle authentication error that occurs when a password contains characters that Oracle cannot properly parse during the login process. This typically happens when special characters like @, /, or " appear in a password without proper quoting, causing Oracle's internal parser to misinterpret the credential string. It is one of the most common login issues in environments enforcing strong password policies.

---

Top 3 Causes

1. Unquoted Special Characters in SQL*Plus

When connecting via SQL*Plus, special characters in passwords must be wrapped in double quotes. Without quoting, Oracle misreads the password string and immediately denies the logon.

-- WRONG: Will trigger ORA-01040
sqlplus scott/P@ss/word@ORCL

-- CORRECT: Wrap password in double quotes
sqlplus scott/"P@ss/word"@ORCL

-- Or connect interactively
sqlplus /nolog
SQL> CONNECT scott/"P@ss/word"@ORCL

2. Special Characters When Changing Passwords

Using ALTER USER to set a password with special characters also requires double-quote encapsulation. Forgetting this step is a frequent DBA mistake during routine password rotations.

-- WRONG: May cause parsing issues
ALTER USER scott IDENTIFIED BY P@ssw0rd!;

-- CORRECT: Enclose password in double quotes
ALTER USER scott IDENTIFIED BY "P@ssw0rd!";

-- Verify account status after change
SELECT username, account_status, expiry_date
FROM dba_users
WHERE username = 'SCOTT';

-- Test the new password immediately
CONNECT scott/"P@ssw0rd!"@ORCL

3. JDBC / Application Connection String Mishandling

Embedding passwords with special characters directly in JDBC URLs can cause the driver to corrupt the password before it even reaches Oracle. Using a Properties object avoids this problem entirely.

-- Create a test user with special character password (DBA side)
CREATE USER app_user IDENTIFIED BY "S3cur3@Pass#!";
GRANT CONNECT, RESOURCE TO app_user;

-- Verify user creation
SELECT username, account_status FROM dba_users
WHERE username = 'APP_USER';

// WRONG: Special characters in URL cause ORA-01040
Connection conn = DriverManager.getConnection(
    "jdbc:oracle:thin:app_user/S3cur3@Pass#!@localhost:1521:ORCL"
);

// CORRECT: Use Properties object to safely pass credentials
Properties props = new Properties();
props.setProperty("user", "app_user");
props.setProperty("password", "S3cur3@Pass#!");
Connection conn = DriverManager.getConnection(
    "jdbc:oracle:thin:@localhost:1521:ORCL", props
);

---

Quick Fix Solutions

-- 1. Check current password profile settings
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_type = 'PASSWORD';

-- 2. Reset password with a safe, quoted value
ALTER USER problematic_user IDENTIFIED BY "SafePass_2024";

-- 3. Unlock account if repeated ORA-01040 caused a lockout
ALTER USER problematic_user ACCOUNT UNLOCK;

-- 4. Check if account is locked or expired
SELECT username, account_status, lock_date, expiry_date
FROM dba_users
WHERE account_status != 'OPEN';

---

Prevention Tips

1. Define an Allowed Special Character Policy
Establish a company-wide password standard that explicitly lists safe special characters (e.g., !, #, $, _, -) and excludes Oracle parser-sensitive characters like @, /, \, and ". Document this in your DBA runbook and enforce it in your password change procedures.

2. Use Oracle Wallet to Eliminate Hardcoded Passwords
Store credentials in Oracle Wallet so applications never need to embed passwords in connection strings, completely eliminating the risk of ORA-01040 from special character mishandling.

-- After configuring wallet with mkstore, connect without a password:
-- mkstore -wrl /oracle/wallet -createCredential ORCL app_user "S3cur3@Pass#!"
-- sqlplus /@ORCL  (no password needed in the connection string)

-- Verify wallet-based connection works
SELECT sys_context('USERENV', 'SESSION_USER') AS current_user FROM dual;

---

Related Errors

Error Code	Description
ORA-01017	Invalid username/password — credential mismatch rather than invalid characters
ORA-28000	Account locked — often triggered after repeated ORA-01040 failures
ORA-28001	Password expired — password change process can introduce ORA-01040
ORA-12154	TNS resolution failure — can occur when @ in a password breaks the TNS string

---

📖 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.