We have oracle form which users can log in with their user name and password. But some times users forget their password an they should ask the administrator to reset it for them. I was wondering if I can make the life easier for them.
I thought we can create a link with the name of " forgot password" in oracle form which opens apex form. Then we ask user some challenge questions and if the answers are right then the Apex can run the execution job in oracle to reset the password and then ask user to enter new password. Is any body ever done this? Is this even possible? Please help me on that. I have no idea where I should start.
Of course, add in to your page any other safeguards and validations such that you determine who the user is by challenge questions, etc., any necessary validations (like have a P123_PASSWORD field and a P123_RETYPE_PASSWORD field and make sure they're matching before proceeding so the user knows they didn't fat-finger their new password, etc.).
declare v_ddl varchar2(1000); begin v_ddl := 'alter user ' || v('APP_USER') || ' identified by ' || :P123_PASSWORD; execute immediate v_ddl; end;
Then we ask user some challenge questionsI think it is easier to reset the database password with some random value and expire this password immediately. Email this password to the user. The next time the user logs in in your Forms app, Forms will ask the user for a new password.
Log on as the utility user
create user utility identified by <password> default tablespace sysaux temporary tablespace temp / grant connect, resource to utility / grant alter user to utility / grant create public synonym to utility /
Now, if you execute this package from apex, after the secret questions have been answered correctly, the password will be set to default 'changeme' for the provided username.
CREATE OR REPLACE PROCEDURE RESET_USER (v_user_id varchar2) AUTHID DEFINER -- execute as the owner of the procedure IS v_default_pass varchar2(30); -- variabele default password BEGIN v_default_pass := 'changeme'; -- set default password IF v_user_id in ('SYSTEM','SYS','SOME_OTHER_IMPORTANT_OWNER') THEN raise_application_error(-20001, 'system users cannot be altered'); ELSE -- reset password EXECUTE IMMEDIATE 'ALTER USER ' || v_user_id || ' IDENTIFIED BY ' || v_default_pass ; -- unlock user EXECUTE IMMEDIATE 'ALTER USER ' || v_user_id || ' ACCOUNT UNLOCK' ; END IF; END; / create public synonym reset_user for utility.reset_user / grant execute on reset_user to <users> /