Here in this post,I am decoding all the facts about Guest User password in 11i/R12
–Guest user password is very important for the functioning of Application SSWA.
Self service login used this password to verify the user password. Self service will not come up
if this password is incorrect.Direct form login will still work if the password is incorrect
-It is stored in following places in 11i
GUEST_USER_PWD oracle profile option
–It is a application user not a Database user while APPLSYSPUB is a database user. So Guest user information is stored in FND_USERS table
–11i/R12 never use this for login …this is used to obtain apps info
–It should be consistent on both the places,otherwise application will not work.
—FNDCPASS also make use of this password for changing the password.
–Following command could be use to check it correctness
select apps.fnd_web_sec.validate_login(‘GUEST’,’ORACLE’) FROM DUAL;
–Guest user password cannot be changed through FNDCPASS. Most appropriate way to change it through user define form.You can get into User define form through forms login
–It should never be end dated
–Following query could be use to obtain the guest user profile
SELECT upper(fnd_profile.value(‘GUEST_USER_PWD’)) FROM dual;
–All the guest user and password manipulation is done through FND_WEB_SEC package stored in apps schema
–ATG RUP 6 introduces new hash password scheme for application password.
Following are some of the queries which are very useful
create or replace function test(key in varchar2,value in varchar2) return varchar2 as language java name 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
Query this to the find apps schema password from the Guest user
SELECT( SELECT test( UPPER((SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)) ,a.encrypted_foundation_password) FROM dual ) AS apps_password FROM fnd_user a WHERE usertable.user_name LIKE upper( (SELECT substr(fnd_profile.value('GUEST_USER_PWD'),1,instr(fnd_profile.value('GUEST_USER_PWD'),'/')-1) FROM dual) )
How to change Guest user password from Backend
GUEST USER PASSWORD CHANGE
set serveroutput on declare l_result varchar2(30000); userid number; BEGIN l_result := fnd_web_sec.CHANGE_GUEST_PASSWORD('GUEST','<appspassword>'); 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;