SQL to Check for Locked User Accounts
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
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
