Clarity: LAST_LOGGED_IN_DATE not updated when login user name is not an exact match to the literal string stored in the database

Description:

If a user logs into the Clarity application and does not enter the exact literal string that matches the literal string stored in the CMN_SEC_USERS.USER_NAME field, the CMN_SEC_USERS.LAST_LOGGED_IN_DATE will not reflect the login date/time stamp or the value is NULL.

Steps to Reproduce:

1. Retrieve the last logged in date information for a user, such as the Clarity Administrator user by executing the following query within your database and make a note of the date

         SELECT ID, USER_NAME, LAST_LOGGED_IN_DATE
         FROM CMN_SEC_USERS
         WHERE USER_NAME = 'admin'

2. Launch your Clarity application
   
   
3. On the Clarity Login page, enter the user name as 'admin' [all in lower case] and the appropriate password
   
   
4. Log out of Clarity
   
   
5. Execute the query identified in Step 1. Notice that the LAST_LOGGED_IN_DATE has changed as expected
   
   
6. On the Clarity Login page, enter the user name as 'ADMIN' or any combination of mixed case and the appropriate password
   
   
7. Log out of Clarity
   
   
8. Execute the query identified in Step 1.

Expected: The CMN_SEC_USERS.LAST_LOGGED_IN_DATE should reflect the latest successful login.
Actual: The CMN_SEC_USERS.LAST_LOGGED_IN_DATE has not been updated.

Solution:

WORKAROUND:

Login using the same literal string that is stored in the CMN_SEC_USERS.USER_NAME field

Or

Edit the file CLARITY_HOME\ META-INF\security\pmd\securityPMD.xml
Section: security.updateLoginDetails

Change the statement
from

update CMN_SEC_USERS set LAST_LOGGED_IN_DATE = ? where user_name = ?

to

update CMN_SEC_USERS set LAST_LOGGED_IN_DATE = ? where UPPER(user_name) = UPPER(?)

STATUS/RESOLUTION:

CLRT-27914 is a duplicate to CLRT-12011 which is resolved in Clarity 12.1.0.
If you are currently using Clarity 12.0.6, you can request and implement the latest GENERIC HOTFIX. For more information read TEC522707.

Keywords: CLARITYKB, CLRT-27914, CLRT-26934, CLRT-12011, clarity8open, clarity1210resolved, access, data, account history.