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) := 'firstname.lastname@example.org'; -- 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in October 2002