I can't connect to my brand new empty Oracle database from DBeaver or SQL Developer with just made new DB user.
I just get started to use Oracle Database 23ai, with SQL Plus logged in as SYSTEM and run following:
CREATE USER C##TEST IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, CREATE SESSION TO C##TEST;
@d:\\project\\schema.sql
I have tried to give right explicitly with GRANT CREATE SESSION TO C##TEST;, but still receive
ORA-01045: Login denied. User C##TEST does not have CREATE SESSION privilege.
If I try to connect without DBeaver with conn C##TEST/password (SQL Plus) it works well.
I read some topics here, Google and asked Perplexity, but still can't solve the issue.
I can't connect to my brand new empty Oracle database from DBeaver or SQL Developer with just made new DB user.
I just get started to use Oracle Database 23ai, with SQL Plus logged in as SYSTEM and run following:
CREATE USER C##TEST IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, CREATE SESSION TO C##TEST;
@d:\\project\\schema.sql
I have tried to give right explicitly with GRANT CREATE SESSION TO C##TEST;, but still receive
ORA-01045: Login denied. User C##TEST does not have CREATE SESSION privilege.
If I try to connect without DBeaver with conn C##TEST/password (SQL Plus) it works well.
I read some topics here, Google and asked Perplexity, but still can't solve the issue.
Share Improve this question edited 3 hours ago Mark Rotteveel 109k226 gold badges155 silver badges219 bronze badges asked 12 hours ago user2312787user2312787 192 bronze badges New contributor user2312787 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 1- Just for clarity. Are you just trying to create a user to connect to? Does it need to be a common user? – Dom G Commented 5 hours ago
1 Answer
Reset to default 0The direct answer to your question is straightforward, using an administrator account such as SYS or SYSTEM, grant the CONNECT role C##TEST
SQLcl: Release 24.3 Production on Mon Feb 10 08:33:29 2025
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Mon Feb 10 2025 08:33:27 -05:00
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
SQL> create user c##test identified by oracle;
User C##TEST created.
SQL> connect c##test/oracle
Connection failed
USER = c##test
URL = jdbc:oracle:thin:@localhost:1521/free
Error Message = ORA-01045: Login denied. User C##TEST does not have CREATE SESSION privilege.
https://docs.oracle.com/error-help/db/ora-01045/
Warning: You are no longer connected to ORACLE.
SQL> connect system/oracle@localhost:1521/free
Connected.
SQL> grant connect to c##test;
Grant succeeded.
SQL> connect c##test/oracle
Connected.
SQL>
However. For most if not all work in your database, especially from an application (vs administrative) perspective, you do not want to be operating in the Container Database, but one of the pluggable databases.
SQL> connect system/oracle
Connected.
SQL> show pdbs
CON_ID CON_NAME OPENMODE RESTRICTED
_________ _____________ _____________ _____________
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
4 MARIN READ WRITE NO
5 PDB_SAMPLE READ WRITE NO
SQL>
If I connect as a regular database user account to say, FREEPDB1, that is where I would expect to create my tables, views, stored procedures, etc.
SQL> connect hr/oracle@localhost:1521/freepdb1
Connected.
SQL> select * from hr.employees fetch first 3 rows only;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID HOBBIES
______________ _____________ ____________ __________ _________________ ____________ __________ _________ _________________ _____________ ________________ __________
100 Steven King SKING 1.515.555.0100 17-JUN-13 AD_PRES 24000 90
101 Neena Yang NYANG 1.515.555.0101 21-SEP-15 AD_VP 17000 100 90
102 Lex Garcia LGARCIA 1.515.555.0102 13-JAN-11 AD_VP 17000 100 90
See more about the relationship between the Container database and it's one or more pluggables (as well about common (C##) users, here: Introduction to Mulitenant Architecture, Oracle Docs