• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Useful Queries on oracle EBS password expiration/Policy/Settings

Useful Queries on oracle EBS password expiration/Policy/Settings

September 21, 2020 by techgoeasy 1 Comment

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

Table of Contents

  • How to Stop the Force Password Reset on Creation of User Account in Oracle EBS
  • How To Check Last Password Change Date For Users in Oracle EBS
  • How to increase Password Expiration for Certain user
  • What are oracle ebs password profile options
  • How to force all Applications users to change their password at the next login

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.
  1. 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

  1. 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)

Filed Under: Oracle, Oracle Ebuisness Suite Tagged With: fnd_user_pkg

Reader Interactions

Comments

  1. Kevin McCabe says

    February 8, 2022 at 2:22 am

    I was searching for a way to keep user from having to reset password upon first logon. Your example of updating existing user was helpful. I was able to use same parameter on the create user api to achieve same results when creating. fnd_user_pkg.CreateUser.

    Thank you!

    Kevin

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • ORA-28007: the password cannot be reused
  • What is crontab in Linux
  • How to change apps password in EBS R12.2
  • How to find weblogic version in Unix & Windows
  • How to use RMAN Catalog Command

Copyright © 2022 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us