Sometimes we want to change passwords through the backend in EBS. Here is how to change user password in oracle apps from backend.
Login to the apps Schema and then execute this PLSQL block.
set serveroutput on
DECLARE
change_pass BOOLEAN;
user varchar2(40);
pass varchar2(40);
BEGIN
user := '<user name>';
pass := '<password>';
change_pass := fnd_user_pkg.changepassword(user,pass);
IF change_pass then
dbms_output.put_line('Password was changed');
ELSE
dbms_output.put_line('Password change failed');
END IF;
END;
/
Let’s take an example
sqlplus apps/<pass>
set serveroutput on
DECLARE
change_pass BOOLEAN;
user varchar2(40);
pass varchar2(40);
BEGIN
user := 'ANONYMOUS';
pass := 'password123';
change_pass := fnd_user_pkg.changepassword(user,pass);
IF change_pass then
dbms_output.put_line('Password was changed');
ELSE
dbms_output.put_line('Password change failed');
END IF;
END;
/
It will throw up the message according to execution. If successful “Password was changed” and not successful “Password change failed”.
If you want to know the error also, you can execute this PLSQL block to change the password
Set serveroutput on
DECLARE
change_pass BOOLEAN;
user varchar2(40);
pass varchar2(40);
l_result varchar2(30000);
BEGIN
user := 'ANONYMOUS';
pass := 'password123';
change_pass := fnd_user_pkg.changepassword(user,pass);
IF change_pass then
dbms_output.put_line('Password was changed');
ELSE
l_result := fnd_message.get();
dbms_output.put_line('Password change failed');
dbms_output.put_line( 'Error stack = ' || l_result );
END IF;
END;
/
Password change may fail because of the password requirement defined in the system. You can use the below sql to get the password complexity requirement set in EBS
select fnd_profile.value('SIGNON_PASSWORD_LENGTH'),
fnd_profile.value('SIGNON_PASSWORD_HARD_TO_GUESS'),
fnd_profile.value('SIGNON_PASSWORD_CUSTOM'),
fnd_profile.value('SIGNON_PASSWORD_NO_REUSE'), from dual
We can validate the password using the below sql
select fnd_user_pkg.VALIDATELOGIN('USER','PASSWORD') from dual;
We can also use fnd_web_sec to change the password
set serveroutput on
declare
l_result varchar2(30000);
userid number;
BEGIN
l_result := apps.fnd_web_sec.change_password('','');
dbms_output.put_line( 'Result = ' || l_result );
if l_result = 'N'
then
l_result := fnd_message.get();
dbms_output.put_line( 'Error stack = ' || l_result );
end if;
END;
We can validate the password using the below sql
Select fnd_web_sec.validate_login('USER','PASS')
From dual;
I hope you find this article on how to change user password in oracle apps from backend useful. Please do provide the feedback
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 for forms in oracle apps r12 : How to enable trace in Self service page, Oracle forms, Concurrent Program, running concurrent Program