when 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 user connected successfully if I run show user the output is always SYS. SQL> show user USER is "SYS"
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
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:
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
or
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.