ORA-01045: user lacks CREATE SESSION privilege; logon denied
ORA-01045 is one of the most common Oracle errors DBAs encounter, occurring when a user attempts to log into the database without the CREATE SESSION system privilege. Unlike many database systems that grant basic login rights automatically, Oracle requires this privilege to be explicitly granted before any connection is possible. This error typically surfaces right after creating a new user account, after a privilege revocation, or following a schema migration.
Top 3 Causes
1. Missing GRANT After User Creation
Oracle does not automatically assign any privileges when a new user is created. Forgetting to grant CREATE SESSION right after CREATE USER is the single most frequent cause of this error.
-- WRONG: User created but no privilege granted
CREATE USER app_user IDENTIFIED BY "MyPass123!";
-- At this point, app_user CANNOT log in
-- CORRECT: Always follow with a GRANT
CREATE USER app_user IDENTIFIED BY "MyPass123!"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CREATE SESSION TO app_user;
-- Verify the privilege was granted
SELECT GRANTEE, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'APP_USER';
2. Accidental REVOKE or Role Modification
A REVOKE command or modification to the CONNECT role can silently strip login access from one or many users at once. This is especially dangerous in production environments because the impact is immediate.
-- Check if a user has CREATE SESSION directly or via a role
SELECT 'DIRECT' AS source, PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'APP_USER'
AND PRIVILEGE = 'CREATE SESSION'
UNION ALL
SELECT 'VIA ROLE: ' || GRANTED_ROLE, 'CREATE SESSION'
FROM DBA_ROLE_PRIVS RP
JOIN DBA_SYS_PRIVS SP ON RP.GRANTED_ROLE = SP.GRANTEE
WHERE RP.GRANTEE = 'APP_USER'
AND SP.PRIVILEGE = 'CREATE SESSION';
-- Find ALL users currently missing CREATE SESSION
SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS
WHERE USERNAME NOT IN (
SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE = 'CREATE SESSION'
UNION
SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'CONNECT'
)
AND USERNAME NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','XDB','ANONYMOUS');
3. Incomplete Schema Migration
When migrating schemas between environments using Data Pump or manual DDL scripts, privilege grants are sometimes omitted or executed out of order. Always validate privileges post-migration.
-- After migration, verify privileges for all migrated users
SELECT U.USERNAME,
NVL(P.PRIVILEGE, 'MISSING - ACTION REQUIRED') AS session_priv
FROM DBA_USERS U
LEFT JOIN DBA_SYS_PRIVS P
ON U.USERNAME = P.GRANTEE AND P.PRIVILEGE = 'CREATE SESSION'
WHERE U.USERNAME IN ('USER1', 'USER2', 'USER3') -- your migrated users
ORDER BY U.USERNAME;
-- Generate remediation script automatically
SELECT 'GRANT CREATE SESSION TO ' || USERNAME || ';' AS fix_script
FROM DBA_USERS
WHERE USERNAME NOT IN (
SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE = 'CREATE SESSION'
UNION
SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'CONNECT'
)
AND ACCOUNT_STATUS = 'OPEN';
Quick Fix Solutions
-- Option 1: Grant privilege directly
GRANT CREATE SESSION TO username;
-- Option 2: Grant via CONNECT role (recommended for application users)
GRANT CONNECT TO username;
-- Option 3: Grant with ADMIN OPTION (allows user to grant to others)
GRANT CREATE SESSION TO username WITH ADMIN OPTION;
Prevention Tips
Standardize user creation with a mandatory template that always includes privilege grants. Never run CREATE USER without immediately following it with the appropriate GRANT statements.
-- Standard user creation template
CREATE USER new_user IDENTIFIED BY "SecurePass!"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 50M ON users;
GRANT CREATE SESSION TO new_user; -- Always include this line
GRANT CREATE TABLE, CREATE VIEW TO new_user;
Audit privilege changes to catch accidental revocations before they cause outages. Enable auditing on GRANT and REVOKE operations so you always have a traceable history.
-- Enable auditing for privilege changes
AUDIT GRANT ANY PRIVILEGE BY ACCESS;
-- Review recent privilege changes
SELECT EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME IN ('GRANT','REVOKE')
ORDER BY EVENT_TIMESTAMP DESC
FETCH FIRST 20 ROWS ONLY;
Related Errors
- ORA-01017 — Invalid username or password
- ORA-28000 — Account is locked
- ORA-28001 — Password has expired
- ORA-01031 — Insufficient privileges (post-login)
📖 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)