Aware Oracle 11g, login is case sensitive

During the years, Openbravo has run on top of different Oracle database versions: Oracle 9i, Oracle 10g, etc. and now Oracle 11g. While this is an additive change for the Community Edition and Openbravo Basic Network, which continue to support Oracle 10g R2 and PostgreSQL (8.1.4 or 8.3.5) as well, Oracle 11g is now embedded in Openbravo SMB Network One for 2.40.

Of course, and I am not a database administrator neither a developer though, from my honest opinion many new interesting functionalities and bug fixing are included in this new release: database catching functionalities, virtual columns, etc. up to 482 new characteristics.

As always happens, many new features require us to change the way we usually do things, for example: Oracle 11g is case sensitive when
login. What does this mean? You could spend the whole morning trying to access to your database (even locking your database user) if you don’t realize Oracle 11g is now case sensitive…

ORA-01017: invalid username/password; logon denied

When I went through, I learnt the below:

1-Oracle 11g is case sensitive

This means, you need to introduce your database user and password with the correct case. To make sure how your database is configured, using
SQL*Plus as database system user:

SQL> SHOW parameter sec_case_sensitive_logon;

If you want to disable/enable this function (it is enabled by default), using SQL*Plus as database system user:

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE or TRUE;

This is a new parameter on this release, not an existing parameter’s new default value.

2- sqlcase can change your password when creating a new user

Furthermore, it is important to realize there is another parameter that can change your new database user’s password:

SQL> SHOW SQLCASE;
sqlcase MIXED

sqlcase accepts three different values: UPPER (changes password to uppercase), LOWER (to lower case) MIXED (keeps the password as it is).

Depending this parameter’s value, when creating a new database user, it changes the password’s case. And this can also confuse yourself and avoid to success the login.

3- Unlocking the database user

The third action I did, was unlocking the locked database user

Also as database system user:

SQL> ALTER USER username ACCOUNT LOCK or UNLOCK;

I hope it helps, and you don’t spend the time I spent with this little issue…