12 Replies Latest reply: Feb 7, 2013 10:26 AM by 756591 RSS

    Want to send password expiration mail

    User356375
      Hi,

      I want to send email notification before the password expires.

      For example, like password expires after every 30 days, so notification/warning for pasword expiration should trigger before 5 days of expiration i.e on 25th day, a mail should be triggered to the user warning password expiration.

      how can configured this ?
        • 1. Re: Want to send password expiration mail
          andrewmy
          One possible solution:

          The password expiry can be found in dba_users.expiry_date
          select username, account_status, expiry_date
          from dba_users
          /
          
          USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
          ------------------------------ -------------------------------- -----------
          USER1                          OPEN                             27/10/2012
          USER2                          OPEN                             30/10/2012
          USER3                          OPEN                             20/10/2012
          ...
          Create a table to store the email addresses of the users e.g. user_email_add

          Create a PL/SQL procedure that:
          a) Checks the view dba_users and select all users where password is expiring in 5 days e.g. where (expiry_date - 5) = sysdate and account_status = 'OPEN'
          b) Get the user email address from user_email_add
          c) Generate the email using UTL_SMTP. You will need to have a SMTP mail server. You could also use UTL_MAIL.

          Schedule the procedure to run everyday using either DBMS_SCHEDULER (10g and above) or DBMS_JOB (earlier versions than 10g).
          • 2. Re: Want to send password expiration mail
            927868
            you can user OS mail utility sucj as mailx for sun solaris to do the same.
            • 3. Re: Want to send password expiration mail
              846231
              Hi all,

              How can I attach the sql list output to the mail message body?



              Thanks
              • 4. Re: Want to send password expiration mail
                EdStevens
                KinsaKaUy? wrote:
                Hi all,

                How can I attach the sql list output to the mail message body?


                Read the docs on utl_mail ....

                Note the input parameter referred to as "message" ....

                Thanks
                • 5. Re: Want to send password expiration mail
                  jgarry
                  (remember to start your own thread rather than opening an old one. You can link to the old one in your new one).

                  Sometimes this is useful: http://laurentschneider.com/wordpress/2011/04/send-html-report-per-email-from-sqlplus.html

                  (I had to change the commands slightly, it seems platform, configuration and recipient mail software dependent.)
                  • 6. Re: Want to send password expiration mail
                    846231
                    Thanks jq,

                    How do I convert this sql code to pl/sql? Since and am going to schedule this batch job on dbms_scheduler.
                    • 7. Re: Want to send password expiration mail
                      sb92075
                      KinsaKaUy? wrote:
                      Thanks jq,

                      How do I convert this sql code to pl/sql? Since and am going to schedule this batch job on dbms_scheduler.
                      what SQL code?
                      please post functioning SQL code that does what you desire.
                      • 8. Re: Want to send password expiration mail
                        ShishirTekadeR
                        First create one new table:==

                        CREATE TABLE "SYSTEM"."USER_LIST"
                        ( "APP_NAME" VARCHAR2(255) DEFAULT 'GENEVA',
                        "DB_NAME" VARCHAR2(255) DEFAULT 'CORPCONF',
                        "USER_NAME" VARCHAR2(255) NOT NULL ENABLE,
                        "EMP_ID" VARCHAR2(255),
                        "RETENTION_PERIOD" VARCHAR2(255),
                        "DIRECT_LOGIN" VARCHAR2(255),
                        "FIRST_NAME" VARCHAR2(255),
                        "SECOND_NAME" VARCHAR2(255),
                        "ACC_STATUS" VARCHAR2(255),
                        "DESGN" VARCHAR2(255),
                        "DEPT" VARCHAR2(255),
                        "ACCT_TYPE" VARCHAR2(255),
                        "GENERIC_SHARED_ACCT" VARCHAR2(255),
                        "REASON_GENERIC_SHARED" VARCHAR2(255),
                        "COMP_NAME" VARCHAR2(255),
                        "ROLES_GRANTED" VARCHAR2(255) DEFAULT 'Pls Check The Roles and Privs HTML',
                        "ACCESS_PRIVS" VARCHAR2(255) DEFAULT 'Pls Check The Roles and Privs HTML',
                        "COMMENTS" VARCHAR2(255),
                        "EMAIL_ID" VARCHAR2(255)
                        ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                        STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                        TABLESPACE "SYSTEM";


                        Create one new procedure===

                        CREATE OR REPLACE PROCEDURE "SYS"."PASSWORD_EXPIRY_MAIL"
                        as
                        l_mailhost VARCHAR2(255) := 'Mailx server IP address';
                        l_mail_conn utl_smtp.connection;
                        v_dbname varchar2(100);
                        v_username varchar2(100);
                        v_expiry varchar2(100);
                        v_exp_status varchar2(100);
                        v_lock varchar2(100);
                        v_email varchar2(1000);
                        stmt2 varchar2(10000);
                        stmt3 varchar2(10000);
                        stmt4 varchar2(10000);
                        v_subject varchar2(100):='Password for DB users Expires soon,Pls change it ASAP
                        ';
                        BEGIN
                        select name into v_dbname from v$database;
                        for l in (select username from dba_users a , dba_profiles b where a.profile=b.pr
                        ofile and RESOURCE_NAME='PASSWORD_LIFE_TIME' and LIMIT <>'UNLIMITED' and nvl(EXP
                        IRY_DATE,sysdate) - sysdate <=10 and (username not in ('MDSYS','ANONYMOUS','CTX
                        SYS','EXFSYS','LBACSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OU
                        TLN','SI_INFORMTN_SCHEMA','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB','DI
                        P','MDDATA','BI','HR','OE','PM','IX','SH','ODM','QS_ADM','ODM_MTR','QS_CB','SCOT
                        T','DMSYS')) order by EXPIRY_DATE )
                        loop
                        stmt2:='select trunc(expiry_date) from dba_users u where u.username='''||l.use
                        rname||'''';
                        stmt3:='select decode(lock_date,null,''not yet locked'',''locked on ''||lock_dat
                        e) from dba_users u where u.username='''||l.username||'''';
                        stmt4:='select nvl(EMAIL_ID,''l2support.dba@tatacommunications.com'') from dba_
                        users u,system.user_list su where u.username=su.USER_NAME and u.username='''||l.
                        username||'''';
                        v_username:= l.username;
                        execute immediate stmt2 into v_expiry;
                        execute immediate stmt3 into v_lock;
                        execute immediate stmt4 into v_email;
                        if v_expiry is null then
                        v_exp_status:='Password Not Yet Reset';
                        else if v_expiry <= trunc(sysdate) then
                        v_exp_status:='expired on '||v_expiry;
                        else
                        v_exp_status:='will be expired on '||v_expiry;
                        end if;
                        end if;
                        dbms_output.put_line('User Name : ' ||v_username);
                        dbms_output.put_line('Expirydate: ' ||v_exp_status);
                        dbms_output.put_line('Lock Date : ' ||v_lock);
                        dbms_output.put_line('Email ID : ' ||v_email);
                        l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
                        utl_smtp.helo(l_mail_conn, l_mailhost);
                        utl_smtp.mail(l_mail_conn, 'l2support.dba@tatacommunications.com');
                        utl_smtp.rcpt(l_mail_conn, v_email);
                        utl_smtp.data(l_mail_conn,'From: Oracle Database Owner' || utl_tcp.crlf || 'To:
                        ' || v_email || utl_tcp.crlf || 'Subject: ' || v_subject || utl_tcp.crlf || 'Pas
                        sword For the DB user '||v_username||' '||v_exp_status||' '||v_lock||utl_tcp.cr
                        lf||'Please change the password ASAP ,other wise a/c will be lock'||utl_tcp.crlf
                        ||'Database Name :'||v_dbname );
                        utl_smtp.quit(l_mail_conn);
                        end loop;
                        EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                        NULL;
                        end PASSWORD_EXPIRY_MAIL;
                        /

                        then insert all user details in new tables ..
                        =====================================
                        Best Regards,
                        Shishir Tekade.
                        My Blog: http://shishirtekade.blogspot.com
                        • 9. Re: Want to send password expiration mail
                          846231
                          what SQL code?
                          please post functioning SQL code that does what you desire.
                          It's in the link of jq dear :) . I wil be charged with piracy if I cut & paste it here :(

                          Ok with the permission from the source owner:

                          rep.sql
                          set echo off numf 999G999G999G999 lin 32000 trims on pages 50000 head on feed off markup html off
                          alter session set nls_numeric_characters='.''' nls_date_format='Day DD. Month, YYYY';
                          spool /tmp/rep.html
                          prompt To: laurentschneider@example.com
                          prompt From: laurentschneider@example.com
                          prompt Subject: Daily department report
                          prompt Content-type: text/html
                          prompt MIME-Version: 1.0
                          set markup html on entmap off table 'BORDER="2" BGCOLOR="pink"'
                          prompt <i>Good morning, </i>
                          prompt <i>Here is the department report per &_DATE</i>
                          prompt <i>Kind Regards, </i>
                          prompt <i>Your IT Operations</i>
                          
                          prompt <br/><h3>List of departments with the total salaries of their employees</h3>
                          select dname "Department", sum(sal) "Salary" from emp join dept using (deptno) group by rollup(dname);
                          spool off
                          host /usr/sbin/sendmail -t </tmp/rep.html
                          quit
                          Edited by: KinsaKaUy? on 26-Dec-2012 01:16

                          Edited by: KinsaKaUy? on 26-Dec-2012 01:18
                          • 10. Re: Want to send password expiration mail
                            846231
                            Thanks Shi

                            Your code is quite complex and long :(
                            Why is that the one from jq is very short and simple?

                            Can you try to convert it to pl/sql? :)


                            Thanks again,
                            • 11. Re: Want to send password expiration mail
                              EdStevens
                              KinsaKaUy? wrote:
                              Thanks Shi

                              Your code is quite complex and long :(
                              Why is that the one from jq is very short and simple?
                              Did you actually read the two different codes and analyze what they do? If you had, you'd know why one is "complex and long" and the other "short and simple". And actually, the one you claim is "complex and long" is neither. You just have to start reading it and thinking about what each command in it does.

                              Can you try to convert it to pl/sql? :)
                              Why don't YOU try to convert it to PL/SQL and and then ask for help with what you find difficult. People are much more willing to help someone who shows some initiative and willingness to do their own work.
                              >
                              >
                              Thanks again,
                              • 12. Re: Want to send password expiration mail
                                756591
                                Well here is one that is inelegant, but it is quick and dirty and you can just schedule it as a procedure or as an anonymous block with the OEM or dbms_scheduler and it works with 10g (no dbms_mail).

                                --create or replace procedure AlertExpAccounts as
                                declare
                                c utl_smtp.connection;
                                r utl_smtp.replies;
                                username varchar2(256) := 'jskinner';
                                password varchar2(256) := 'pasword';
                                v_SID varchar2(10);
                                v_From VARCHAR2(80) := 'joe.skinner@xxx.xxx.gov';
                                v_Recipient VARCHAR2(80) := 'somebody@xxx.xxx.xxx';
                                v_Subject VARCHAR2(80) := 'Expiring Accounts';
                                v_Mail_Host VARCHAR2(30) := 'exchangeHostforExample.xxx.gov';
                                v_Mail_Conn utl_smtp.Connection;
                                crlf VARCHAR2(2) := chr(13)||chr(10);
                                longString varchar2(200);
                                BEGIN
                                SELECT wm_concat(username) into longString from sys.dba_users where expiry_date < sysdate + 14
                                and expiry_date > sysdate - 60;
                                select instance_name into v_sid from v$instance;
                                v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
                                utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
                                utl_smtp.Mail(v_Mail_Conn, v_From);
                                utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
                                utl_smtp.Data(v_Mail_Conn,
                                'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
                                'From: ' || v_From || crlf ||
                                'Subject: '|| v_Subject || crlf ||
                                'To: ' || v_Recipient || crlf ||
                                crlf ||v_sid||' '||
                                longString|| crlf ||     -- Message body
                                'end of message'|| crlf
                                );
                                utl_smtp.Quit(v_mail_conn);
                                EXCEPTION
                                WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
                                raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
                                END;
                                /

                                Edited by: jitaylor on Feb 7, 2013 8:26 AM