Utilizzare gli script shell di PowerShell e Bash descritti in questa sezione per usare i comandi EPM Automate per raccogliere i dati di audit e conformità relativi all'accesso manuale al database.
È possibile utilizzare i seguenti script per completare i task elencati di seguito:
./reports/dataAccessAuditReport.txt
in relazione alla directory da cui si esegue lo script. Nel report sono riportati l'ora degli accessi al database e il comando SQL eseguito. Si tratta di un file cumulativo, che riporta le informazioni più aggiornate nella parte superiore dell'elenco. Le informazioni disponibili includono:
Se gli accessi manuali al database non sono registrati nel report attività, nel report è contenuto il messaggio No SQL statements executed
.
Per automatizzare l'audit e la conformità dell'accesso ai dati, procedere come segue.
parseActivityReport.ps1
(per Windows, fare riferimento alla sezione Script di PowerShell (parseActivityReport.ps1)) o parseActivityReport.sh
(per Linux/UNIX, fare riferimento alla sezione Script shell Bash (parseActivityReport.sh)).parseActivityReport.bat
copiando lo script seguente in un file. Salvare il file nella stessa directory in cui è memorizzato il file 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) o parseActivityReport.sh
(Linux/UNIX) per impostare i valori dei parametri descritti nella tabella riportata di seguito.
Tabella 3-10 Valori di variabili da includere negli script
Variabile | Descrizione |
---|---|
epmuser |
Nome utente di un amministratore del servizio
Esempi: Windows: Linux/UNIX: |
epmpassword |
Password dell'utente con ruolo Amministratore servizi o posizione in cui è disponibile il password file cifrato. Fare riferimento al comando encrypt per informazioni sulla creazione di un password file cifrato.
Se la password contiene caratteri speciali, fare riferimento alla sezione Gestione dei caratteri speciali. Esempi: Windows: Linux/UNIX: |
epmurl |
URL dell'ambiente Oracle Enterprise Performance Management Cloud.
Esempi: Windows: Linux/UNIX: |
report_email_to_address |
(Facoltativo) Indirizzo e-mail a cui deve essere inviato il report. Il report viene inviato via e-mail solo se viene specificato questo valore.
Esempio: |
parseActivityReport.sh
: assicurarsi che i seguenti valori siano impostati correttamente per il sistema in uso:
JAVA_HOME
epmautomatescript.sh
mediante l'aggiornamento del valore della direttiva epmautomatescript
parseActivityReport.bat
(che esegue parseActivityReport.ps1
) o di parseActivityReport.sh
una volta al giorno. Fare riferimento alla sezione Automazione dell'esecuzione degli script.Script di 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
Script shell 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