この項では、PowerShellおよびBashシェル・スクリプトを使用し、EPM自動化コマンドを利用して、手動データベース・アクセス全体の監査およびコンプライアンス・データを収集します。
これらのスクリプトを使用して、次のタスクを完了できます:
./reports/dataAccessAuditReport.txt
を作成します。このレポートには、データベース・アクセスの時間と、実行されたSQLコマンドがリストされます。これは累積ファイルで、最新の情報が最上部に表示されます。使用可能な情報は次のとおりです:
アクティビティ・レポートに手動データベース・アクセスがレポートされていない場合、レポートにはNo SQL statements executed
と記載されます。
データ・アクセスの監査およびコンプライアンスを自動化するには:
parseActivityReport.ps1
(Windowsの場合、PowerShellスクリプト(parseActivityReport.ps1)を参照)またはparseActivityReport.sh
(Linux/UNIXの場合、Bashシェル・スクリプト(parseActivityReport.sh)を参照)という名前を付けます。parseActivityReport.bat
という名前のバッチ・ファイルを作成します。parseActivityReport.ps1
が格納されているディレクトリにファイルを保存します。
@echo off set paramRequiredMessage=Syntax: parseActivityReport.bat USERNAME PASSWORD/PASSWORD_FILE URL [REPORT_EMAIL_TO_ADDRESS] if "%~1" == "" ( echo User Name is missing. echo %paramRequiredMessage% exit /b 1 ) if "%~2" == "" ( echo Password or Password_File is missing. echo %paramRequiredMessage% exit /b 1 ) if "%~3" == "" ( echo URL is missing. echo %paramRequiredMessage% exit /b 1 ) PowerShell.exe -File parseActivityReport.ps1 %*
parseActivityReport.bat
(Windows)またはparseActivityReport.sh
(Linux/UNIX)を変更して、次の表にあるパラメータの値を設定します。
表3-10 スクリプトに含める変数の値
変数 | 説明 |
---|---|
epmuser |
サービス管理者のユーザー名
例: Windows: Linux/UNIX: |
epmpassword |
サービス管理者のパスワードまたは暗号化されたパスワード・ファイルの場所暗号化されたパスワード・ファイルの作成の詳細は、encrypt コマンドを参照してください。
パスワードに特殊文字が含まれている場合は、特殊文字の処理を参照してください。 例: Windows: Linux/UNIX: |
epmurl |
Oracle Enterprise Performance Management Cloud環境のURL。
例: Windows: Linux/UNIX: |
report_email_to_address |
オプションで、レポートが送信される電子メール・アドレス。この値が指定されている場合のみ、レポートが電子メールで送信されます。
例: |
parseActivityReport.sh
の場合のみ: 次の値がシステムに正確に設定されていることを確認します:
JAVA_HOME
epmautomatescript
ディレクティブの値の更新によるepmautomatescript.sh
の場所parseActivityReport.bat
(parseActivityReport.ps1
を実行する)またはparseActivityReport.sh
を毎日1回実行するようにスケジュールします。スクリプトの実行の自動化を参照してください。PowerShellスクリプト(parseActivityReport.ps1)
# Parse Activity Report script $epmuser=$args[0] $epmpassword=$args[1] $epmurl=$args[2] $reportemailtoaddress=$args[3] $logdir="./logs" $logfile="${logdir}/data_access.log" $reportdir="./reports" $reportfile="${reportdir}/dataAccessAuditReport.txt" $matchfile="${reportdir}/matchfile.txt" $nosrfile="${reportdir}/data_access_nosr.csv" $srfile="${reportdir}/data_access_sr.csv" $aprfilelist="${reportdir}/aprfilelist.txt" $activityreportfilelist="${reportdir}/activityreportfiles.txt" $activityreportregex='apr/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}_[0-9]{2}_[0-9]{2}/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}_[0-9]{2}_[0-9]{2}.html' $global:activityreportfile="" $NO_SQL_EXECUTED_STATEMENT="No SQL statements executed" $SQL_WITH_SR_EXECUTED_STATEMENT="SQL statements executed with an SR" $SQL_WITH_NO_SR_EXECUTED_STATEMENT="SQL statements executed without an SR" function DownloadLatestActivityReport() { epmautomate.bat login ${epmuser} ${epmpassword} ${epmurl} >> ${logfile} epmautomate.bat listfiles > ${aprfilelist} foreach ($line in Get-Content $aprfilelist) { if ($line -match $activityreportregex){ echo "$line" >> $activityreportfilelist } } $global:activityreportfile=Get-Content ${activityreportfilelist} -Tail 1 $global:activityreportfile=$global:activityreportfile.trim() echo " " echo "Processing activity report file: $global:activityreportfile" | tee -a ${logfile} epmautomate.bat downloadfile "$global:activityreportfile" >> ${logfile} epmautomate.bat logout >> ${logfile} } function deleteLine($file, $start, $end) { $i = 0 $start-- $end-- (Get-Content $file) | where{ ($i -lt $start -or $i -gt $end) $i++ } > $file #(Get-Content $file) } function GenerateCsvs() { $sqlregex='<DIV id="Database">.*?</DIV>' $activityreportfilename=Split-Path $global:activityreportfile -leaf echo "Creating CSV file: ${matchfile} from data in activityreportfile: ${activityreportfilename}" >> ${logfile} # remove tab and newline characters $activityreportexists=Test-Path "$activityreportfilename" if ($activityreportexists) { (Get-Content "$activityreportfilename") -join ' ' | Set-Content "$activityreportfilename" (Get-Content "$activityreportfilename") -replace "`t", "" | Set-Content "$activityreportfilename" } # capture text matching regex $string=Get-Content $activityreportfilename $ans=$string -match $sqlregex if ($ans -eq "True") { $Matches.0 > $matchfile # remove HTML tags, etc. (Get-Content "$matchfile") -replace "<tr", "`n<tr" | Set-Content "$matchfile" (Get-Content "$matchfile") -replace "<tr[^>]*>", "" | Set-Content "$matchfile" (Get-Content "$matchfile") -replace "<th[^>]*>", "" | Set-Content "$matchfile" (Get-Content "$matchfile") -replace "<td[^>]*>", "|" | Set-Content "$matchfile" (Get-Content "$matchfile") -replace "<br>", "" | Set-Content "$matchfile" (Get-Content "$matchfile") -replace "</td>", "" | Set-Content "$matchfile" (Get-Content "$matchfile") -replace "</tr>", "" | Set-Content "$matchfile" (Get-Content "$matchfile") -replace "\s*</table>\s*</DIV>", "" | Set-Content "$matchfile" deleteLine $matchfile 1 2 # create SR, NOSR CSV files Get-Content $matchfile | ForEach-Object { $elements=$_.split('|') $timeval=$elements[1].Trim() $srval=$elements[3].Trim() $sqlval=$elements[4].Trim() if (${srval} -eq "") { echo "${timeval}|${sqlval}" >> ${nosrfile} } else { if (${sqlval} -ne "") { echo "${srval}|${timeval}|${sqlval}" >> ${srfile} } } } } else { # no SQL statements in activity report echo "" >> ${reportfile} echo $(date) >> ${reportfile} echo "Processing activity report file: $global:activityreportfile" >> ${reportfile} echo "${NO_SQL_EXECUTED_STATEMENT}" | tee -a ${reportfile} CleanUp EmailReportResults exit } } function ReportResults() { echo $(date) >> ${reportfile} echo "Processing activity report file: $global:activityreportfile" >> ${reportfile} $srfileexists=Test-Path $srfile if ($srfileexists) { echo "" | tee -a ${reportfile} echo "${SQL_WITH_SR_EXECUTED_STATEMENT}" | tee -a ${reportfile} echo "SR# Time SQL Statement" | tee -a ${reportfile} echo "--- ---- -------------" | tee -a ${reportfile} # Loop through csv file and parse Get-Content $srfile | ForEach-Object { $elements=$_.split('|') $srval=$elements[0] $timeval=$elements[1] $sqlval=$elements[2] echo "${srval} ${timeval} ${sqlval}" | tee -a ${reportfile} } } $nosrfileexists=Test-Path $nosrfile if ($nosrfileexists) { echo "" | tee -a ${reportfile} echo "${SQL_WITH_NO_SR_EXECUTED_STATEMENT}" | tee -a ${reportfile} echo "Time SQL Statement" | tee -a ${reportfile} echo "---- -------------" | tee -a ${reportfile} # Loop through csv file and parse Get-Content $nosrfile | ForEach-Object { $elements=$_.split('|') $timeval=$elements[0] $sqlval=$elements[1] echo "${timeval} ${sqlval}" | tee -a ${reportfile} } } EmailReportResults } function EmailReportResults { $elements=$reportfile.split('/') $reportfilename=$elements[2] if (${reportemailtoaddress} -match "@") { echo "Emailing Activity Report Results" | tee -a ${logfile} epmautomate.bat login ${epmuser} ${epmpassword} ${epmurl} >> ${logfile} epmautomate.bat uploadFile $reportfile >> ${logfile} epmautomate.bat sendMail $reportemailtoaddress "Database Access Audit Report Results" Body="Database Access Audit Report Results are attached." Attachments=$reportfilename >> ${logfile} epmautomate.bat deleteFile $reportfilename >> ${logfile} epmautomate.bat logout >> ${logfile} } } function Init { $logdirexists=Test-Path $logdir if (!($logdirexists)) { mkdir $logdir 2>&1 | out-null } $reportdirexists=Test-Path $reportdir if (!($reportdirexists)) { mkdir $reportdir 2>&1 | out-null } $logfileexists=Test-Path $logfile if ($logfileexists) { rm $logfile 2>&1 | out-null } $matchfileexists=Test-Path $matchfile if ($matchfileexists) { rm $matchfile 2>&1 | out-null } $nosrfileexists=Test-Path $nosrfile if ($nosrfileexists) { rm $nosrfile 2>&1 | out-null } $srfileexists=Test-Path $srfile if ($srfileexists) { rm $srfile 2>&1 | out-null } $aprfilelistexists=Test-Path $aprfilelist if ($aprfilelistexists) { rm $aprfilelist 2>&1 | out-null } $activityreportfilelistexists=Test-Path $activityreportfilelist if ($activityreportfilelistexists) { rm $activityreportfilelist 2>&1 | out-null } } function CleanUp { $matchfileexists=Test-Path $matchfile if ($matchfileexists) { rm $matchfile 2>&1 | out-null } $aprfilelistexists=Test-Path $aprfilelist if ($aprfilelistexists) { rm $aprfilelist 2>&1 | out-null } $activityreportfilelistexists=Test-Path $activityreportfilelist if ($activityreportfilelistexists) { rm $activityreportfilelist 2>&1 | out-null } } Init DownloadLatestActivityReport GenerateCsvs ReportResults CleanUp
Bashシェル・スクリプト(parseActivityReport.sh)
#!/bin/sh export JAVA_HOME=/scratch/dteHome/autoWork/jdk1.8.0_191 epmautomatescript=/scratch/dteHome/autoWork/epmautomate/19.11.55/bin/epmautomate.sh epmuser="<EPM USER>" epmpwd="<EPM PASSWORD>" epmurl="<EPM URL>" reportemailtoaddress="<EMAIL ADDRESS>" logdir=./logs logfile="${logdir}/data_access.log" reportdir=./reports reportfile="${reportdir}/dataAccessAuditReport.txt" nosrfile="${reportdir}/data_access_nosr.csv" srfile="${reportdir}/data_access_sr.csv" matchfile="${reportdir}/match.out" aprfilelist="${reportdir}/aprfilelist.txt" activityreportfile="" activityreportregex='apr/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}_[0-9]{2}_[0-9]{2}/[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}_[0-9]{2}_[0-9]{2}.html' NO_SQL_EXECUTED_STATEMENT="No SQL statements executed". SQL_WITH_SR_EXECUTED_STATEMENT="SQL statements executed with an SR" SQL_WITH_NO_SR_EXECUTED_STATEMENT="SQL statements executed without an SR" cd "$(dirname "$0")" generateCsvs() { local sqlregex='<DIV id="Database">.*?</DIV>' local activityreportfilename=$(echo "${activityreportfile}" | rev | cut -d'/' -f1 | rev) echo "Creating CSV file: ${matchfile} from data in activityreportfile: ${activityreportfilename}" >> ${logfile} # remove tab and newline characters cat "${activityreportfilename}" | tr -d "\t\n\r" > ${matchfile} # capture text matching regex grep -Po "${sqlregex}" ${matchfile} > ${matchfile}.tmp # remove HTML tags, etc. sed -e 's/<tr/\n<tr/g' -e 's/<tr[^>]*>//g' -e 's/<th[^>]*>//g' -e 's/<td[^>]*>/|/g' -e 's/<br>//g' -e 's|</td>||g' -e 's|</tr>||g' -e 's|[ ]*</table></DIV>||g' -e 's/|[ ]*/|/g' -e 's/[ ]*|/|/g' -e 's/<DIV id="Database">.*<!-- Print Tables -->\n//g' ${matchfile}.tmp > ${matchfile} # create SR, NOSR CSV files while read line do timeval=$(echo "${line}" | cut -d'|' -f2) srval=$(echo "${line}" | cut -d'|' -f4) sqlval=$(echo "${line}" | cut -d'|' -f5) if [[ "${srval}" == "" ]] then echo "${timeval}|${sqlval}" >> ${nosrfile} else if [[ "${sqlval}" != "" ]] then echo "${srval}|${timeval}|${sqlval}" >> ${srfile} fi fi done < ${matchfile} } reportResults() { echo $(date) >> ${reportfile} echo "Processing activity report file: $activityreportfile" >> ${reportfile} if [[ -f ${srfile} ]] then echo "" | tee -a ${reportfile} echo "${SQL_WITH_SR_EXECUTED_STATEMENT}" | tee -a ${reportfile} echo "SR# Time SQL Statement" | tee -a ${reportfile} echo "--- ---- -------------" | tee -a ${reportfile} while read line do srval=$(echo "${line}" | cut -d'|' -f1) timeval=$(echo "${line}" | cut -d'|' -f2) sqlval=$(echo "${line}" | cut -d'|' -f3) echo "${srval} ${timeval} ${sqlval}" | tee -a ${reportfile} done < ${srfile} fi if [[ -f ${nosrfile} ]] then echo "" | tee -a ${reportfile} echo "${SQL_WITH_NO_SR_EXECUTED_STATEMENT}" | tee -a ${reportfile} echo "Time SQL Statement" | tee -a ${reportfile} echo "---- --- ---------" | tee -a ${reportfile} while read line do timeval=$(echo "${line}" | cut -d'|' -f1) sqlval=$(echo "${line}" | cut -d'|' -f2) echo "${timeval} ${sqlval}" | tee -a ${reportfile} done < ${nosrfile} fi if [[ ! -f ${srfile} ]] && [[ ! -f ${nosrfile} ]] then echo "" | tee -a ${reportfile} echo "${NO_SQL_EXECUTED_STATEMENT}" | tee -a ${reportfile} fi emailReportResults } downloadLatestActivityReport() { ${epmautomatescript} login ${epmuser} ${epmpwd} ${epmurl} >> ${logfile} ${epmautomatescript} listfiles > ${aprfilelist} activityreportfile=$(cat ${aprfilelist} | grep -P "${activityreportregex}" | tail -n 1 | sed -e 's/^ //') echo " " echo "Processing activity report file: ${activityreportfile}" | tee -a ${logfile} ${epmautomatescript} downloadfile "${activityreportfile}" >> ${logfile} ${epmautomatescript} logout >> ${logfile} } emailReportResults() { reportfilename=$(echo "${reportfile}" | cut -d'/' -f3) if [[ "${reportemailtoaddress}" == *"@"* ]] then echo "Emailing Activity Report Results" | tee -a ${logfile} ${epmautomatescript} login ${epmuser} ${epmpwd} ${epmurl} >> ${logfile} ${epmautomatescript} uploadFile "$reportfile" >> ${logfile} ${epmautomatescript} sendMail $reportemailtoaddress "Database Access Audit Report Results" Body="Database Access Audit Report Results are attached." Attachments=$reportfilename >> ${logfile} ${epmautomatescript} deleteFile "$reportfilename" >> ${logfile} ${epmautomatescript} logout >> ${logfile} fi } checkParams() { if [ -z "$epmuser" ] then echo "Username is missing." echo "Syntax: parseActivityReport.sh USERNAME PASSWORD URL" exit 2 fi if [ -z "$epmpwd" ] then echo "Password is missing." echo "Syntax: parseActivityReport.sh USERNAME PASSWORD URL" exit 2 fi if [ -z "$epmurl" ] then echo "URL is missing." echo "Syntax: parseActivityReport.sh USERNAME PASSWORD URL" exit 2 fi } init() { checkParams if [ ! -d "${logdir}" ] then mkdir ${logdir} fi if [ ! -d "${reportdir}" ] then mkdir ${reportdir} fi if [ ! -f "${epmautomatescript}" ] then echo "Cannot locate EPMAutomate script: ${epmautomatescript}. Please check setting and run script again. Exiting." | tee -a ${logfile} exit fi if [ -f "${srfile}" ] then rm ${srfile} fi if [ -f "${nosrfile}" ] then rm ${nosrfile} fi if [ -f "${matchfile}" ] then rm ${matchfile} fi if [ -f "${aprfilelist}" ] then rm ${aprfilelist} fi } cleanup() { if [ -f "${matchfile}" ] then rm ${matchfile} fi if [ -f "${matchfile}.tmp" ] then rm ${matchfile}.tmp fi if [ -f "${aprfilelist}" ] then rm ${aprfilelist} fi } init downloadLatestActivityReport generateCsvs reportResults cleanup