How to Resolve ORA-01045: user lacks CREATE SESSION privilege; logon denied

ORA-01045

ORA-01045 means that the user who tried to connect to the database has no privilege to connect to. Therefore, the database refused the user to logon.

SQL> conn hr/hr@orcl
ERROR:
ORA-01045: user hr lacks CREATE SESSION privilege; logon denied

Solutions

Since the error message indicated that the user has no CREATE SESSION privilege, so we can just follow the instruction to grant CREATE SESSION privilege to the user by privileged user. In this case, we grant it by SYS.

Single Granting

When only one user needs the privilege, we can perform single granting.

Normal Databases

For normal databases (non-CDB) which are not using multitenant architecture, we can connect the database by SYS like this.

SQL> conn / as sysdba
Connected.
SQL> grant create session to hr;

Grant succeeded.

Pluggable Databases (PDB)

For pluggable databases, we should additionally change the current container in order to operate granting CREATE SESSION privilege.

SQL> conn / as sysdba
Connected.
SQL> alter session set container=ORCLPDB;

Session altered.

SQL> grant create session to hr;

Grant succeeded.

There're more ways to connect to a PDB via OS authentication without password.

Batch Granting

If a bunch of new users need the same privilege, we can grant it to them in one command.

SQL> grant create session to hr,oe,sh;

Grant succeeded.

Please use comma to delimit multiple users.

Connection Test

We connect again.

SQL> conn hr/hr@orcl
Connected.

At times, users may get ORA-01017: invalid username/password; logon denied when they connect to the database at the first time. DBA should guide them through.

Please note that, the predefined role CONNECT that we used to grant it to a new user contains only CREATE SESSION system privilege since 11g. That is to say, using CONNECT instead of CREATE SESSION also solves ORA-01045, but it has no secret ingredient anymore.

To grant some proper privileges for new accounts, you can check the complete list of 19c system privileges.

Multitenant Issue

In a multitenant environment (12c or later), we should additionally focus on what user we are being granting. Is it a local or common user? If the grantee is a common user, maybe you should consider to grant it with CONTAINER=ALL in order to properly manage commonly and locally granted privileges.

In reality, we see ORA-01017: invalid username/password; logon denied in a multitenant environment more often than this error.