This discussion is archived
14 Replies Latest reply: Mar 4, 2013 3:08 PM by 994748 RSS

Changing oracle password from Apex

Haisa.M Newbie
Currently Being Moderated
Hello All,

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.

Thanks,

Atousa
  • 1. Re: Changing oracle password from Apex
    Earl Lewis Journeyer
    Currently Being Moderated
    >
    Hello All,

    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.

    Thanks,

    Atousa>
    Atousa,

    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.

    Earl
  • 2. Re: Changing oracle password from Apex
    Haisa.M Newbie
    Currently Being Moderated
    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.

    Atousa
  • 3. Re: Changing oracle password from Apex
    560577 Pro
    Currently Being Moderated
    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):
    declare
      v_ddl varchar2(1000);
    begin
      v_ddl := 'alter user ' || v('APP_USER') || ' identified by ' || :P123_PASSWORD;
      execute immediate v_ddl;
    end;
    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.).
  • 4. Re: Changing oracle password from Apex
    Haisa.M Newbie
    Currently Being Moderated
    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.

    Thanks,

    Atousa
  • 5. Re: Changing oracle password from Apex
    Haisa.M Newbie
    Currently Being Moderated
    No idea!!!!!!
  • 6. Re: Changing oracle password from Apex
    878811 Newbie
    Currently Being Moderated
    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
  • 7. Re: Changing oracle password from Apex
    InoL Guru
    Currently Being Moderated
    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 questions
    I 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.
  • 8. Re: Changing oracle password from Apex
    866990 Explorer
    Currently Being Moderated
    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
    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
    /
    Log on as the utility user
    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>
    /
    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.
    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 :-)

    Robin

    Edited by: RobbieNerve on 6-dec-2011 13:13
  • 9. Re: Changing oracle password from Apex
    866990 Explorer
    Currently Being Moderated
    Did this help you?
  • 10. Re: Changing oracle password from Apex
    Haisa.M Newbie
    Currently Being Moderated
    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!

    Thanks,
  • 11. Re: Changing oracle password from Apex
    Haisa.M Newbie
    Currently Being Moderated
    Hi Robin,

    I tried your way several times but I get nothing. I don't know where I am doing wrong. Is there anyway that you can create an example in oracle web site that I can see it. Or give me step by step solution that I can understand where I am doing wrong. I really appreciate your help.
  • 12. Re: Changing oracle password from Apex
    jwellsnh Expert
    Currently Being Moderated
    Atouse,

    I have a small application on my workspace I can share. Send me an email and I will a temporary account for you to export that application for use in your own space.

    Jeff
  • 13. Re: Changing oracle password from Apex
    user9315786 Newbie
    Currently Being Moderated
    can you export and email me your application?

    Thanks
  • 14. Re: Changing oracle password from Apex
    994748 Newbie
    Currently Being Moderated
    I would definitely be interested in any documentation related to changing the password when logging into the Apex. Can you please email it to me?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points