Utilice los scripts de PowerShell y Bash Shell de esta sección para usar los comandos de EPM Automate a fin de recopilar datos de auditoría y conformidad sobre el acceso manual a la base de datos.
Puede utilizar estos scripts para completar las siguientes tareas:
./reports/dataAccessAuditReport.txt
relacionado con el directorio desde donde ejecuta el script. El informe muestra la hora de acceso a la base de datos y el comando SQL que se ha ejecutado. Se trata de un archivo acumulado, que muestra la información más reciente en la parte superior. La información disponible incluye:
Si no se registra el acceso manual a la base de datos en el Informe de actividad, el informe indica No SQL statements executed
.
Para automatizar la auditoría y la conformidad del acceso a la base de datos:
parseActivityReport.ps1
(Windows, consulte Script de PowerShell (parseActivityReport.ps1)) o parseActivityReport.sh
(Linux/UNIX, consulte Script de shell de Bash (parseActivityReport.sh)).parseActivityReport.bat
copiando el siguiente script en un archivo. Guarde el archivo en el directorio donde está almacenado 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, consulte) para establecer los valores de los parámetros de la siguiente tabla.
Tabla 3-10 Valores de variable que se deben incluir en los scripts
Variable | Descripción |
---|---|
epmuser |
Nombre de usuario de un administrador del servicio.
Ejemplos: Windows: Linux/UNIX: |
epmpassword |
Contraseña del administrador de servicio o ubicación del archivo de contraseña cifrado. Consulte el comando encrypt para obtener información sobre la creación de un archivo de contraseñas cifradas.
Si su contraseña contiene caracteres especiales, consulte Manejo de caracteres especiales. Ejemplos: Windows: Linux/UNIX: |
epmurl |
URL del entorno de Oracle Enterprise Performance Management Cloud.
Ejemplos: Windows: Linux/UNIX: |
report_email_to_address |
Opcionalmente, dirección de correo electrónico a la que se va a enviar el informe. El informe se envía por correo electrónico solo si se especifica este valor.
Ejemplo: |
parseActivityReport.sh
: asegúrese de que los siguientes valores se han definido correctamente para el sistema:
JAVA_HOME
epmautomatescript.sh
mediante la actualización del valor de la directiva epmautomatescript
parseActivityReport.bat
(que ejecuta parseActivityReport.ps1
) o parseActivityReport.sh
para que se ejecute una vez al día. Consulte Automatización de la ejecución de scripts.Script de 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 de shell de 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