Automate Database Access Audit and Compliance
Use the PowerShell and Bash Shell scripts in this section to leverage EPM Automate commands to collect audit and compliance data around manual database access.
You can use these scripts to complete these tasks:
- Download the Activity Report for the current day
- Parse the report to determine if manual database access is reported for the environment
- Create
./reports/dataAccessAuditReport.txt
relative to the directory from where you execute the script. The report lists the time of database access and the SQL command that was executed. This is a cumulative file, which shows the latest information at the top. Information available include:- Date and time at which the report was generated
- Database access details, if available. Database access without a service request and database access with service request are listed in separate sections.
If manual database access is not reported in the Activity Report, the report states
No SQL statements executed
. - Optionally, send the report to a specified email address.
To automate data access audit and compliance:
- Copy one of the script from the following sections to a file and save it to your file system. Name the file
parseActivityReport.ps1
(Windows see PowerShell Script (parseActivityReport.ps1)) orparseActivityReport.sh
(Linux/UNIX see Bash Shell Script (parseActivityReport.sh)). - Windows only: Create a batch file named
parseActivityReport.bat
by copying the following script into a file. Save the file in the directory whereparseActivityReport.ps1
is stored.@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 %*
- Modify
parseActivityReport.bat
(Windows) orparseActivityReport.sh
(Linux/UNIX see ) to set the values for the parameters in the following table.Table 3-11 Variable Values to Include in Scripts
Variable Description epmuser
User name of a Service Administrator Examples:
Windows:
set epmuser="jDoe"
Linux/UNIX:
epmuser="jDoe"
epmpassword
Password of the Service Administrator or the location of the encrypted password file. See the encrypt
command for information on creating an encrypted password file.If your password contains special characters, see Handling Special Characters.
Examples:
Windows:
set epmpassword = "Example"
Linux/UNIX:
epmpassword="Example"
epmurl
The URL of the Oracle Enterprise Performance Management Cloud environment. Examples:
Windows:
set epmurl="https://example.oraclecloud.com"
Linux/UNIX:
epmurl="https://example.oraclecloud.com"
report_email_to_address
Optionally, the email address to which the report is to be sent. The report is emailed only if this value is specified. Example:
john.doe@example.com
- For
parseActivityReport.sh
only: Ensure that the following values are set correctly for your system:JAVA_HOME
- Location of
epmautomatescript.sh
by updating the value ofepmautomatescript
directive
- Using a scheduler available on the operating system, schedule
parseActivityReport.bat
(which executesparseActivityReport.ps1
) orparseActivityReport.sh
to run once every day. See Automating Script Execution.
PowerShell Script (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 Shell Script (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