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.
[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.
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. :)
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
...
....
...
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