Helpful replies, thanks.
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@xxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Yong Huang
Sent: Thursday, September 17, 2015 11:20 AM
To: post.ethan@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx
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
...
--
//www.freelists.org/webpage/oracle-l
