1

Please guide me in configuring Oracle11g to send out Password Expiry Notifications vide SMS or e-mail before password expiry of one week or upon user's profile entering into grace period or any other given condition.

  • I am not aware of 11g providing this service. Phone numbers or addresses for users aren't kept. – Gerard H. Pille Nov 1 '18 at 7:26
3

With all the possibilities of not expired, in grace period and expired this can be quite tricky. As well many databases have service accounts that there is no point in sending emails to as they do not have an email address. In order to get this right you need:

  • a database profile for the people whose passwords expire and you wish to advise
  • a table to hold their database username, email, phone
  • a procedure or external process so that when a new user is added you add them to your email table. Do not put a trigger on dba_users!
  • a logging table so when someone's password expires and they say they were not notified you can show when the email was sent
  • a table/package to hold constants like the name of the email domain

I had this code created by someone on our team to answer the same problem. It works but could be improved. I leave the profile, table and some package names as an exercise

PROCEDURE EmailExpiringPassword IS

      pEmailBody       VARCHAR2(32767);
      pEmailTitle      VARCHAR2(32767); 
      pCount           INTEGER;
      pDBName          VARCHAR2(50);
      pDomain          VARCHAR2(50);
      --email of group issuing this warning 
      --so they can be contacted IF the user is locked out
      pDBNameSupportEmail        VARCHAR2(255);  
begin
      pDomain := <call a package to get the constant value for the domain name>
      pDBNameSupportEmail := <call a package to get the constant value for the email address of the group/team responsible for account management>

      SELECT d.name
      into pDBName
      FROM v$database d;

       FOR missed IN (  SELECT
                        du.username,
                        du.expiry_date,
                        to_char(coalesce(abs(cast(du.expiry_date - sysdate as integer)),0)) as days,
                        du.account_status,
                        du.profile     
                        FROM dba_users du
                        left outer join <Your custom table to hold user email,phone> ud
                           on ud.db_username = du.username
                        where   ud.db_username is null
                        and du.profile in ('<profiles of interest>') 
                      )
         LOOP
          pCount := pCount + 1;
          pEmailTitle := pEmailTitle || missed.username || ' missing record, ';

          pEmailBody := pEmailBody ||
                        '<tr>' || 
                        'Username: ' || missed.username || '<br>' ||
                        'Profile: ' || missed.profile || '<br>' ||
                        'Expiry Date: ' || missed.expiry_date || '<br>' ||
                        'Account Status: ' || missed.account_status ||  '<br>' ||
                        'Database: ' || pDBName  || '<br>' ||
                        'Table: <Your custom table to hold user email,phone>' || '<br>' ||
                        'Missing record. Please add the user to the table <Your custom table to hold user email,phone>.'   || '<br>' ||
                        '' || '<br>' 
                        ;

          IF (length(pEmailBody) > 30000)THEN
            EXIT;
          END IF;
         END LOOP;

         IF (pCount > 0)
           THEN
           <Your custom package to send email>(
           strFROM    => pDBName || '_missing_record@' ||  pDomain ,                            
           strsubject => pEmailTitle,
           strbody    => pEmailBody,
           strto      => pDBNameSupportEmail
           );

           INSERT into <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG
           (user_data_pwd_email_log_id ,sentdate, username, EmailAddress, EmailBody)
           SELECT (SELECT coalesce(max(user_data_pwd_email_log_id),0)+1 
           FROM <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG),
           sysdate,
           pEmailTitle,
           pDBNameSupportEmail,
           pEmailBody
           FROM dual;
           commit;

         END IF;

         pCount := 0;

        FOR email IN (  SELECT
                        du.username,
                        du.expiry_date,
                        ud.firstdotlastname || '@' || pDomain as email, 
                        cast(du.expiry_date - sysdate as integer) as days_number,
                        to_char(coalesce(abs(cast(du.expiry_date - sysdate as integer)),0)) as days,
                        account_status, CASE WHEN p.limit = 'DEFAULT' THEN 10 ELSE 10 END AS Limit     
                        FROM <Your custom table to hold user email,phone> ud
                        inner join dba_users du
                             on ud.db_username = du.username
                        inner join dba_profiles p
                             on du.profile = p.profile 
                             and resource_name = 'PASSWORD_GRACE_TIME'
                        where 
                        du.profile in ('<profiles of interest>') 
                        and (cast(du.expiry_date - sysdate as integer) between -10 and 10
                            or account_status = 'EXPIRED (GRACE)')
                        and ud.active_ind = 1
                      )
         LOOP

          pCount := pCount + 1;

          pEmailTitle := email.username || ' password expiring';

          pEmailBody := 'Username: ' || email.username || '<br>' ||
                        'Database: ' || pDBName  || '<br>' || '<br>' ;

          IF NOT (email.account_status = 'EXPIRED (GRACE)' and email.expiry_date is null) and email.days_number>0 
              THEN
                pEmailBody := 
                'Your password will expire in ' ||  email.days || ' days. ' ||
                'Please reset your password. It will expire after ' || 
                email.expiry_date || 
                ' and locked after ' || 
                to_char(email.expiry_date + email.limit) || '.' ||
                'Contact ' || pDBNameSupportEmail || ' for details.' ||'<br>' ||'<br>'; 


          ELSE IF email.days_number <= 0 and email.expiry_date >= sysdate
               THEN
                pEmailBody :=                
                'Your password expired ' ||  email.days || ' days ago. ' ||
                'Please reset your password. It will be locked after ' || 
                to_char(email.expiry_date + email.limit) || ' days.' ||
                ' Contact ' || pDBNameSupportEmail || ' for details.' ||'<br>' ||'<br>' ;       

           ELSE  
                pEmailBody := pEmailBody ||                 
                'Please login into ' || pDBName || ' to reset your password. ' || '<br>' ||'<br>';
           END IF;              
        END IF;   

          pEmailBody := pEmailBody ||
          '<i>' || 'Please do not reply to this email, this address is not monitored.'   ||                     
          '</tr>'; 


         INSERT into <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG
         (user_data_pwd_email_log_id ,sentdate, username, EmailAddress ,  EmailBody)
         SELECT (SELECT coalesce(max(user_data_pwd_email_log_id),0)+1 FROM <Your custom table to hold user email,phone>_PASSWORD_EMAIL_LOG),
         sysdate,
         email.username,
         email.email,
         pEmailBody
         FROM dual;
         commit; 

         <Your custom package to send email>(
         strFROM    => pDBName || '_password_expiring@' ||  pDomain ,                            
         strsubject => pEmailTitle,
         strbody    => pEmailBody,
         strto      => email.email
         );

         END LOOP;


-- delete logs 60+ days old
delete FROM PASSWORD_EMAIL_LOG
where cast(sysdate - sentdate  as integer) >=60;

end EmailExpiringPassword;
0

There is no built-in functionality like that.

Create your own table where you store e-mail addresses and phone numbers.

Install UTL_MAIL:

sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

Set the SMTP server:

SMTP_OUT_SERVER

Set up an SMS gateway that where you can send e-mails to send SMS (not a database task).

Create a job that regulary checks the expiry date of accounts and sends notifications when needed using UTL_MAIL.

Grant the job owner the necessary ACL privilege to communicate with the SMTP server:

begin
   dbms_network_acl_admin.create_acl (
    acl          => 'mail_access.xml',
    principal    => 'job_owner',
    is_grant     => true,
    privilege    => 'connect');
   commit;
end;
/

begin
   dbms_network_acl_admin.assign_acl (
    acl          => 'mail_access.xml',
    host         => 'smtp.domain.com',
    lower_port   => 25,
    upper_port   => 25
    );
   commit;
end;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.