Oracle database newbie about ORA-01045 error [closed]

Question

Closed. This question is not about programming or software development. It is not currently accepting answers.

This question does not appear to be about a specific programming problem, a software algorithm, or software tools primarily used by programmers. If you believe the question would be on-topic on another Stack Exchange site, you can leave a comment to explain where the question may be able to be answered.

Closed 10 months ago.

Improve this question

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.

Just for clarity. Are you just trying to create a user to connect to? Does it need to be a common user? — Dom G
– Dom G, Commented Feb 10 at 9:37

thatjeffsmith · Accepted Answer · 2025-02-10 13:39:29Z

1

The 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

answered Feb 10 at 13:39

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2312787 Feb 10 at 15:02

I did GRANT CONNECT, RESOURCE, CREATE SESSION TO C##TEST; but, unfortunately, it doesn't work as expected

thatjeffsmith Feb 10 at 15:03

i showed my code, and results. Please do the same so we can compare.

user2312787 Feb 10 at 15:21

SQL> create user c##test2 identified by oracle; User created. SQL> connect c##test2/oracle ERROR: ORA-01045: Login denied. User C##TEST2 does not have CREATE SESSION privilege. Help: docs.oracle.com/error-help/db/ora-01045 Warning: You are no longer connected to ORACLE. SQL> conn system/phc4tznbla2sBTc Connected. SQL> grant connect to c##test2; Grant succeeded. SQL> connect c##test2/oracle Connected.' Within SQL plus it writes "Connected", but I can't use this credentials with DBeaver or SQL Developer

user2312787 Feb 10 at 16:32

This doesn't work as well:CREATE USER C##TEST3 IDENTIFIED BY TEST DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; CREATE ROLE C##TEST3_role; GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE TRIGGER to C##TEST3_role; GRANT C##TEST3_role TO C##TEST3;

user2312787 Feb 11 at 11:57

Thank you for your support! Finally solved: alter session set container = FREEPDB1; CREATE USER test IDENTIFIED BY password; grant connect, resource to test;

|

Collectives™ on Stack Overflow

Oracle database newbie about ORA-01045 error [closed]

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related