Audit Report Generation For Oracle In HTML Format
This document describes steps needed to setup auditing in Oracle, generating HTML report, managing audit records and sending HTML email to DBAs.
v Set audit_trail parameter in init.ora to and bounce the database. This will log the audit trail in the database sys.aud$ table.
AUDIT_TRAIL = DB
Or if spfile is in use it can be changed using
alter system set audit_trail=DB scope=spfile
and restarting the database.
v Following things are audited. These contain statement auditing, privilege auditing and object auditing. Statement auditing is enabled by just an audit on a particular type like audit cluster or audit trigger. Privilege auditing is enabled by an audit on a particular privilege like audit create index or audit drop index. We have not enabled audit on a particular object in which object name has to be explicitly specified. All the audit options are enabled by ACCESS(default) except logon audit which is a session level auditing whenever not successful.
AUDIT CLUSTER;
AUDIT CONTEXT;
AUDIT DATABASE LINK;
AUDIT DIMENSION;
AUDIT DIRECTORY;
AUDIT CREATE ANY INDEX;
AUDIT DROP ANY INDEX;
AUDIT PROCEDURE;
AUDIT PROFILE;
AUDIT PUBLIC DATABASE LINK;
AUDIT PUBLIC SYNONYM;
AUDIT ROLE;
AUDIT SEQUENCE;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
AUDIT SYNONYM;
AUDIT SYSTEM GRANT;
AUDIT TABLE;
AUDIT TRIGGER;
AUDIT TYPE;
AUDIT USER;
AUDIT VIEW;
AUDIT ALTER TABLE;
AUDIT ALTER SEQUENCE;
AUDIT GRANT DIRECTORY;
AUDIT GRANT SEQUENCE;
AUDIT GRANT TABLE;
AUDIT GRANT TYPE;
v Generate an audit report using script audit_report.bat, which in turns call audit_report.sql
v Delete audit records older than 30 days every day using audit_purge.sql
v Send email by running auditsummary.bat wrapper batch file which merges all the reports generated by audit_report and sends email using sendmailHTML.vbs
Scripts
1. AUDIT_REPORT.BAT
Audit_Report.bat is called from a batch file (daily_other_jobs.bat) for each database which needs audit report (as defined in JobInfo configuration file).
@echo off
if "%1" equ "" goto usage
set DBNAME=%1
if not exist E:\Scripts\%DBNAME%\sql\logs mkdir E:\Scripts\%DBNAME%\sql\logs
rem purge audit records older than 30 days
for /F "tokens=1-3" %%i in (E:\Scripts\Sql\AdminInfo) do ( if /I %%i equ %DBNAME% (
if exist e:\scripts\%DBNAME%\sql\logs\auditreport.html del e:\scripts\%DBNAME%\sql\logs\auditreport.html
D:\oracle\ora92\bin\sqlplus -S -M "HTML ON PREFORMAT OFF TABLE 'BORDER="2"'" system/%%j@%DBNAME% @e:\scripts\sql\audit_report.sql > e:\scripts\%DBNAME%\sql\logs\auditreport.html
D:\oracle\ora92\bin\sqlplus -S system/%%j@%DBNAME% @e:\scripts\sql\audit_purge.sql 30 > e:\scripts\%DBNAME%\sql\logs\audit_purge.log
))
E:\scripts\saveauditlog.bat %DBNAME%
goto end
:usage
echo usage : %0 DBNAME
:end
1.1 AdminInfo
This file contains UserID and password file to run the audit report. The file is encrypted using Windows encryption and can only be open by the Windows account scheduling the job. The format of the file content is
SID SYSTEMPassword
DB1 db1password
DB2 db2password
1.2 Audit_Report.sql
It reads AUD$ file and produces a HTML formatted report. Some records are filtered to minimize the size of the report.
REM Filter out ALTER TABLESPACE done by SYSTEM ACCOUNT on locally managed tablespaces
REM Filter JOURNAL and IOT tables and indexes created by Oracle for online index rebuild.
SET FEEDBACK OFF
SET ECHO OFF
COLUMN ROWNUM HEADING 'SR #'
COLUMN NAME HEADING 'ACTION'
COLUMN OBJ$NAME HEADING 'OBJECT NAME'
COLUMN AUTH$PRIVILEGES HEADING 'PRIVILEGES'
COLUMN AUTH$GRANTEE HEADING 'GRANTEE'
COLUMN SPARE1 HEADING 'OS USER'
COLUMN STATUS HEADING 'STATUS' ENTMAP OFF
SET HEAD OFF
COLUMN DATABASE ENTMAP OFF
select '<b>' || name || '</b>' "DATABASE" from v$database;
SET HEAD ON
select rownum,
to_char(timestamp#,'MM/DD/YY HH24:MI:SS') TIMESTAMP,
userid,
terminal,
name,
obj$name,
decode(substr(NVL(auth$privileges,'----------------'), 1,1),'-','','ALT ') ||
decode(substr(NVL(auth$privileges,'----------------'), 2,1),'-','','AUD ') ||
decode(substr(NVL(auth$privileges,'----------------'), 3,1),'-','','COM ') ||
decode(substr(NVL(auth$privileges,'----------------'), 4,1),'-','','DEL ') ||
decode(substr(NVL(auth$privileges,'----------------'), 5,1),'-','','GRA ') ||
decode(substr(NVL(auth$privileges,'----------------'), 6,1),'-','','IND ') ||
decode(substr(NVL(auth$privileges,'----------------'), 7,1),'-','','INS ') ||
decode(substr(NVL(auth$privileges,'----------------'), 8,1),'-','','LOC ') ||
decode(substr(NVL(auth$privileges,'----------------'), 9,1),'-','','REN ') ||
decode(substr(NVL(auth$privileges,'----------------'), 10,1),'-','','SEL ') ||
decode(substr(NVL(auth$privileges,'----------------'), 11,1),'-','','UPD ') ||
decode(substr(NVL(auth$privileges,'----------------'), 12,1),'-','','REF ') ||
decode(substr(NVL(auth$privileges,'----------------'), 13,1),'-','','EXE ') ||
decode(substr(NVL(auth$privileges,'----------------'), 14,1),'-','','CRE ') ||
decode(substr(NVL(auth$privileges,'----------------'), 15,1),'-','','REA ') ||
decode(substr(NVL(auth$privileges,'----------------'), 16,1),'-','','WRI ')
auth$grantee,
decode(returncode,0, 'SUCCESS', '<font color=''red''>'|| 'FAIL' ) status,
spare1
from sys.aud$ a, audit_actions b
where a.action# = b.action
and trunc(timestamp#) between trunc(sysdate-1) and trunc(sysdate)
and NVL(obj$name,' ') not like 'SYS_JOURNAL%'
and NVL(obj$name,' ') not like 'SYS_IOT_TOP%'
and name not in (
'LOGOFF',
'SET ROLE',
'SESSION REC')
and not exists
(select 1 from dba_tablespaces dt
where b.name = 'ALTER TABLESPACE'
and a.obj$name = dt.tablespace_name
and dt.extent_management='LOCAL'
and a.terminal = (select host_name from v$instance)
and a.userid = 'SYSTEM'
);
exit;
1.3 Audit_Purge.sql
This script purges records older than x number of days.
set verify off;
delete from sys.aud$
where timestamp# < sysdate-&1;
commit;
exit;
1.4 Saveauditlog.bat
This script saves audit log by renaming the audit log file with a date timestamp.
@echo off
Rem %1 is database name
Rem get date
REM The FOR Loop gets the date convention, mm-dd-yyyy and saves the date part in three variables
FOR /f "tokens=2-4 skip=1 delims=(-)" %%G IN ('echo.^|date') DO (
SET v_first=%%G
SET v_second=%%H
SET v_third=%%I
REM FOR loop breaks date /t output and saves everything after blank delimiter, v_all has date
FOR /f "tokens=2 delims= " %%A IN ('date /t') DO (
SET v_all=%%A
)
)
REM saves date part values into date part variables
SET %v_first%=%v_all:~0,2%
SET %v_second%=%v_all:~3,2%
SET %v_third%=%v_all:~6,4%
copy e:\scripts\%1\sql\logs\auditreport.html e:\scripts\%1\auditlog\auditreport_%YY%%mm%%dd%.html
2. Auditsummary.bat
This is wrapper script which merges audit reports for all the databases and sends email. It reads Jobsinfo file to see if audit report is needed for a database. It looks at IfLastDDL settings, copies the report for a database that has Y in this column to auditsummary.html
@echo off
for /F "tokens=1-9" %%i in (E:\Scripts\Jobs\JobsInfo) do (
if exist e:\scripts\%%i\sql\logs\auditreport.html type e:\scripts\%%i\sql\logs\auditreport.html >> e:\scripts\auditsummary.html
)
cscript.exe e:\scripts\sendmailhtml.vbs "dbas@gotodba.com" "server1@gotodba.com" "Oracle Security Audit Summary Report" "e:\scripts\auditsummary.html" "HTML"
if exist e:\scripts\auditsummary.html del e:\scripts\auditsummary.html
JobsInfo
Format of JobsInfo
sid IfExport IfLastDDL IfStatsPack IfDelOldFiles IfIndexRbld IfRMan IfGatherStats Ifhotbackup
DB1 Y Y Y Y Y Y Y Y
DB2 Y Y Y Y Y Y N N
3. DailyOtherJobs.bat
This script also calls other jobs, but only audit_report.bat is listed here.
@echo off
for /F "tokens=1-9" %%i in (E:\Scripts\Jobs\JobsInfo) do (
if /I "%%k" equ "Y" (Call E:\scripts\sql\audit_report.bat %%i )
)
call E:\Scripts\AuditSummary.bat
4. SendmailHTML.Vbs
This is a VBScript program that calls Windows CDO object to send SMTP email. smtp.gotodba.com is the SMTP server name.
'**********************************************************************
' Visual Basic Script
'************************************************************************
' Usage: cscript.exe e:\scripts\sendmailhtml.vbs "to@gotodba.com" "from@gotodba.com" "Subject" "HTMLfile" "HTML"
'************************************************************************
const ForReading=1
Dim ObjArgs, MailTo, From, Subject, Message, wshshell, goFS, oFile, FileName, FileType
Set ObjArgs = WScript.Arguments
MailTo = ObjArgs(0)
From = ObjArgs(1)
Subject = ObjArgs(2)
FileName = ObjArgs(3)
if ObjArgs.Count = 5 then
FileType = ObjArgs(4)
end if
set wshshell=wscript.createobject("Wscript.Shell")
set goFs=createobject("Scripting.FileSystemObject")
'This code requires the file to exist to compile
set oFile = goFs.OpenTextFile(FileName, ForReading)
Message = oFile.ReadAll
Call SendMail(MailTo, From, Subject, Message)
oFile.Close
set wshshell = nothing
set goFs = nothing
set oFile = nothing
Function SendMail(MailTo, From, Subject, Message)
Dim iMsg
Set iMsg = CreateObject("CDO.Message")
With iMsg
.To = MailTo
.From = From
.Subject = Subject
If UCase(FileType) = "HTML" Then
.HTMLBody = Message
ElseIf UCase(FileType) = "ATTACHMENT" Then
.AddAttachment FileName
Else
.TextBody = Message
End If
End With
iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gotodba.com"
iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
iMsg.Configuration.Fields.Update
iMsg.Send
End Function