Setting up automatic e-mail notification of expiring passwords

We are using Oracle 8i. We want to create a database trigger, or something similar, which will send an e-mail automatically to users one week before the expiration date of their passwords. How is this possible? Is there some package available inside Oracle called SENDMAIL or somthing like that, or some Oracle API, or Java? Is there some documentation about it and examples? How do you create database-level triggers? I know how to create table-level, but I want to create database-level trigger for this. Thanks.

    Requires Free Membership to View

    Login

It makes more sense to create a job which does this function that you are seeking. This way, you can run the job on some frequency (daily?) and find out which users passwords expire soon. Triggers require an event to happen before the body of the trigger "fires", or runs. So which event do you want to fire on? Database logon? If this is the case, then every time they log on with only one week before their password expires, they will get an e-mail notification. This may irritate your users.

I like the idea of a scheduled, daily batch job to determine if their password will expire, and if so, e-mail the user a notification. To do this, you will use a few things. One, the DBA_USERS view will show you when a user's password is set to expire in the EXPIRY_DATE column. Two, the supplied UTL_SMTP package can be used to e-mail someone right from the database. Three, the UTL_SMTP package does require a SMTP gateway to send the mail from. Four, you'll need a table to store the user's e-mail address. In my example, we'll call this table (email_address).

You can code this quite easily in PL/SQL as follows: 

DECLARE
   -- Cursor of those accounts whose password 
   -- expires in seven days or less
   CURSOR c1 IS SELECT
u.username,u.expiry_date,e.address
      FROM dba_users u, email_address e
      WHERE u.username=e.username
      AND expiry_date-sysdate<=7;
   username VARCHAR2(50);
   email    VARCHAR2(250);
   expire_date DATE;
   -- Who is sending the email?
   sender    VARCHAR2(40) := 'dba@host.dom';
   -- What is the message body of the email?
   message   VARCHAR2(200);
   -- What is the SMTP gateway?
   mailhost  VARCHAR2(30) := 'myserver.host.dom';
   -- What is the gateway port?
   mailport  NUMBER := 25;
   -- Define the SMTP gateway connection
   mail_conn UTL_SMTP.connection;
BEGIN
   -- set up email message
   message:=', your password will expire on';

   OPEN c1;
   LOOP
      FETCH c1 INTO username,email,expire_date;
      EXIT WHEN c1%NOTFOUND;

      -- Open the SMTP gateway connection
      mail_conn := UTL_SMTP.open_connection(mailhost,
mailport);
      -- Perform initial handshaking after connecting
      UTL_SMTP.helo(mail_conn, mailhost);
      -- Initiate a mail transaction with the server
      UTL_SMTP.mail(mail_conn, sender);
      -- Specify the recipient
      UTL_SMTP.rcpt(mail_conn, email);
      -- Specify the message body
      UTL_SMTP.data(mail_conn, username||message||'
'||expire_date);
      -- Terminate and disconnect. This sends the
email.
      UTL_SMTP.quit(mail_conn);

   END LOOP;
   CLOSE c1;
END;
/
You can store this as a procedure in your database, or just use it as a stand-alone script. Then schedule this to run daily!

For More Information


This was first published in October 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top