In our instance we’re not using the seeded delivered password policies which can be activated by setting some profiles but we have a Java class in place describing a custom password policy. I blogged this in a earlier post: Signon Password Policies.
With a more secured password policy it might also happen that users forget their password and when using profile option Signon Password Failure Limit with a maximum number of attempts to login the user may lock himself out. The user status becomes LOCKED and a System Administrator has to reactivate the user login.
In table APPLSYS.FND_USER two columns are there which describes the encrypted password of the user.
- ENCRYPTED_FOUNDATION_PASSWORD
- ENCRYPTED_USER_PASSWORD
The encrypted foundation password contains the encrypted version of the APPS/APPLSYS password based on the user and the user password. The encrypted user password contains the encrypted version of the user password based on the APPS/APPLSYS password.
When a user locks himself out by multiple times entering the wrong password the password statusses become INVALID. The foundation and user encrypted version of the password will get a value of INVALID.
Based on the above we can built a small SQL statement to extract the users which are locked currently together with their possible HR information like name and employee number.
See the below SQL I created – as always change it to fit your requirement.
SELECT
FUSER.USER_NAME
, PER.FULL_NAME
, PER.EMPLOYEE_NUMBER
FROM
APPLSYS.FND_USER FUSER
, APPS.PER_PEOPLE_F PER
WHERE
FUSER.EMPLOYEE_ID = PER.PERSON_ID(+)
AND FUSER.ENCRYPTED_USER_PASSWORD = 'INVALID'
ORDER BY
FUSER.USER_NAME
Thanks. The sql is good.
How to unlock or remove locked?
Hi, good question! Check out the new post on how to unlock user accounts. Check it out here!