Useful Queries on oracle EBS password expiration/Policy/Settings

Here are some important questions and queries for oracle EBS password expiration, password complexity, profiles options

How to Stop the Force Password Reset on Creation of User Account in Oracle EBS

When a new EBS user is created, the User is supposed to change the password of the account upon the first login. There are cases when we don’t want that to happen. Let see how to stop the force password reset on the user’s first log in to the application after the creation of an e-business suite user.
After the creation of  the user from the Define Users forms, the following SQL statement can be used to disable the force password reset on the user’s first login

set serveroutput on
declare
l_user_name varchar2(2000);
begin
l_user_name := 'XYZ';
fnd_user_pkg.updateuser(x_user_name =>
l_user_name, X_OWNER => 'SEED', x_password_date =>sysdate);
end;
/

On the above SQL statement, replace ‘XYZ’ with the username created on the Define Users forms. For example, for user OEXPERT

set serveroutput on
declare
l_user_name varchar2(2000);
begin
l_user_name := 'OEXPERT';
fnd_user_pkg.updateuser(x_user_name =>
l_user_name, X_OWNER => 'SEED', x_password_date =>sysdate);
end;
/

This basically updates the password_date column in FND_USER to sysdate

How To Check Last Password Change Date For Users in Oracle EBS

select USER_NAME, TO_CHAR(PASSWORD_DATE, 'DD-MON-RR HH24:MI:SS') from FND_USER;

If you are interested in a particular user

select USER_NAME, TO_CHAR(PASSWORD_DATE, 'DD-MON-RR HH24:MI:SS') from FND_USER where USER_NAME='&1';

How to increase Password Expiration for Certain user

1. Launch the user define form by navigating to System Administrator -> Security -> Users.

2. Set the ‘Password Expiration’ Days to an expected value.

What are oracle ebs password profile options

Signon Password Case: It defines the case for the user password. Default is insensitive
Signon Password Failure Limit: It defines the number of failed attempts allowed
Signon Password Hard To Guess: This defines the password to have complexity so that it is hard to guess. Here are the rules defines as per Oracle
The password contains at least one letter and at least one number
The password does not contain repeating characters.
The password does not contain the username.
Signon Password Length: This defines the password Length
Signon Password No Reuse: This is set to the number of days that must pass before a user is allowed to reuse a password.
Signon Password Custom: This is used if your company security policy is not met with the above standard site profiles described above and you want to define your own password scheme (validated by custom Java code) in a custom Java class. This can be defined if you have a more advanced and complex password value requirement

Recommended Values
Signon Password Case: Sensitive
Signon Password Failure Limit:5
Signon Password Hard To Guess: Yes
Signon Password Length:8

You can check the current Value with the sql query

SQL> column PASS_COMPLEXITY format a20
SQL> column PASS_CUSTOM format a10
SQL> column PASS_POLICY_NOREUSE format a20
SQL> column PASS_FAILURE_LIMIT format a20
SQL> column PASS_LENGTH format a20
SQL>select fnd_profile.value('SIGNON_PASSWORD_LENGTH') PASS_LENGTH,
fnd_profile.value('SIGNON_PASSWORD_HARD_TO_GUESS') PASS_COMPLEXITY,
fnd_profile.value('SIGNON_PASSWORD_CUSTOM') PASS_CUSTOM,
fnd_profile.value('SIGNON_PASSWORD_NO_REUSE') PASS_POLICY_NOREUSE, fnd_profile.value('SIGNON_PASSWORD_FAILURE_LIMIT') PASS_FAILURE_LIMIT from dual;

How to force all Applications users to change their password at the next login

1. Starting in RUP4 Patch 4676589 ATG RUP 4, the following script to expire all passwords in the fnd_user table is $FND_TOP/patch/115/sql/AFCPEXPIRE.sql. It can be executed from SQL*Plus or as a Concurrent Program:

sqlplus -s APPS/ @AFCPEXPIRE.sql
or
Submit a concurrent request: CP SQL*Plus Expire FND_USER Passwords

2. This script sets the fnd_user.password_date to null for all users which causes all user passwords to expire. It can also be run as a SQL*Plus concurrent program. The user will need to create a new password upon the next login.?

Hope you like this content on the oracle EBS password expiration

Related Articles
FNDCPASS : FNDCPASS & AFPASSWD is the utility used to change the apps schema, Oracle EBS schema ,and user password in Oracle EBS all versions
Guest User password in 11i/R12 : Check out how to troubleshoot Guest User password in 11i/R12, how to change the guest user password and how to check it
oracle apps dba interview questions : You should not miss these 60 awesome oracle apps dba interview questions.Must read to succeed in interviews and jobs. Download also available
APPLSYSPUB schema : applsyspub schema is the public schema in Oracle EBS which is used first while connecting to Oracle Forms and Oracle OAF pages.
oracle apps queries : This page contains the very useful and practical Top 30 Useful oracle apps queries for APPS DBA to help in day to day administration activities
how to enable trace in oracle apps r12 : How to enable trace in Self service page, Oracle forms, Concurrent Program, running concurrent Program
How To Set The Password Expiration Field To xx Days As Default? (Doc ID 758036.1)