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.
When trying to connect to an Oracle database, you might run into this error:
ORA-01045: user xxx lacks CREATE SESSION privilege; logon denied
The reason is that the username you are using to connect lacks the CREATE SESSION system privilege.
So to fix it, you need to grant the permission:
GRANT CREATE SESSION TO user_name;
And you should be able to login to your database by now.
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Download TablePlus for Mac.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
Learn the cause and how to resolve the ORA-01045 error message in Oracle.
Description
When you encounter an ORA-01045 error, the following error message will appear:
- ORA-01045: user name lacks CREATE SESSION privilege; logon denied
Cause
You tried to log into Oracle with a username that has not been granted the CREATE SESSION privilege.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Have the Oracle DBA grant the username the CREATE SESSION privilege.
Option #2
Try logging into Oracle with a different username that has the CREATE SESSION privilege.
By
Atul Rai |
Last Updated: July 5, 2022
Previous Next
In the Oracle database, if you have created a new user and tried to log in with a newly created user, you might get an error ORA-01045: user xxx lacks CREATE SESSION privilege; logon denied error.
This normally happens when you didn’t give CREATE SESSION privilege while creating the user. To grant the CREATE SESSION privilege follow the below steps.
Step 1: Open the Oracle’s Run SQL Command Line terminal. And connect as sysdba.
Step 2: Enter your system dba username and password that you have created during the installation of the database(if you didn’t create any, try login with SYS as a username and empty(no password) password).
SQL> connect as sysdba
Enter user-name: sys
Enter password:
Connected.
SQL>
Step 3: Execute the GRANT CREATE SESSION TO user_name; query.
SQL> GRANT CREATE SESSION TO root;
Grant succeeded.
SQL>Step 4: Now try to connect with the new user again. You will be able to connect.
References
- Oracle-CREATE USER
- Overview, Editions, and Features of Oracle Database
- Overview and Architecture of Oracle Database Server
Hi Friends,
This is general error getting from Oracle 10g database. It calls as ORA-01045 that lacking of create session privilege.
— Advertisement —
Reason of ORA-01045:
From Oracle 10g, connect role doesn’t contain create session privilege.
Solution of ORA-01045:
If you granted connect role to any user and database was upgraded then you should need to give create session privilege explicitly.
— Advertisement —
Sometimes we are getting this same ORA-01045 during datapump usage. The same solution is applying to the same condition also.
During database administration, we are getting so many mysterious errors and the situation of those errors is unknown. To resolution any ORA ERROR and managing Database, approach database services of DBAMETRIX. You will get maximum benefits in minimum charges.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
— Advertisement —
