Fix: ORA-01017: invalid username/password; logon denied

In Oracle Database 19c the error reads:

ORA-01017: invalid username/password; logon denied

Newer releases use broader wording, ORA-01017: invalid credential or not authorized; logon denied, because the same error also covers other authentication methods. Either way, the client reached the database (the listener and service were fine) and authentication failed.

Cause 1: wrong password, often by case

Oracle passwords are case-sensitive. Passwords containing special characters can also be split or changed by the shell or by SQL*Plus. Let the tool prompt for the password instead of putting it on the command line:

sqlplus [email protected]:1521/orclpdb1.example.com
Enter password:

If the password must be set in a script, quote it with double quotes inside SQL: ALTER USER appuser IDENTIFIED BY "CHANGE_ME#2026";

Cause 2: connected to the wrong container

A local user exists only in its own PDB. Connecting to the CDB root’s service, or to another PDB, with that user name fails with ORA-01017 even when the password is correct. Check the service name in your connect string, then verify where the user lives:

-- as a DBA in the root
SELECT con_id, username, account_status FROM cdb_users WHERE username = 'APPUSER';
SELECT con_id, name FROM v$pdbs;

Remember that unquoted user names are stored in upper case: appuser becomes APPUSER.

Cause 3: the password was changed or never set as expected

As a DBA connected to the right PDB, reset it and force a change at next login:

ALTER USER appuser IDENTIFIED BY "CHANGE_ME_Str0ng#1" PASSWORD EXPIRE;

Related: ORA-28000, the account is locked

Repeated failed logins usually end in a different error:

ORA-28000: The account is locked.

Oracle locks an account after FAILED_LOGIN_ATTEMPTS consecutive failures (a limit from the user’s profile), or when an administrator locked it, or for a common user locked in the root container. Depending on the profile’s PASSWORD_LOCK_TIME it unlocks itself after a while (status LOCKED(TIMED)) or stays locked until a DBA unlocks it.

SELECT username, account_status, lock_date, profile
FROM   dba_users
WHERE  username = 'APPUSER';

SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile = 'DEFAULT'
AND    resource_name IN ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME');

ALTER USER appuser ACCOUNT UNLOCK;

Before unlocking, find what kept trying the wrong password, typically an application server, a scheduled job or a connection pool with an old password. If you only unlock the account, that client locks it again within minutes. Update the stored credential first, then unlock.

Checklist

1. Retype the password at the prompt, minding case.
2. Confirm the service name points to the PDB where the user exists.
3. Check ACCOUNT_STATUS in DBA_USERS (OPEN, LOCKED, EXPIRED…).
4. Reset the password and update every client that stores it.
5. Unlock the account only after the source of failed logins is fixed.

Official documentation

• Oracle Database error help: ORA-01017
• Oracle Database error help: ORA-28000
• Oracle Database 19c SQL Reference: CREATE USER