Guest User and its password in 11i

Guest user password is very important for Oracle Applications to function properly. Self-service login used this password to verify the user password. Selfservice will not work if this password is incorrect. But direct form login will still work if the password is incorrect.

•        It is stored in the following location in 11i:

$FND_TOP/secure/

GUEST_USER_PWD profile

•        It is an application user not a DB user while APPLSYSPUB is a DB user.

•        Oracle Apps never uses this for login, this is only used to obtain apps information.

•        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 can be used 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.

•        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. We can write a script to find out APPS schema from Guest User. 

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'; 

Now query the following 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_P WD'),'/')-1)

FROM dual)

);