RE: notification of password expiration?

[Vergara, Michael (TEM)]

Oh man...have I been dealing with this lately!   We had a similar problem.  We had a security audit come in and ask us how often the users changed passwords.  The honest answer of 'to my knowledge they never have' did not sit well with them.  Go figure.   So first we set up user profiles.  We control the aging of accounts and format of passwords through these profiles.  Now...how to notify the users?  We have instances ranging from 7.3.4 through 8.1.6 (and soon 8.1.7).   We created an 8.1.6 instance for administrative purposes on one of our central servers.  In this database is a table called SEND_EMAIL, which records TO, FROM, SUBJ, TEXT, and SYSDATE.  When a row is inserted in this table, a trigger is fired which calls a stored PL/SQL procedure which uses UTL_SMTP to send an e-mail.  This table can be used for any e-mails from the database - not just password messages.  One bonus is that I now have a record of any messages sent from the database.   At the client databases, I created a synonym and database link which point to the SEND_EMAIL table.  Daily, I have a PL/SQL procedure scan the EXPIRY_DATE field of the DBA_USERS view.  When a user's EXPIRY_DATE is within 14 days of SYSDATE, I insert a row with a canned message into the SEND_EMAIL synonym.  The record goes over the link, into the table, fires the trigger, executes the procedure, and shazzam an e-mail goes to the user.   I tried to genericize my message so I could store the same procedure on several remote databases.  The problem with that is I now get tons of e-mail saying "What is the ABC database?  When did I get an account there?  What machine is it on?  Is that the PeopleSoft database or the Data Warehouse?" and stuff like that, so I've learned to store the generic message from the script, and then go in later and modify it for the specific database.  <GRIPE>It seems incredible that a user who uses a database on a daily or at least periodic basis does not even know the name of the database, but oh well...that's life.</GRIPE>   HTH, Mike --- =========================================================================== Michael P. Vergara              | I’ve got a PBS mind in an MTV world Oracle DBA                      | Guidant Corporation             | -----Original Message----- From: Paul Sheahan [mailto:[EMAIL PROTECTED]] Sent: Friday, July 06, 2001 11:01 AM To: Multiple recipients of list ORACLE-L Subject: notification of password expiration? Hello all,   Can anyone share any ideas on this?         We have a procedure in our Oracle databases that forces a user to change there password after x number of days.  This password procedure also forces them to use num-alpha characters.  Our lead Oracle admin turned off the procedure because Oracle was not notifying users when their password was going to expire and once it did it would not let them in.  I was hoping that with 8i there would be some improvements to the way Oracle handled expiring passwords.  Does anyone know or use a workaround for this? We would like to set password expiration of 60 days on Oracle accounts but they need some type of notification so they aren't locked out.... Thanks, Paul