Home » RDBMS Server » Security » Username in lowercase authentication issues (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Username in lowercase authentication issues [message #681613] |
Sun, 09 August 2020 04:48 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Hi all,
I want to create a lowercase user with a lowercase password, explicitly show Oracle which username and which password I want to connect with SQL*Plus:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL>
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user "test"
2 identified by "test"
3 default tablespace USERS
4 temporary tablespace TEMP
5 profile DEFAULT;
User created.
SQL> grant DBA to "test";
Grant succeeded.
SQL> grant UNLIMITED TABLESPACE to "test";
Grant succeeded.
So far so good, I managed to create it, I'll also try to connect from already connected SQL*Plus session:
SQL> conn "test"/"test"@mydb
Connected.
SQL>
However, if I am logging out to the COMMAND PROMPT of the client and do the same, it doesn't authenticate me ( Client is 10g ) :
SQL> quit
C:\Documents and Settings\myosuser>
C:\Documents and Settings\myosuser>sqlplus "test"/"test"@mydb
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 9 12:41:15 2020
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
What am I doing wrong, why is it not connecting from the OS, but connects fine with "connect..." from sql*Plus ?
Thanks,
Andrey
|
|
|
|
Re: Username in lowercase authentication issues [message #681618 is a reply to message #681616] |
Sun, 09 August 2020 05:44 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 09 August 2020 13:15
You first have to set SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10 in server sqlnet.ora file.
Thanks for the reply.
My server SQLNET.ora file contents:
# sqlnet.ora Network Configuration File: /.../.../oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
~
~
~
~
~
~
~
I am opening a new command prompt window from my 10g client and trying to connect again:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\myuser>sqlplus "test"/"test"@mydb
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 9 13:40:55 2020
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
Still same error.
By the way, I can connect from inside SQL*Plus even if I am not authenticated anywhere:
C:\Documents and Settings\myuser>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 9 13:42:06 2020
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn "test"/"test"@mydb
Connected.
SQL>
Any more ideas of what I can try/check ?
|
|
|
Re: Username in lowercase authentication issues [message #681622 is a reply to message #681613] |
Sun, 09 August 2020 06:58 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
Andrey_R wrote on Sun, 09 August 2020 04:48Hi all,
I want to create a lowercase user with a lowercase password, explicitly show Oracle which username and which password I want to connect with SQL*Plus:
And why would you want to force the username to lower-case? This just goes against the grain of how oracle treats case-sensitivity. And if you have sec_case_sensitive_logon=TRUE you don't have to double-quote your passwords.
SQL> create user beetle identified by beetle;
User created.
SQL> grant create session to beetle;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle:oklacity$ sqlplus beetle/beetle
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 9 06:57:35 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle:oklacity$ sqlplus beetle/BEETLE
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 9 06:57:46 2020
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
|
|
|
Re: Username in lowercase authentication issues [message #681623 is a reply to message #681622] |
Sun, 09 August 2020 07:06 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
EdStevens wrote on Sun, 09 August 2020 14:58Andrey_R wrote on Sun, 09 August 2020 04:48Hi all,
I want to create a lowercase user with a lowercase password, explicitly show Oracle which username and which password I want to connect with SQL*Plus:
And why would you want to force the username to lower-case? This just goes against the grain of how oracle treats case-sensitivity. And if you have sec_case_sensitive_logon=TRUE you don't have to double-quote your passwords.
I was asked to do so by developers. After a short discussion I was asked to "just do it lowercase" because they said "that is correct for the application" so I did as requested.
This is not the issue, nevertheless.
My question is why I cannot, or how do I connect to sqlplus from the OS with lowercase user.
If it's such a bad practice - why is it allowed?
If it's allowed - IMO it should be connectable. Don't you think ???
|
|
|
|
Re: Username in lowercase authentication issues [message #681625 is a reply to message #681624] |
Sun, 09 August 2020 07:34 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 09 August 2020 15:18
You are on Windows, double-quotes are removed by DOS shell, escape them:
sqlplus \"test\"/\"test\"@mydb
Seems that this is the behavior on both Linux and Windows too.
This doesn't work from windows xp, 10g client, 12c database:
C:\Documents and Settings\myuser>sqlplus "test"/"test"@mydb
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 9 15:24:45 2020
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
Neither does it work from Linux (Red Hat 4.8.5-28 ) , 11g client, same 12c database:
[oracle@myhost ~]$ sqlplus "test"/"test"@mydb
SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 9 08:28:09 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
But when we escape it works, on both
Windows:
C:\Documents and Settings\myuser>sqlplus \"test\"/\"test\"@mydb
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 9 15:29:45 2020
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
Linux:
[oracle@myhost ~]$
[oracle@myhost ~]$ sqlplus \"test\"/\"test\"@mydb
SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 9 08:30:35 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sun Aug 09 2020 08:29:45 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
So seems like both scenarios need this type of handling to lowercase names when connecting with SQL*Plus from the OS
Mystery solved.
Thank you very much Michel Cadot and EdStevens for your assistance.
Andrey R
|
|
|
Re: Username in lowercase authentication issues [message #681628 is a reply to message #681625] |
Sun, 09 August 2020 11:03 |
|
Michel Cadot
Messages: 68716 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Andrey_R wrote on Sun, 09 August 2020 14:34Michel Cadot wrote on Sun, 09 August 2020 15:18
You are on Windows, double-quotes are removed by DOS shell, escape them:
sqlplus \"test\"/\"test\"@mydb
Seems that this is the behavior on both Linux and Windows too.
...
Andrey R
Yes, and with *sh, single-quote are also removed:
$ echo 'some'-'thing'
some-thing
This is not the case with Windows/DOS.
And with *sh you don't need to escape, you can enclose your connection between single-quotes:
$ echo "test"/"test"@mydb
test/test@mydb
$ echo '"test"/"test"@mydb'
"test"/"test"@mydb
Something you can't do with DOS/Windows
|
|
|
Re: Username in lowercase authentication issues [message #681629 is a reply to message #681628] |
Sun, 09 August 2020 11:15 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 09 August 2020 19:03
And with *sh you don't need to escape, you can enclose your connection between single-quotes:
$ echo "test"/"test"@mydb
test/test@mydb
$ echo '"test"/"test"@mydb'
"test"/"test"@mydb
Something you can't do with DOS/Windows
Indeed, it works with single quotes from Linux shell:
[oracle@myhost ~]$ sqlplus '"test"/"test"@mydb'
SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 9 12:12:24 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sun Aug 09 2020 08:30:35 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
Thanks.
|
|
|
Goto Forum:
Current Time: Thu Nov 21 09:56:19 CST 2024
|