Expired Oracle Password

[RobertSBoyle]

Hi Guys,

Hopefully you can help me ASAP with this one.

I am running CF 4.5 and Oracle 8i, we are using the Oracle password system to authenticate users. We are having a problem when the password has expired. How do we change it via CF?

The ORACLE command I know is "Alter user blah identified by blah", but this will not work if the password has already expired. Is there something else I need to use?

Thanks in advance,
R.

 

[Guest_imported]

Robert,

You have the right syntax, but you'll need DBA authority to change an expired password. I guess that CF doesn't pass through a prompt to change the old password with a new one once it has expired. I don't have anything set for password_life_time in my users' profile. Don't suppose you can tolerate a non-expiring password though ?

I think also that I have used just one database connection with a hardcoded password in the datasource definition. So the users id isn't used within the Oracle DB security anyway and I had a separate table that held the user and password for application specific access.

What you could do is to use the 'created' date for the user from the dba_users table, and the password_life_time for the users profile (see dba_profiles). Then you could build a table for every user with these values and you could compare the current date and if say the password was within 5 days of expiring you could write a prompt yourself from CF which would take a new password and run an 'Alter user xyx identified by abc'. And then set a new 'created' date.

Any Help ?

Richard Cooke

 

[RobertSBoyle]

Thanks Richard.

I have actually created a user with whose sole rights are to connect and to alter user with admin, when the password is expired then this user will log in and change the users password via an Oracle package.

We actually store the users in a table at the moment and the security is handled by the application. I think this is messy and doesn't allow us to use fully the Oracle functionality (for instance Fine Grain Access Control) that comes with Oracle.

The problem with the expire within 5 days idea (which I did think of too ;o) ) is that if a user doesn't log in for those 5 days before it is due to expire, when the do log in it will have already expired. Thankfully we sorted it using the different user as described above. And by catching the Oracle errors associated with changing the password (same password as previous etc) in the CF code we can give meaningful feed back to the user.

Thanks again.
Robert