This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
dbi-users@isc.org
Subject: Re: Oracle password expiration
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Wed, 6 Sep 2000 21:25:51 +0100
On Wed, Sep 06, 2000 at 12:05:16PM -0500, Rob Fugina wrote:
>
> My DBA just started trying out Oracle password expiration, and one of
> our developers (a beginner in Perl) started encountering the following:
>
> Error: ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)
>
> Our DBA contacted Oracle and this is what she was told: "Oracle Support
> does not provide support for PERL.
Tip: Don't mention perl when talking to Oracle. Talk about an "advanced
application implemented using the Oracle OCI interface". Use trace
level 6 if they want to see a log of the OCI calls.
> However, I believe that the latest
> version of the driver added password change functionality. please upgrade
> to the latest version of driver, and try again."
Nope.
> I can't find anything about this in any DBD::Oracle documentation.
> Nor would I expect there to be an easy solution to this situation. Sure,
> you can change a user's password through DBI, but you have to be able
> to connect first, then issue "ALTER USER SCOTT IDENTIFIED BY TIGER".
> The matter of prompting a user for a new password and performing an
> atomic connect/password change is what would need to be addressed, yes?
>
> What to do?
Find the relevent part of the OCI docs (or ask Oracle to point you at it)
and I'll see what I can do. But it won't be soon as I'm away for the next
two weeks.
Tim.
===
Subject: Re: Oracle password expiration
From: Rob Fugina <robf@geekthing.com>
Date: Wed, 6 Sep 2000 16:25:08 -0500
On Wed, Sep 06, 2000 at 09:25:51PM +0100, Tim Bunce wrote:
> Find the relevent part of the OCI docs (or ask Oracle to point you at it)
> and I'll see what I can do. But it won't be soon as I'm away for the next
> two weeks.
I think I found the right part of the docs on my own, though it really lacks
context for me since I'm not really familiar with OCI. It's under "Password
and Session Management"...
-- begin quote --
Password Management
OCI provides the OCIPasswordChange() call to allow an OCI application
to modify a user's database password as necessary. This is particularly
useful if a call to OCISessionBegin() returns an error message or warning
indicating that a user's password has expired.
Following is the OCI call for changing a user's password:
OCIPasswordChange(service_handle, error_handle, user_name, user_name_len,
old_password, old_password_len, new_password, new_password_len, mode);
Like OCISessionBegin(), OCIPasswordChange() can be called only after a
server is attached, and the service handle has been set with the server
handle and the user session handle. The effect of OCIPasswordChange()
on a user session depends on whether or not the session is established
before the call:
If OCIPasswordChange() is called before a user session is created,
the old password is used to create the session. After the password
is changed and if the client has not requested that the session
to remain active (the mode parameter is not set to OCI_AUTH),
the session is terminated at the end of the call. In other words,
OCIPasswordChange() may be used to both establish a user session
as well as to change the password.
If OCIPasswordChange() is called after the user session is
established, the session remains active after the call, regardless
of how the mode is set.
See Also: For more information about this call and its parameters,
refer to the description of OCIPasswordChange().
-- end quote --
The "Comments" section of the OCIPasswordChange section says:
-- begin quote --
This call allows the password of an account to be changed. This call is
similar to OCISessionBegin() with the following differences:
If the user session is already established, it authenticates the
account using the old password and then changes the password to
the new password
If the user session is not established, it establishes a user
session and authenticates the account using the old password,
then changes the password to the new password.
This call is useful when the password of an account has expired and
OCISessionBegin() returns an error (ORA-28001) or warning that indicates
that the password has expired.
-- end quote --
Looks like exactly what we need, but it's not obvious to me immediately
how it should be hooked up with DBI/DBD...
Rob
===
Subject: Re: Oracle password expiration
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Thu, 7 Sep 2000 14:43:47 +0100
Okay, thanks. Here's a rough outline of how I'll probably do it...
(I'm explaining this here since I won't have time for a few weeks and
some kind soul may volunteer to implement it - it's not too hard) ...
Usage:
DBI->connect("dbi:Oracle:...", $user, $pass, {
ora_change_password => $newpass
});
Implementation:
Pick up the ora_change_password attribute in dbd_db_login6() using
DBD_ATTRIB_GET_SVP (defined in DBIXS.h) in a similar way to how
ora_init_mode attribute is handled now.
If OCISessionBegin_log_stat() fails and ora_change_password is
set then try OCIPasswordChange() to both change the password and
start a new session.
To be more polished it should check for the specific error using
OCIErrorGet_log_stat(), but that could wait.
Also, for an added bonus, if the OCISessionBegin_log_stat() succeeds
and the ora_change_password attribute is set then call OCIPasswordChange()
to change the password but retain the existing session.
Have fun!
===
From: mpm@thefriend.com [SMTP:mpm@thefriend.com]
Sent: Wednesday, August 23, 2000 6:27 PM
To:
Subject: Cost of prepare in oracle.
I'm in the process of re-writing most of our website in mod_perl.
Right now, we run the prepare when we need to run the statement.
With the switch to mod_perl, we could take the queries that are
prepared /bind_param/executed on every request and move the
prepare to an init area and move the finish to the disconnect area.
I'm wondering about the memory use and performance benifit of
doing such though. How costly is the prepare each time vs a
single prepare and having to store the statement handle?
===
Subject: Re: Cost of prepare in oracle.
From: Tim Bunce <Tim.Bunce@ig.co.uk>
Date: Tue, 5 Sep 2000 09:54:58 +0100
On Wed, Aug 23, 2000 at 06:36:28PM -0400, Steve Sapovits wrote:
>
> My experience is that it depends on the query. I've
> had a few that cost a decent amount of time to prepare
> and therefore benefit from the ability to prepare once
> and execute multiple times. For most of my queries it
> doesn't matter, but for those where I needed it, it did
> make a difference.
It also depends on the rate of churn in your server's SQL cache.
Use the supplied ora_explain tool to get a feel for that.
As with all such issues: try it yourself.
Tim.
===