Problem solve Get help with specific problems with your technologies, process and projects.

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.

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) := 'myserver.host.dom';
   -- 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
      UTL_SMTP.data(mail_conn, 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 last published in October 2002

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.