The call_gen_pwd.bat will update the oracle db pwd with an external oracle sql client, and send notification email at the end

2020-06-19_174722.sql_alter_user.txt

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 17:47:23 2020
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri Jun 19 2020 17:34:18 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

old   1: alter user &1 identified by &2 replace &3
new   1: alter user bmreader identified by F7vJu8FLlj replace N4yNjBAgq6

User altered.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

2020-06-19_174722.sql_select_dual.txt

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 19 17:47:24 2020
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri Jun 19 2020 17:47:24 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


	 1
----------
	 1

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

call_gen_pwd.bat

SET /a generateTime = 1
SET /a pwdLength = 10
set /a index = 0
call config.bat
::FOR /L %%G IN (1,1,5) DO (
::echo %%G 
::)

:: create folder if not exists
if not exist "logs" mkdir "logs"
if not exist "history" mkdir "history"
:: create empty text file
type NUL > renew_credential.txt
 
:: call generate random password to text file
for /L %%i in (1,1,%generateTime%) do (
	call gen_pwd.bat %pwdLength% > renew_credential.txt
    set /a index+=1
)
:: swap the newly pwd to the first line in the txt file
if not exist credential_list.txt type nul > credential_list.txt
type credential_list.txt >> renew_credential.txt
type renew_credential.txt > credential_list.txt

set /p oldpwd=<current_credential.txt
set /p newpwd=<credential_list.txt

:: update oracle db pwd
call update_pwd.bat %oldpwd% %newpwd%

:: call 
set "_DB_PWD=%newpwd%"
call check_db_connection.bat %newpwd%

:: replace pwd in config
call ReplaceText.vbs "C:\20200619\context.xml" "%oldpwd%" "%newpwd%" "%cd%" %_STAMP%
call ReplaceText.vbs "C:\20200619\email_config.properties" "%oldpwd%" "%newpwd%" "%cd%" %_STAMP%
call ReplaceText.vbs "C:\20200619\config.bat" "%oldpwd%" "%newpwd%" "%cd%" %_STAMP%

:: write new pws
echo %newpwd% > current_credential.txt
del /f renew_credential.txt

:: send notification email
set "filename=%_STAMP%.sql_select_dual.txt"
call send_email.bat %filename% %newpwd%

changepwd.sql

--spool d:\sql_execuste.log

-- write sql here
-- if password complexity verification function has been enabled
alter user &1 identified by &2 replace &3

-- if password verification is disabled
-- alter user <user> identified by <password>;

-- execute the sql
/

--spool off;
-- exit SQL client
exit

check_db_connection.bat

set "newpwd=%1"
"%sqlclient%\sqlplus.exe" %_DB_USER%/%_DB_PWD%@//%_DB_HOST%:%_DB_PORT%/%_DB_NAME% @"%cd%\selectdual.sql" > logs\%_STAMP%.sql_select_dual.txt

config.bat

set "sqlclient=C:\instantclient_19_6_win_x64"
set "_DB_HOST=yourDBhost_name_or_ip"
set "_DB_PORT=yourport"
set "_DB_NAME=database_name"
set "_DB_USER=database_login_user"
set "_DB_PWD=user_password"

set "_EMAIL_SENDER=the.email.sender@com"
set "_EMAIL_RECEIVER=the.email.receiver@com"
:: for multiple receiver, use ";" without spaces
:: set "_EMAIL_RECEIVER=email1@com;email2@com"

set "_EMAIL_SUBJECT=Database Password Auto-renew Alert - ODW DEV"
set "_EMAIL_TEXTBODY=this is a testing email, kindly ignore"
set "_EMAIL_HTMLBODY=Dear Support,^<br^>^<br^>"

:: initialize currenct date time variable
for /f "delims=" %%a in ('wmic OS Get localdatetime  ^| find "."') do set dt=%%a
set YYYY=%dt:~0,4%
set MM=%dt:~4,2%
set DD=%dt:~6,2%
set HH=%dt:~8,2%
set Min=%dt:~10,2%
set Sec=%dt:~12,2%

:: set stamp=%YYYY%-%MM%-%DD%_%HH%-%Min%-%Sec%
:: set stamp=%YYYY%%MM%%DD%
set _STAMP=%YYYY%-%MM%-%DD%_%HH%%Min%%Sec%

gen_pwd.bat

:: to generate a 10 length password use below in .bat
:: call gen_pwd.bat 10

:: this batch script generate a password include at least 1 upper letter, 1 lower letter, 1 numeric

@echo off
setlocal enableextensions enabledelayedexpansion
set "lower=abcdefghijklmnopqrstuvwxyz"
set "upper=ABCDEFGHIJKLMNOPQRSTUVWXYZ"
set "numeric=0123456789"
set "alphanumeric=%lower%%upper%%numeric%"

set "pwdLength=%1"

set /a part1=1
set /a part2=%part1%+1
set /a part3=%pwdLength%-%part2%

set "result="

set /a index = 1
for /L %%i in (1,1,%pwdLength%) do (
    if !index!==1 (
        call :addUpper
    ) else if !index!==2 (
        call :addNumeric
    ) else if !index!==3 (
        call :addLower
    ) else (
        call :addAny
    )
    set /a index+=1
)

@echo %result%
endlocal
goto :eof

:addUpper
set /a u=%random% %% 25 + 1
set result=%result%!upper:~%u%,1!
goto :eof

:addLower
set /a u=%random% %% 25 + 1
set result=%result%!lower:~%u%,1!
goto :eof

:addNumeric
set /a n=%random% %% 9 + 1
set result=%result%!numeric:~%n%,1!
goto :eof

:addAny
set /a s=%random% %% 61 + 1
set result=%result%!alphanumeric:~%s%,1!
goto :eof

:: Batch file: random alphanumeric sequence
:: https://stackoverflow.com/questions/46573603/batch-file-random-alphanumeric-sequence

ReplaceText.vbs

'ReplaceText.vbs

Option Explicit

Const ForAppending = 8
Const TristateFalse = 0 ' the value for ASCII
Const Overwrite = True

Const WindowsFolder = 0
Const SystemFolder = 1
Const TemporaryFolder = 2

Dim FileSystem
Dim FilePath, FileName, OldText, NewText
Dim CurrentFolder
Dim objFSO, objFile

Dim OriginalFile, TempFile, Line
Dim TempFilePath

Function LPad (str, pad, length)
    LPad = String(length - Len(str), pad) & str
End Function

' the date time should not generate from this file
' it should pass from the bat script to make it consistency
Dim dd, mmm, mm, yyyy, hh, nn, ss
Dim datevalue, timevalue, dtsnow, dtsvalue
dtsnow = Now()
dd = Right("00" & Day(dtsnow), 2)
mmm = MonthName(Month(Now), True)
mm = Right("00" & Month(dtsnow), 2)
yyyy = Year(dtsnow)
hh = Right("00" & Hour(dtsnow), 2)
nn = Right("00" & Minute(dtsnow), 2)
ss = Right("00" & Second(dtsnow), 2)

Dim TimeStamp

If WScript.Arguments.Count = 5 Then
    FilePath = WScript.Arguments.Item(0)
    OldText = WScript.Arguments.Item(1)
    NewText = WScript.Arguments.Item(2)
    CurrentFolder = WScript.Arguments.Item(3)
    TimeStamp = WScript.Arguments.Item(4)
	
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Set objFile = objFSO.GetFile(FilePath)
	FileName = objFSO.GetFileName(objFile)

	'Wscript.Echo "Absolute path: " & objFSO.GetAbsolutePathName(objFile)
	'Wscript.Echo "Parent folder: " & objFSO.GetParentFolderName(objFile)
	'Wscript.Echo "File name: " & objFSO.GetFileName(objFile)
	'Wscript.Echo "Base name: " & objFSO.GetBaseName(objFile)
	'Wscript.Echo "Extension name: " & objFSO.GetExtensionName(objFile)
	'e.g
	'Absolute path: C:\scripts\test.txt
	'Parent folder: C:\scripts
	'File name: test.txt
	'Base name: test
	'Extension name: txt
Else
    Wscript.Echo "Usage: ReplaceText.vbs <FilePath> <OldText> <NewText> <CurrentFolder>"
    Wscript.Quit
End If

Set FileSystem = CreateObject("Scripting.FileSystemObject")
Dim tempFolder: tempFolder = FileSystem.GetSpecialFolder(TemporaryFolder)
TempFilePath = FileSystem.GetTempName

If FileSystem.FileExists(TempFilePath) Then
    FileSystem.DeleteFile TempFilePath
End If
Set TempFile = FileSystem.CreateTextFile(TempFilePath, Overwrite, TristateFalse)
Set OriginalFile = FileSystem.OpenTextFile(FilePath)

Do Until OriginalFile.AtEndOfStream
    Line = OriginalFile.ReadLine

	'MsgBox("Old: "+Line)
    If InStr(Line, OldText) > 0 Then
        Line = Replace(Line, OldText, NewText)
    End If 
	'MsgBox("New: "+Line)

    TempFile.WriteLine(Line)
Loop

OriginalFile.Close
TempFile.Close

'MsgBox(CurrentFolder+"/history/"+FileName+".bak." + dd & mmm & yyyy & "_" & hh & nn & ss)

FileSystem.MoveFile FilePath, CurrentFolder+"/history/"+FileName+".bak." + TimeStamp
'FileSystem.MoveFile FilePath, CurrentFolder+"/history/"+FileName+".bak." + dd & mmm & yyyy & "_" & hh & nn & ss

FileSystem.MoveFile TempFilePath, FilePath

Wscript.Quit

selectdual.sql

--spool d:\sql_execuste.log

-- write sql here
select 1 from dual
-- execute the sql
/

--spool off;
-- exit SQL client
exit

send_email.bat

set attachment=%1
set newpwd=%2

:: merge email content
set _EMAIL_HTMLBODY="Dear Support,<br><br> <span style='color:red;'>This is testing email, please ignore.</span><br><br> The password has been changed to %newpwd% <br><br>(This is an Auto-generated System Notification. Please do NOT reply.)"

cscript.exe //NoLogo password_changed_alert.vbs /esubject:"%_EMAIL_SUBJECT%" /econtent:%_EMAIL_HTMLBODY% /esender:"%_EMAIL_SENDER%" /ereceiver:"%_EMAIL_RECEIVER%" /filepath:"%cd%\logs\%attachment%"

update_pwd.bat

:: this batch script use external oracle sql client to update the db pwd

set "oldpwd=%1"
set "newpwd=%2"

"%sqlclient%\sqlplus.exe" %_DB_USER%/%_DB_PWD%@//%_DB_HOST%:%_DB_PORT%/%_DB_NAME% @"%cd%\changepwd.sql" %_DB_USER% %newpwd% %oldpwd% > logs\%_STAMP%.sql_alter_user.txt