Les scripts PowerShell et d'interpréteur de commandes Bash de cette section vous permettent d'exploiter les commandes EPM Automate en vue de collecter des données d'audit et de conformité concernant l'accès manuel à la base de données.
Vous pouvez utiliser ces scripts pour effectuer les tâches suivantes :
./reports/dataAccessAuditReport.txt
relatif au répertoire à partir duquel exécuter le script. Le rapport répertorie l'heure d'accès à la base de données et la commande SQL exécutée. Il s'agit d'un fichier cumulé qui affiche les dernières informations en haut. Informations disponibles :
Si l'accès manuel à la base de données n'est pas signalé dans le rapport d'activité, le rapport indique No SQL statements executed
.
Pour automatiser l'audit et la conformité de l'accès à la base de données, procédez comme suit :
parseActivityReport.ps1
(Windows, reportez-vous à la section Script PowerShell (parseActivityReport.ps1)) ou parseActivityReport.sh
(Linux/UNIX, reportez-vous à la section Script d'interpréteur de commandes Bash (parseActivityReport.sh)).parseActivityReport.bat
en copiant le script suivant dans un fichier. Enregistrez le fichier dans le répertoire où parseActivityReport.ps1
est stocké.
@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) ou parseActivityReport.sh
(Linux/UNIX) pour définir les valeurs des paramètres indiqués dans le tableau suivant.
Tableau 3-10 Valeurs de variable à inclure dans les scripts
Variable | Description |
---|---|
epmuser |
Nom d'utilisateur d'un administrateur de service
Exemples : Windows : Linux/UNIX : |
epmpassword |
Mot de passe de l'administrateur de service ou emplacement du fichier de mots de passe crypté. Pour obtenir des informations sur la création d'un fichier de mots de passe cryptés, reportez-vous à la commande encrypt .
Si votre mot de passe contient des caractères spéciaux, reportez-vous à la section Gestion des caractères spéciaux. Exemples : Windows : Linux/UNIX : |
epmurl |
URL de l'environnement Oracle Enterprise Performance Management Cloud.
Exemples : Windows : Linux/UNIX : |
report_email_to_address |
(Facultatif) Adresse électronique à laquelle le rapport doit être envoyé. Le rapport n'est envoyé par courriel que si cette valeur est indiquée.
Exemple : |
parseActivityReport.sh
uniquement : assurez-vous que les valeurs suivantes sont correctement définies pour votre système :
JAVA_HOME
epmautomatescript.sh
en mettant à jour la directive epmautomatescript
parseActivityReport.bat
(qui exécute parseActivityReport.ps1
) ou de parseActivityReport.sh
une fois par jour. Reportez-vous à la section Automatisation de l'exécution de scripts.Script 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 d'interpréteur de commandes 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