Skip to Main Content
  • Questions

  • Security policy of password expiry causes problems

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jay.

Asked: May 17, 2002 - 12:00 pm UTC

Last updated: May 19, 2006 - 12:20 pm UTC

Version: 9.0.1

Viewed 1000+ times

You Asked

Tom

I have been asked to implement a security policy on a system that will have many users. One of the requirements of the system is that the password is expired on creation and after every 90 days, prompting the user for a new one - fairly standard. The issue is that the Application used to access the database is Business Objects and I have been told by them that it can't handle this situation. I believe the Oracle error is "ORA-28001: the password has expired". If the application was clever, it would check for this Oracle error and pop up a little form to retrieve the relevant data etc. Can I trap this exception at logon and handle it in some other way - perhaps send an email to the user and an administrator to get them to change the password, or perhaps get the system to auto change the password and email the new logon details to the user and the Administrator. Do you have any other ideas?

Once again, thanks in anticipation of your invaluable advice!

Cheers
Jay


and Tom said...

Sure, you can have an on-logon trigger that would inspect the EXPIRY_DATE in the dba_users table.

You might create a table like this:

create table user_notified ( username varchar2(30), expiry_date date );

and in the logon trigger, query dba_users to find if this user is set to expire. If they are -- check this other table to see if they were already notified and if not

o insert a row
o schedule a DBMS_JOB job that will send them an email (using utl_smtp)
o commit

That way, you won't send them more then one email and the insertion of the row and sending of the email will be a "transaction" (either both happen or neither happens -- you can rely on dbms_job to send the email in the background and if it fails, you'll be notified of that via dba_jobs AND the alert log)



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I'll give that a go

Jay, May 22, 2002 - 7:30 am UTC

I'll have a go at that - just another though (relevant to my app) Since Busness Objects uses an Oracle database as it's repository, I could look at updating the BO password as part of the process, thereby removing the requirement for an administrator to manually change the BO password at the same time.

Thanks
Jay

Catch - 22 Situation or Is it ?

Anand, July 13, 2004 - 11:08 am UTC

Hi Tom...

I am using the Logon function to logon to the database in Oracle Forms 6i and the user is a database user.
If the password expires. how to handle that?
If I were using sql, if the password expires... it will prompt for the old and new passwords there itself.......
but it wont do the same in forms, it throws the error 0RA-23008.

I can Trap it, but later how to proceed. I cant change his password, because before doing that I need to login......which I cant do.....(where as in sql prompt it is different)

Please tell me how to handle this situation.

Thanks as Always

Anand

Tom Kyte
July 13, 2004 - 12:28 pm UTC

i don't use forms -- but if you let forms do the logon -- I know it puts up a dialog letting you change your password.

you'd have to capture the error and do the same I suppose -- you'd want to goto otn.oracle.com under the discussion forums for developer and ask there how you might best do this.

Password of DBlink expired

Ram, May 19, 2006 - 10:36 am UTC

Is there any way of changing the dblink password, which has expired or about to expire because of security policy, without recreating the dblink ?

Tom Kyte
May 19, 2006 - 12:20 pm UTC

not that I am aware of, there is no alter database link command

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library