All users created in Oracle 19c are SYS user-CodePudding

I'm trying to create a new user in the normal way:

CREATE USER test IDENTIFIED BY test;

After that, when I'm trying to connect to this user with normal privileges I get the following error:

ORA-01017: invalid username/password; logon denied

If I change the privileges to sysdba I can connect successfully, but if I run show user the output is always SYS.

C:\Users\96560>sqlplus test/test@ecc

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 14:33:40 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
C:\Users\96560>sqlplus test/test@localhost/ecc

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 14:33:57 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
C:\Users\96560>sqlplus test/test@ecc as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 14:34:19 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"
SQL>

CodePudding user response：

I guess it is about 19c; it depends on how you created that user because it can be

a common user (which is created if you're connected to the container database and current container must be root); optionally, while creating a user, you can specify CONTAINER = ALL, or
a local user (in which case you have to be connected to a PDB, pluggable database); optionally, in that case, you can specify CONTAINER = CURRENT

It looks as if you've created a local user. In that case, specify PDB (I don't know its name; check TNSNAMES.ORA, but - presume it is PDB1):

sqlplus test/test@pdb1

If it won't work (probably because there's no entry in tnsnames.ora, try

sqlplus test/test@host_name/pdb1

(host_name is name of the database server; if database runs on your PC, that might be localhost)

CodePudding user response：

@Littlefoot Please check the following result:

C:\Users\96560>sqlplus test/test@ecc

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 14:33:40 2022 Version 19.3.0.0.0

ERROR: ORA-01017: invalid username/password; logon denied

Enter user-name: C:\Users\96560>sqlplus test/test@localhost/ecc

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 14:33:57 2022 Version 19.3.0.0.0

ERROR: ORA-01017: invalid username/password; logon denied

Enter user-name: C:\Users\96560>sqlplus test/test@ecc as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 14:34:19 2022 Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0

SQL> show user USER is "SYS" SQL>

CodePudding user response：

When you do:

sqlplus test/test@ecc as sysdba

you are affected by this part of the documentation:

When you connect with the SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

From SQL*Plus the actual username and password you supply aren't validated - those can be anything, or more usually just /.

The new user isn't created as SYS, the way you are connecting just means you are connecting as SYS rather than as that user.

So, if you can't connect with:

sqlplus test/test@ecc

sqlplus test/test@localhost/ecc

then either the the TNS alias and server are pointing to a different database than the user was created in - which could be a CDB vs. PDB issue - or perhaps more likely you didn't create it with the username and/or password you think.