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 Go to next message
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 #681616 is a reply to message #681613] Sun, 09 August 2020 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You first have to set SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10 in server sqlnet.ora file.



Re: Username in lowercase authentication issues [message #681618 is a reply to message #681616] Sun, 09 August 2020 05:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Andrey_R wrote on Sun, 09 August 2020 04:48
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:
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 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
EdStevens wrote on Sun, 09 August 2020 14:58
Andrey_R wrote on Sun, 09 August 2020 04:48
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:
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 #681624 is a reply to message #681623] Sun, 09 August 2020 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You are on Windows, double-quotes are removed by DOS shell, escape them:
sqlplus \"test\"/\"test\"@mydb
Re: Username in lowercase authentication issues [message #681625 is a reply to message #681624] Sun, 09 August 2020 07:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Andrey_R wrote on Sun, 09 August 2020 14:34
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.
...
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 Go to previous message
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.
Previous Topic: Licence free Advanced Security
Next Topic: Role Based Access for User Management
Goto Forum:
  


Current Time: Thu Mar 28 05:09:08 CDT 2024