Ask the Expert

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

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:

   -- Cursor of those accounts whose password 
   -- expires in seven days or less
      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) := '';
   -- What is the gateway port?
   mailport  NUMBER := 25;
   -- Define the SMTP gateway connection
   mail_conn UTL_SMTP.connection;
   -- set up email message
   message:=', your password will expire on';

   OPEN c1;
      FETCH c1 INTO username,email,expire_date;

      -- Open the SMTP gateway connection
      mail_conn := UTL_SMTP.open_connection(mailhost,
      -- 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, username||message||'
      -- Terminate and disconnect. This sends the

   CLOSE c1;
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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: