Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-65096: Invalid common user or role name

user8708553Aug 31 2019 — edited Sep 14 2019

Hello everyone,

Hope everyone is doing well!

I just downloaded and installed Oracle Database 19c (Release 19.0.0.0.0).

And when I ran :

     create user john_doe identified by abc123;

It gave me :

     ORA-65096: Invalid common user or role name

What did I do incorrectly?  Or did I miss something in the installation step?

Thank you so much,

cmh

This post has been answered by Solomon Yakobson on Aug 31 2019
Jump to Answer

Comments

Frank Kulash

Hi,

user8708553 wrote:

Hello everyone,

Hope everyone is doing well!

I just downloaded and installed Oracle Database 19c (Release 19.0.0.0.0).

And when I ran :

create user john_doe identified by abc123;

It gave me :

ORA-65096: Invalid common user or role name

What did I do incorrectly? Or did I miss something in the installation step?

Thank you so much,

cmh

See: issue creating user in 12c database

Do you want to create the user in the container database or in a pluggable database?

user8708553

I logged on as SYSTEM.  I would like to create a user in whatever database

that I am allowed to create.

Thank you,

cmh

Solomon Yakobson
Answer

As Frank noted, database you are logged in to is container database. You need to login to pluggable database in that container and then create regular user. Only common users can be created in container database and their names must be prefixed with C## (unless you change prefix). I suggest you read documentation on types of users.

SY.

Marked as Answer by user8708553 · Sep 27 2020
user8708553

I am guessing that I am in the default database.  And I would like

to create a user in it.

cmh

user8708553

I tried to connect to my pluggable database.

But I do not have a username and password.

Could you please help?  Thank you.

cmh

mathguy

Starting from Oracle 12.1, it is no longer enough to know who you logged in as. You need to know also where you logged in.

For various very good reasons, Oracle introduced a more complicated setup than just "one database" you connect to. It now has a "multi-tenant" structure; there is a container database (where, in general, you should NOT create new users, unless you have a very good reason to, and you understand exactly what is happening), and one or more "pluggable" database - all part of the same installation. You should create a user like JOHN_DOE in a PLUGGABLE database, not in the CONTAINER.

When you log in as SYSTEM, you are most likely connecting to the container database. (As I will show below, SYSTEM can also log in to a pluggable database, but that is probably not what you did.)

Your next step is to find out what databases you have on your system. From there, decide where the new user should be created. Connect to the correct pluggable database (as SYSTEM) and create the user.

SQL> connect system

Enter password:

Connected.

SQL> column pdb_name format a30

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME                       STATUS

------------------------------ ----------

ORCLPDB                        NORMAL

PDB$SEED                       NORMAL

Explanation: Your output may be different from mine. PDB$SEED is a pluggable database template; it should always be present in the output, and you shouldn't touch it. On my system, I have a "user" pluggable database, ORCLPDB. I created it (and I named it ORCLPDB) when I installed Oracle (in my case it's version 12.2, but I believe it is similar in later versions).

OK, so if I must create a new user, I should do that in ORCLPDB. First I must connect to THAT database as SYSTEM, and then create the user: (note - don't blindly write ORCLPDB; use an actual PDB name you identified in the previous step!)

SQL> connect system@orclpdb

Enter password:

Connected.

SQL> create user john_doe identified by abc123;

User created.

SQL> connect john_doe@orclpdb

Enter password:

ERROR:

ORA-01045: user JOHN_DOE lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

As you can see, I connected (as SYSTEM) to ORCLPDB, and I created the user there. That was successful.

Then, if JOHN_DOE needs to connect to the database, he must connect to ORCLPDB, as I showed in my example. Now you will run into a new issue: SYSTEM created the new user, but it didn't grant any privileges to the new user; not even the simplest of privileges, which is the CREATE SESSION privilege. The user can log in, but he really can't, because logging in starts a new session, and I (SYSTEM) haven't granted him the right to do that.

You should take it from here - decide what privileges must be granted to this user. Log in to ORCLPDB as SYSTEM as shown above, and do the remaining needed work from there.

user8708553

Hello mathguy,

Thank you so much for your reply.  I really appreciate that.

I ran "select pdb_name, status from dab_pdbs".  And "ORCLPDB"

was listed in the return.

At the SQL> prompt, I tried :

           SQL> connect SYSTEM@ORCLPDB;

Next, I entered my password.  It gave me :

ORA-12154: TNS:could not resolve the connect identifier specified

Would you by any chance know what caused this?

Thank you,

cmh

mathguy

user8708553 wrote:

Hello mathguy,

Thank you so much for your reply. I really appreciate that.

I ran "select pdb_name, status from dab_pdbs". And "ORCLPDB"

was listed in the return.

At the SQL> prompt, I tried :

SQL> connect SYSTEM@ORCLPDB;

Next, I entered my password. It gave me :

ORA-12154: TNS:could not resolve the connect identifier specified

Would you by any chance know what caused this?

Thank you,

cmh

That is a longer and more complicated question - and, importantly, quite unrelated to your original question; you may be better off closing this question and starting another thread. However, this is a frequently asked question; before you start yet another thread about it, it may be better to see what has already been written about it. Also, take a look at this "sister" community (forum):   You may be better off asking your question(s) there.

The answer depends on several things: your Oracle version, your operating system, your user interface (the program you use to connect to the database, such as SQL*Plus or SQL Developer), etc.

I am a relative novice myself, and I had similar problems when I installed my system (for learning and practice). I remember the attached blog article from @"EdStevens" was very useful; it may be a bit old (not addressing things to do with more recent Oracle versions), but it may still be enough to understand the issues and to figure out what you need to do.

https://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/

1 - 8

Post Details

Added on Aug 31 2019
8 comments
16,495 views