ORA-01045 User Lacks CREATE SESSION Privilege - Access Permission Fix

ORA-01045: User Lacks CREATE SESSION Privilege

Error Overview

Error Text: ORA-01045: user [username] lacks CREATE SESSION privilege; logon denied

This error occurs when a database user attempts to connect to Oracle but doesn’t have the necessary CREATE SESSION system privilege. It’s a security-related error that prevents unauthorized access to the database.

Understanding Oracle Privileges

System Privileges vs Object Privileges

Oracle Privileges
├── System Privileges (database-wide)
│   ├── CREATE SESSION - Connect to database
│   ├── CREATE TABLE - Create tables
│   ├── CREATE VIEW - Create views
│   └── DBA - Full database admin
└── Object Privileges (specific objects)
    ├── SELECT on table
    ├── INSERT on table
    └── EXECUTE on procedure

CREATE SESSION Privilege

• Purpose: Allows user to connect to the database
• Required for: Any database connection
• Granted by: DBA or user with GRANT ANY PRIVILEGE

Common Causes

New User Without Privileges

-- User created but no privileges granted
CREATE USER new_user IDENTIFIED BY password;
-- User cannot connect without CREATE SESSION

Revoked Privileges

-- Privilege was revoked
REVOKE CREATE SESSION FROM username;
-- User can no longer connect

Role-Based Access Issues

-- User has role but role lacks CREATE SESSION
-- Or role is not granted properly

Diagnostic Queries

Check User Privileges

-- Check direct system privileges for user
SELECT * FROM dba_sys_privs
WHERE grantee = 'USERNAME';

-- Check role-based privileges
SELECT * FROM dba_role_privs
WHERE grantee = 'USERNAME';

-- Check privileges through roles
SELECT rp.role, sp.privilege
FROM dba_role_privs rp, dba_sys_privs sp
WHERE rp.grantee = 'USERNAME'
AND rp.role = sp.grantee
AND sp.privilege = 'CREATE SESSION';

Verify User Status

-- Check if user exists and account status
SELECT username, account_status, lock_date, expiry_date
FROM dba_users
WHERE username = 'USERNAME';

Check Current Session Privileges

-- Check privileges for current session
SELECT * FROM session_privs
WHERE privilege = 'CREATE SESSION';

-- Check effective roles
SELECT * FROM session_roles;

Resolution Steps

Grant CREATE SESSION Directly

-- Connect as DBA or privileged user
GRANT CREATE SESSION TO username;

-- Verify the grant
SELECT * FROM dba_sys_privs
WHERE grantee = 'USERNAME'
AND privilege = 'CREATE SESSION';

Grant Through Role

-- Grant CONNECT role (includes CREATE SESSION)
GRANT CONNECT TO username;

-- Or create custom role
CREATE ROLE app_user_role;
GRANT CREATE SESSION TO app_user_role;
GRANT app_user_role TO username;

Multiple User Grant

-- Grant to multiple users at once
GRANT CREATE SESSION TO user1, user2, user3;

-- Grant role to multiple users
GRANT CONNECT TO user1, user2, user3;

Common Resolution Patterns

New Application User Setup

-- Complete new user setup
CREATE USER app_user IDENTIFIED BY secure_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

-- Grant basic connection privileges
GRANT CONNECT TO app_user;
GRANT RESOURCE TO app_user;

-- Additional privileges as needed
GRANT CREATE VIEW TO app_user;
GRANT CREATE PROCEDURE TO app_user;

Service Account Setup

-- Service account with minimal privileges
CREATE USER service_account IDENTIFIED BY password
ACCOUNT UNLOCK;

-- Grant only necessary privileges
GRANT CREATE SESSION TO service_account;
GRANT SELECT ON specific_table TO service_account;

-- Optional: Create dedicated role
CREATE ROLE service_role;
GRANT CREATE SESSION TO service_role;
GRANT SELECT ON app_table TO service_role;
GRANT service_role TO service_account;

Bulk User Management

-- Script for multiple users
DECLARE
    TYPE user_array IS TABLE OF VARCHAR2(30);
    users user_array := user_array('USER1', 'USER2', 'USER3');
BEGIN
    FOR i IN users.FIRST..users.LAST LOOP
        EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || users(i);
        DBMS_OUTPUT.PUT_LINE('Granted CREATE SESSION to ' || users(i));
    END LOOP;
END;
/

Advanced Scenarios

Profile-Based Access Control

-- Check if user profile restricts sessions
SELECT username, profile FROM dba_users WHERE username = 'USERNAME';

-- Check profile limits
SELECT * FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = 'USERNAME')
AND resource_name IN ('SESSIONS_PER_USER', 'IDLE_TIME', 'CONNECT_TIME');

-- Modify profile if needed
ALTER PROFILE user_profile LIMIT SESSIONS_PER_USER 5;

Conditional Privilege Granting

-- Grant privilege only if user exists
DECLARE
    user_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO user_count
    FROM dba_users
    WHERE username = 'TARGET_USER';

    IF user_count > 0 THEN
        EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO TARGET_USER';
        DBMS_OUTPUT.PUT_LINE('CREATE SESSION granted to TARGET_USER');
    ELSE
        DBMS_OUTPUT.PUT_LINE('User TARGET_USER does not exist');
    END IF;
END;
/

Role Hierarchy Management

-- Create role hierarchy
CREATE ROLE basic_user_role;
CREATE ROLE power_user_role;
CREATE ROLE admin_user_role;

-- Grant basic privileges
GRANT CREATE SESSION TO basic_user_role;

-- Build hierarchy
GRANT basic_user_role TO power_user_role;
GRANT CREATE TABLE, CREATE VIEW TO power_user_role;

GRANT power_user_role TO admin_user_role;
GRANT CREATE USER TO admin_user_role;

-- Assign to users
GRANT basic_user_role TO regular_user;
GRANT power_user_role TO developer_user;
GRANT admin_user_role TO dba_user;

Troubleshooting Steps

Verify Connection Attempt

-- Test connection as the user
CONNECT username/password@database

-- If successful, check session info
SELECT user, sys_context('userenv', 'session_user') FROM dual;

Check for Account Issues

-- Check for locked accounts
SELECT username, account_status, lock_date
FROM dba_users
WHERE username = 'USERNAME'
AND account_status != 'OPEN';

-- Unlock if needed
ALTER USER username ACCOUNT UNLOCK;

Audit Connection Attempts

-- Enable login auditing
AUDIT CREATE SESSION;

-- Check audit trail
SELECT username, timestamp, action_name, returncode
FROM dba_audit_trail
WHERE username = 'USERNAME'
AND action_name = 'LOGON'
ORDER BY timestamp DESC;

Monitoring and Management

Privilege Monitoring Script

-- Monitor users without CREATE SESSION
SELECT u.username, u.account_status
FROM dba_users u
WHERE u.username NOT IN (
    -- Users with direct CREATE SESSION
    SELECT grantee FROM dba_sys_privs
    WHERE privilege = 'CREATE SESSION'
    UNION
    -- Users with CREATE SESSION through roles
    SELECT rp.grantee
    FROM dba_role_privs rp, dba_sys_privs sp
    WHERE rp.role = sp.grantee
    AND sp.privilege = 'CREATE SESSION'
)
AND u.username NOT IN ('SYS', 'SYSTEM', 'ANONYMOUS')
ORDER BY u.username;

Automated Privilege Assignment

-- Procedure to grant standard privileges to new users
CREATE OR REPLACE PROCEDURE grant_standard_access(p_username VARCHAR2) AS
BEGIN
    -- Check if user exists
    DECLARE
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count
        FROM dba_users
        WHERE username = UPPER(p_username);

        IF v_count = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'User does not exist: ' || p_username);
        END IF;
    END;

    -- Grant standard privileges
    EXECUTE IMMEDIATE 'GRANT CONNECT TO ' || p_username;
    EXECUTE IMMEDIATE 'GRANT RESOURCE TO ' || p_username;

    DBMS_OUTPUT.PUT_LINE('Standard access granted to ' || p_username);
END;
/

Access Review Report

-- Generate access review report
SELECT
    u.username,
    u.account_status,
    u.lock_date,
    u.expiry_date,
    CASE
        WHEN EXISTS (
            SELECT 1 FROM dba_sys_privs
            WHERE grantee = u.username
            AND privilege = 'CREATE SESSION'
        ) THEN 'DIRECT'
        WHEN EXISTS (
            SELECT 1 FROM dba_role_privs rp, dba_sys_privs sp
            WHERE rp.grantee = u.username
            AND rp.role = sp.grantee
            AND sp.privilege = 'CREATE SESSION'
        ) THEN 'ROLE-BASED'
        ELSE 'NO ACCESS'
    END as session_access
FROM dba_users u
WHERE u.username NOT LIKE '%$%'  -- Exclude system accounts
ORDER BY u.username;

Security Best Practices

Principle of Least Privilege

-- Create role-based access
CREATE ROLE app_readonly_role;
GRANT CREATE SESSION TO app_readonly_role;
GRANT SELECT ON app_table TO app_readonly_role;

CREATE ROLE app_readwrite_role;
GRANT app_readonly_role TO app_readwrite_role;
GRANT INSERT, UPDATE, DELETE ON app_table TO app_readwrite_role;

-- Grant appropriate role to users
GRANT app_readonly_role TO report_user;
GRANT app_readwrite_role TO app_user;

Password and Account Management

-- Create user with strong password policy
CREATE USER secure_user IDENTIFIED BY complex_password
PASSWORD EXPIRE
ACCOUNT UNLOCK;

-- Grant minimal required privileges
GRANT CREATE SESSION TO secure_user;

-- Set appropriate profile
ALTER USER secure_user PROFILE restricted_profile;

Emergency Procedures

Grant Emergency Access

-- Emergency access script (run as DBA)
-- Grant CREATE SESSION to locked-out user
ALTER USER emergency_user ACCOUNT UNLOCK;
GRANT CREATE SESSION TO emergency_user;

-- Temporary elevated access if needed
GRANT DBA TO emergency_user;  -- CAUTION: Very powerful

-- Remember to revoke after emergency
-- REVOKE DBA FROM emergency_user;

Bulk Access Restore

-- Restore access for multiple users
BEGIN
    FOR rec IN (
        SELECT username FROM dba_users
        WHERE account_status = 'LOCKED'
        AND username LIKE 'APP_%'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER USER ' || rec.username || ' ACCOUNT UNLOCK';
        EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || rec.username;
        DBMS_OUTPUT.PUT_LINE('Access restored for ' || rec.username);
    END LOOP;
END;
/

Related Errors

• ORA-00988: Missing or invalid password
• ORA-28000: Account is locked
• ORA-28001: Password has expired
• ORA-01017: Invalid username/password

Best Practices

1. Use role-based access control
2. Follow principle of least privilege
3. Regular access reviews and audits
4. Document privilege assignments
5. Monitor failed connection attempts
6. Use strong password policies

Troubleshooting Checklist

• Verify user exists in database
• Check account status (locked/expired)
• Confirm CREATE SESSION privilege
• Review role assignments
• Check profile restrictions
• Test connection with correct credentials
• Review audit logs for connection attempts
• Verify network connectivity