SQL & PL/SQL

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?

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

that I am allowed to create.

Thank you,

cmh

I tried to connect to my pluggable database.

But I do not have a username and password.

Could you please help?  Thank you.

cmh

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.

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

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/