Wednesday, July 3, 2013

ORA-01017: invalid username/password; logon denied

I got a user call where he was not able to login into the Production database. I query the database for that user and found that his account was neither locked nor expired. Then I realize might be the user's credential is in case sensitive which he is not able to recall.
I change the database case sensitive parameter which helps that user to log in successfully into the database.

I simulated that scenario into my test machine for you.
I'm using user 'scott' whose password is 'tiger'(in lower case).
Now I'll try to login into the database in upper,middle,mixed to check the database behaviour.


SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users;

USERNAME ACCOUNT_STATUS  LOCK_DATE  EXPIRY_DA
-------- --------------- ---------  ---------
SYSTEM   OPEN
SYS      OPEN
DBSNMP   OPEN
SYSMAN   OPEN
SCOTT    OPEN

...
....
...

[oracle@oracle GG]$ sqlplus scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
[oracle@oracle GG]$ sqlplus scott/tiGER
ERROR:
ORA-01017: invalid username/password; logon denied
[oracle@oracle GG]$ sqlplus scott/Tiger
ERROR:
ORA-01017: invalid username/password; logon denied
[oracle@oracle GG]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 3 19:27:31 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn /as sysdba
Connected.

SQL> show parameter case_sensitive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=FALSE scope=both;
System altered.

Now try to login the database by user 'scott'  credential.
SQL> conn scott/TIger
Connected.
SQL> conn scott/TigER
Connected.
SQL> conn scott/TIGER
Connected.

Now user scott can login into the database without facing issue with case sensitivity in his password.  

We can also create a oracle password file without having in case sensitive attribute.
[oracle@oracle ~]$ orapwd file=orapwdb1 password=Oracle ignorecase=Y

Thanks for your time. :)

No comments:

Post a Comment