Re: Automatic Password Reset

I like a web service approach but not sure I want to deal with the there. The user base in mind all have basic SQL skills. This is also something that will be built into an existing tool kit so all the required parts are already deployed and there, I just need to hook a few things together into a new feature. Maybe someday I will look into APEX development, have a nice weekend folks.

On Thu, Sep 17, 2015 at 12:01 PM, Gary Gruber <GGruber_at_rpmdirectllc.com> wrote:

> We built something like that here using APEX and PL/SQL. The APEX parts
> are really lightweight and could be probably be substituted with another
> technology. It does not require the user to verify their identity in part
> because no action is taken when they request the change. It was inspired
> by the way APEX self-service password changes work.
>
> It works roughly like this:
>
> Part 1, the request.
>
> # User goes to the password reset APEX application which contains a field
> for them to enter their password, the database name (you are here) and a
> "Submit" button.
> # Upon "Submit", we:
> ## verify the user's email address is in our table associating database
> user account to email address
> ## if so, add a row to a password request table with a randomly generated
> string (dbms_random, possibly also hashed IIRC) and a timestamp and session
> details about who made the request. If not, display an error message that
> we don't know your email address.
> ## Send an email to the specified address containing a specially
> constructed URL to the APEX application that contains this random string,
> along with instructions and reminders about the password complexity rules.
>
> Part 2, the change.
>
> # User receives the email, clicks the URL, bringing them to the 'real'
> password change page. The random string is parsed off the URL by the APEX
> application.
> # The password change page reiterates the complexity rules and has two
> fields for the user (enter password, confirm password).
> # When user submits this page, the password and the random string are
> handed off the PL/SQL API which does a few things:
> ## Verify the random string is Valid. That is, it exists in the request
> table from part 1 (or its hash does; I forget) AND it is not more than 15
> minutes old.
> ## Verify the password meets the complexity requirements
> ## Assuming the prior conditions are met, unlock the account if it was
> locked and then change the password to what the user specified.
> ## If any verification fails the user must start over and is redirected to
> the landing page for Part 1.
>
> The APEX application and user-email table sit in one application schema.
> The queue table containing the random string along with the code to hit the
> verify_password function and do the password change sit in another schema
> that only the DBA has access to. The PL/SQL that the APEX application uses
> makes calls to PL/SQL APIs in the DBA schema. The requests are retained
> and updated in some way upon successful or failed change so the DBA can
> monitor use of the application. Obviously the password is never stored
> anywhere.
>
> Admittedly there is some risk in not verifying the user beyond having a
> valid email address entered, but the risk is mitigated by the expiring
> request, the random string in the URL and policy-dictated screen lock after
> n minutes. Management considered those risks to be acceptable.
>
> HTH
>
> Gary Gruber
> RPM Direct
> www.rpmdirectllc.com
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Yong Huang
> Sent: Thursday, September 17, 2015 11:20 AM
> To: post.ethan_at_gmail.com
> Cc: oracle-l_at_freelists.org; JEREMY.SHEEHAN_at_nexteraenergy.com
> Subject: Re: Automatic Password Reset
>
> The process you outlined probably has a user check in the first step.
> Right? If anyone could log into pwd_reset without checking who he is, he
> could reset anybody else's password, even though this guy won't receive the
> email with the new pin.
>
> We have a database running a legacy client-server type of application,
> with hundreds of users. Per company policy, these users' passwords expire
> every 90 days. We built a web page backed by an OCI program for users to
> reset their passwords when the password expires (or at any time he wants to
> reset). That solves the problem that most of these users don't have or
> don't know how to use Sqlplus and the problem that JDBC thin driver based
> SQL Developer users can't reset expired password. The site requires input
> of current password, expired or not. If the user forgets his password, he
> has to contact DBAs. But having this site up still significantly cuts down
> on the calls to us. If we had time and resource, we could build a complete
> password self-service site that allows users to reset their password even
> if he forgets it. That sure requires input of some verification such as
> make and model of his first car, favorite teacher... I don't see this part
> in your
> process.
>
> Yong Huang
>
> ------- original message --------
>
> Anyone out there have an automatic way for users to have their account
> unlocked and passwords reset?
> My thoughts are to use a generic account, something like "PWD_RESET" which
> can call a procedure which will do something like the following
>
> # Assume there is already a table with user/email reference...
>
> # User logs into pwd_reset account.
> # User runs exec password_reset('MYUSER_NAME');
> # Email with "pin" is sent to email on file.
> # User runs exec password_pin(PIN); , this returns the temporary pwd.
> # User logs in with temporary pass
> ...
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l