Password Change Reminder Procedure
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; /