CODEWITHSUNDEEP
oracle-databasecloud
Chad Lomax
Chad Lomax

Reputation: 113

How do I connect as newly created user through SQL Developer on Oracle Autonomous?

I have created a user(new_user) with a password. I have granted create session to that user. I still cannot connect to database as new_user. ORA-01017. I can connect as admin.

  1. I can see the user(new_user) in the other users menu in SQL Developer.
  2. When I go to create a table like CREATE TABLE new_user.SALES, I get ORA-01918: user 'NEW_USER' does not exist.
  3. I can right-click and create a table.

I know I have the right password.

Upvotes: 0

Views: 397

Answers (1)

Littlefoot
Littlefoot

Reputation: 143003

Sounds very much like the double quotes issue.

Here's how it is supposed to be done:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL> create user new_user identified by new_user
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant create session, create table to new_user;

Grant succeeded.

SQL> connect new_user/new_user
Connected.
SQL> create table test (id number);

Table created.

This is what I suspect you did: drop the old user first:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> drop user new_user cascade;

User dropped.

SQL>

Now, start over. Pay attention to all double quotes in the following code:

SQL> create user "new_user" identified by new_user
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant create session, create table to new_user;
grant create session, create table to new_user
                                      *
ERROR at line 1:
ORA-01917: user or role 'NEW_USER' does not exist


SQL> grant create session, create table to "new_user";

Grant succeeded.

SQL> connect new_user/new_user
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect "new_user"/new_user
Connected.
SQL> create table new_user.test (id number);
create table new_user.test (id number)
*
ERROR at line 1:
ORA-01918: user 'NEW_USER' does not exist


SQL> create table "new_user".test (id number);

Table created.

SQL>

See? If you created it using double quotes, every time you reference it, you must use double quotes.

I suggest you get rid of them (double quotes) when working with Oracle. That includes users, table names, column names, procedure names, everything.

Upvotes: 1

Related Questions