Password Change Reminder Procedure

From NazimWIKI
Jump to navigation Jump to search

The following steps are how I set up a process to remind users to change their Oracle password 7 days prior to the expiry ..

**Note: I have created a schema called ORACLE_SEC to store all components of this solution


STEP ONE – INSTALL UTL_MAIL PACKAGE

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
SQL> grant execute on utl_mail to oracle_sec;


STEP TWO – SET INIT.ORA PARAMETER SMTP_OUT_SERVER

SQL> alter system set smtp_out_server=<smtpserver> scope=both;


STEP THREE – GRANT ACL GROUP PERMISSIONS TO ORACLE_SEC

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'mail_access.xml',
    description  => 'Permissions to access e-mail server.',
    principal    => 'ORACLE_SEC',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/
 
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => '<smtpserver>',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
END;
/


STEP FOUR - CREATE A TABLE IN ORACLE_SEC CALLED ORAUSERS

CREATE TABLE ORACLE_SEC.ORAUSERS
(
  USERNAME  VARCHAR2(128 BYTE),
  EMAIL     VARCHAR2(100 BYTE)
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


STEP FIVE - MANUALLY ADD USERNAME & EMAIL ADDRESSES OF USERS TO ORAUSERS TABLES

SQL> INSERT INTO ORACLE_SEC.ORAUSERS VALUES ('SCOTT','scott@email.com');
SQL> COMMIT;


STEP SIX CREATE A TABLE TO LOG REMINDER EMAIL EVENTS

CREATE TABLE ORACLE_SEC.CHGPWD_LOG
(
  EVENT  VARCHAR2(3000 BYTE)
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


STEP SEVEN - CREATE THE STORED PROCEDURE WHICH WILL SEND THE REMINDER EMAIL

CREATE OR REPLACE procedure ORACLE_SEC.CHGPWD is


vName    VARCHAR2(20);
vSender VARCHAR2(100) := 'sender@server.com';
vRecip VARCHAR2(1000);
vSubj VARCHAR2(100) := 'Password Change Reminder';
vMesg VARCHAR2(4000) := 'Your password to the above database is due to expire.  Please reset the password using SQLPLUS and the command PASSWORD.';
vMType VARCHAR2(30) := 'text/plain; charset=us-ascii';


cursor sm is
select ou.username
     , ou.email
     , to_char(du.expiry_date,'YYYYMMDD') edt
     , to_char(du.expiry_date-7,'YYYYMMDD') rdt
     , to_char(sysdate,'YYYYMMDD') sd
  from oracle_sec.orausers ou
     , dba_users du
 where ou.username = du.username;

begin

dbms_output.enable(100000);

select global_name into vName from global_name;


for i in sm loop


 if i.sd = i.rdt then

  begin
   utl_mail.send(vSender, i.email, null, null, vSubj || ' ' || i.username || ' - ' || vName, vMesg, vMType, null);
   insert into oracle_sec.chgpwd_log values ('Sysdate: ' || i.sd || ' ' || i.username || ' Reminder Date: ' || i.rdt || ' ' || vName);
   commit;
  end;



  end if;

end loop;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);

end;
/


STEP EIGHT - CREATE A JOB TO RUN THE STORED PROCEDURE ONCE A DAY

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'ORACLE_SEC.CHGPWD;'
   ,next_date => to_date('14/12/2017 07:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1)+7/24'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/