This content has been marked as final. Show 14 replies
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.
This is certainly possible. I call it "self serve password reset". Original, huh? In any case, I did mine with a custom authentication scheme, where I have my username/password information in a table in my application schema. I would only attempt to do this with a custom authentication scheme, where you have control over the username/password.
If you're using the Apex authentication scheme then I'm not going to be much help. I haven't tried it and not familiar with the limits and capabilities of Apex APIs to change password. And if you're using an LDAP authentication then you're probably not going to be allowed by your LDAP admins to send password changes back into the LDAP system.
If you're interested in the custom authentication approach let me (us) know and we will try to help you out.
Thanks for your reply. There is no problem going with oracle form or Apex. Because I usually work with Apex I thought may be that is easier for me. But if I can make it through oracle that is great. Please let me know where I should start because I have no idea about it and I have to create a form for challenge questions too.
Thank you in advance.
Haven't tried it but couldn't this be simply done in Apex by a PL/SQL process on submit like this (assuming that APP_USER and the database user are one and the same):
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;
I haven't tried it yet, but your solution seems to change the password with the new password but what I need is
first, reset the password in oracle (this is not apex user name and password it is oracle form user name and password). and then let them insert new password. My problem is, I don't know how to reset that first and then ask them to insert new password.
What I was thinking in the first place was that, I can create a link from oracle form (where they want to log in but they don't know their password) to apex, the first thing they see in the apex page is challenge questions(which I can create them easily), and after they finished answering challenge questions and clicked on submit, if those answers are correct the system reset the password automatically to something like(12345) and let the user know that now your password is this and try to enter your new password to change the default. Then when they close that apex page and go back to oracle form and try with their new password, everything works fine for them.
I have no idea how I can do this.
I will appreciate your help.
Do you have an authentication method in place? I've developed something similar that uses LDAP authentication. Once the individual logs onto the site, their username is written to the :APP_USER variable.
If you don't have a way to filter users automatically based upon login, you may want to use a drop-down menu that lists all of the available account names. Once someone chooses their username, you could then display the list of challenge questions associated with that specific account. If you have multiple applications that could benefit from this approach, I would suggest passing primary keys for each application via hidden fields on the form so the list of usernames would be relevent to that application.
Edited by: BJones on Dec 5, 2011 12:12 PM
Why do you want to combine Oracle Forms and Apex like that? (I assume you are referring to Forms here). Are the Forms users database users?
If I were you I would build this functionality in Forms, so that the users have only one application.
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.
This is possible :-)
You have to create a procedure under a user with alter user privileges, preferrably your DBA has to create (or already has) a schema for utilities
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> /
You could modify the procedure to accept both a user_id and a password so the user can reset it immediately.
You cannot grant the execute privilege on the reset_user to a role all the users already have, because in PL/SQL only direct grants are active. Role grants will not work!
Hope this helps you :-)
Edited by: RobbieNerve on 6-dec-2011 13:13
Sorry for the late repose. I couldn't log in to this site for several days and I don't know why this was happening to me. Anyway answering your question, I am still having problem. The solution you provided I think can work with oracle form without using apex (if I am not wrong) and I tried it the way that it can work in oracle form but I don't know why it does nothing. Nothing actually changes. Could you please describe it more a little bit that I can understand where I am doing wrong!