DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01005 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01005: null password given; logon denied — A Practical Guide

ORA-01005 occurs when Oracle receives a NULL or empty password during a login attempt, causing the connection to be rejected immediately. This error is not always caused by a user simply forgetting to type a password — it frequently originates from misconfigured application connection strings, uninitialized variables in code, or broken authentication setups. Understanding the root cause quickly is key to restoring service with minimal downtime.

Top 3 Causes and Fixes

Cause 1: Empty Password in Connection String or Config File

The most common cause is a missing password value in a configuration file, environment variable, or connection string. This often happens during deployments when secrets are not properly injected into the environment.

Diagnosis:

-- Test direct connection via SQL*Plus
CONNECT myuser/mypassword@MYDB

-- If password is blank, this triggers ORA-01005:
-- CONNECT myuser/@MYDB  <-- ERROR

-- Verify current session after successful login
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS logged_in_user
FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Fix: Ensure your connection string always carries a non-null password. For database links, explicitly define credentials:

-- Recreate DB Link with explicit password
DROP DATABASE LINK my_link;

CREATE DATABASE LINK my_link
  CONNECT TO remote_user IDENTIFIED BY "SecurePass@2024"
  USING 'REMOTE_DB';

-- Verify the link works
SELECT * FROM dual@my_link;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Uninitialized Password Variable in Application Code

In PL/SQL scripts or external application code (Java, Python, Shell), a password variable may be declared but never assigned a value before being passed to the connection function. This is a subtle bug that often slips through without proper testing.

-- Example: Checking user account status before troubleshooting further
SELECT username,
       account_status,
       expiry_date,
       password_versions
FROM   dba_users
WHERE  username = UPPER('myuser');

-- Reset the password if needed
ALTER USER myuser IDENTIFIED BY "NewPass@2024";

-- Unlock account if locked due to repeated failures
ALTER USER myuser ACCOUNT UNLOCK;
Enter fullscreen mode Exit fullscreen mode

Fix: Always validate that password variables are initialized before use. Add null/empty checks in your application logic before establishing a database connection.

Cause 3: Oracle Wallet or External Authentication Misconfiguration

When using Oracle Wallet, LDAP, or OS-based authentication, a misconfigured sqlnet.ora or missing wallet entry can result in a NULL password being sent, triggering ORA-01005.

-- Check authentication-related database parameters
SELECT name, value
FROM   v$parameter
WHERE  name IN ('os_authent_prefix',
                'remote_os_authent',
                'sec_case_sensitive_logon');

-- Example: Creating an externally authenticated user (OS auth)
CREATE USER ops$oracle IDENTIFIED EXTERNALLY;
GRANT CREATE SESSION TO ops$oracle;

-- After configuring sqlnet.ora with wallet path,
-- connect without password using wallet credentials:
-- CONNECT /@MYDB
Enter fullscreen mode Exit fullscreen mode

Fix: Verify sqlnet.ora contains the correct wallet location and that credentials are properly stored using mkstore. Confirm SQLNET.AUTHENTICATION_SERVICES is set appropriately for your environment.

Quick Investigation Checklist 

-- 1. Check recent failed logins (requires auditing enabled)
AUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL;

SELECT username,
       userhost,
       timestamp,
       returncode
FROM   dba_audit_session
WHERE  returncode = 1005
ORDER  BY timestamp DESC
FETCH FIRST 10 ROWS ONLY;

-- 2. Check password profile limits
SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile = 'DEFAULT'
AND    resource_type = 'PASSWORD';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Use Oracle Wallet for credential management. Never store passwords in plain text inside config files or source code. Oracle Wallet centralizes credential storage and eliminates the risk of empty passwords reaching the database.

Enable login failure auditing. Capture every ORA-01005 event using Oracle's Unified Auditing so you can detect misconfigurations early — before they cause production outages.

-- Enable failed session auditing
AUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL;

-- Monitor failures in real time
SELECT username, returncode, timestamp
FROM   dba_audit_trail
WHERE  returncode = 1005
ORDER  BY timestamp DESC;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01017 — Wrong username or password (value provided but incorrect)
  • ORA-28000 — Account locked after repeated failed logins
  • ORA-28001 — Password expired, requiring a reset before next login
  • ORA-12154 — TNS connection identifier not resolved (network-level misconfiguration)

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