RE: notification of password expiration?

<SPAN

class=656282717-06072001>Oh man...have I been dealing with this lately!
<SPAN

class=656282717-06072001> 
<SPAN

class=656282717-06072001>We had a similar problem.  We had a security audit come in and ask us
<SPAN

class=656282717-06072001>how often the users changed passwords.  The honest answer of 'to my
<SPAN

class=656282717-06072001>knowledge they never <FONT color=#0000ff face="Courier New" size=2>have' did not sit well with them.  Go figure.
<SPAN

class=656282717-06072001> 
<SPAN

class=656282717-06072001>So first we set up user profiles.  We control the aging of accounts
<SPAN

class=656282717-06072001>and format of passwords through these profiles.  Now...how to notify
<SPAN

class=656282717-06072001>the users?  We have instances ranging from 7.3.4 through 8.1.6 (and
<SPAN

class=656282717-06072001>soon 8.1.7).
<SPAN

class=656282717-06072001> 
<SPAN

class=656282717-06072001>We created an 8.1.6 instance for administrative purposes on one of our
<SPAN

class=656282717-06072001>central servers.  In this database is a table called SEND_EMAIL, which
<SPAN

class=656282717-06072001>records TO, FROM, SUBJ, TEXT, and SYSDATE.  When a row is inserted in
<SPAN

class=656282717-06072001>this table, a trigger is fired which calls a stored PL/SQL procedure
<SPAN

class=656282717-06072001>which uses UTL_SMTP to send an e-mail.  This table can be used for
<SPAN

class=656282717-06072001>any e-mails from the database - not just password messages.  One bonus
<SPAN

class=656282717-06072001>is that I now have a record of any messages sent from the database.
<SPAN

class=656282717-06072001> 
<SPAN

class=656282717-06072001>At the client databases, I created a synonym and database link which
<SPAN

class=656282717-06072001>point to the SEND_EMAIL table.  Daily, I have a PL/SQL procedure
<SPAN

class=656282717-06072001>scan the EXPIRY_DATE field of the DBA_USERS view.  When a user's
<SPAN

class=656282717-06072001>EXPIRY_DATE is within 14 days of SYSDATE, I insert a row with a
<SPAN

class=656282717-06072001>canned message into the SEND_EMAIL synonym.  The record goes over
<SPAN

class=656282717-06072001>the link, into the table, fires the trigger, executes the procedure,
<SPAN

class=656282717-06072001>and shazzam an e-mail goes to the user.
<SPAN

class=656282717-06072001> 
<SPAN

class=656282717-06072001>I tried to genericize my message so I could store the
<SPAN

class=656282717-06072001>same procedure on
<SPAN

class=656282717-06072001>several remote databases.  The problem with that is I now get tons of
<SPAN

class=656282717-06072001>e-mail <FONT color=#0000ff face="Courier New" size=2>saying "What is the ABC database?  When did I get an account
<SPAN

class=656282717-06072001>there?  What machine is it on?  Is that the PeopleSoft database or the
<SPAN

class=656282717-06072001>Data Warehouse?" <FONT color=#0000ff face="Courier New" size=2>and stuff like that, so I've learned to store the
<SPAN

class=656282717-06072001>generic message <FONT color=#0000ff face="Courier New" size=2>from
<SPAN

class=656282717-06072001>the script, and then go in later and modify it for
<SPAN

class=656282717-06072001>the specific <FONT color=#0000ff face="Courier New" size=2>database. 
<SPAN

class=656282717-06072001><GRIPE><FONT color=#0000ff face="Courier New" size=2>It seems incredible that a user who uses
<SPAN

class=656282717-06072001>a <FONT color=#0000ff face="Courier New" size=2>database on
a <SPAN
class=656282717-06072001>daily or at <FONT color=#0000ff face="Courier New" size=2>least periodic basis does not even know the<FONT color=#0000ff face="Courier New" size=2>
<SPAN

class=656282717-06072001>name of the <FONT color=#0000ff face="Courier New" size=2>database, but
<SPAN

class=656282717-06072001>oh well...that's life.<FONT color=#0000ff face="Courier New" size=2><SPAN
class=656282717-06072001></GRIPE>
 
<SPAN

class=656282717-06072001>HTH,
<SPAN

class=656282717-06072001>Mike

---


<FONT color=#0000ff face="Courier New" 

size=2>===========================================================================
Michael P. 
Vergara              
| I&#8217;ve got a PBS mind in an MTV world
Oracle 
DBA                      
|
Guidant 
Corporation             
|

  <FONT face=Tahoma 
  size=2>-----Original Message-----From: Paul Sheahan 
  [mailto:psheahan_at_snet.net]Sent: Friday, July 06, 2001 11:01 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  notification of password expiration?
  
  Hello all,
   
  Can anyone share any ideas on this?
  <FONT face=Arial 
  size=2>        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