The main idea I am trying to advocate with these posts is a simple one. 

Compare a database you are auditing against a database that you know already meets the standards required by the organisation you are auditing.

This is achieved by creating “CSV type” files.  This makes it easier to import the information into your favourite CAATs tool (e.g. IDEA, ACL etc).  In previous posts we imported the files in groups.  In reality you would import them all at once.

I have uploaded the Oracle script to the Oracle documents section or you may access it directly here http://www.isaca.org/Groups/Professional-English/oracle-database/GroupDocuments/Oracle.SQL.

The document contains all the views previously discussed and maybe one or two others.   There are also many other Oracle views that should be considered and could be imported in the same way.  These can be seen at http://docs.oracle.com/cd/B19306_01/server.102/b14237/index.htm.  It really depends on what you are auditing.

Once you have decided on your views have the DBA run the script in SQL Plus (I have achieved erratic results with other tools).  This is achieved by simply typing @<script location>\oracle.sql at the SQL Plus prompt.  It needs DBA authority to run.

The script should produce CSV type files.  If, however you need to adjust any of the settings they are described below;

SET LINES 10000 - sets the total number of characters that can display on one line before beginning a new line
SET TRIMSPOOL ON - remove trailing spaces from output
SET PAGESIZE 10000 - the number of rows on each page of output
SET TIMING OFF - suppress timing statistics
SET ECHO OFF - turn command echoing off
SET FEEDBACK OFF – suppress display of the number of records returned
SET COLSEP "," - set the character to be printed between columns
SET UNDERLINE OFF – turn off underline on column headings
SET NEWPAGE 0 – prevents a blank line displaying before the column headings

The CSV files are now ready to import into your CAATs tool.

To fully utilise the power of CAATs (I use IDEA) set the tool to “record” all the import steps (and indeed the tests themselves if you wish).  This can then be saved as a script.  This script can then be run over and over again by simply pointing it at your (new) CSV files.

Many of the Oracle views discussed in previous posts are all utilised in the excellent Security, Audit and Control Features Oracle Database, 3rd Edition Excerpt audit program.

If you use this program you could create an Oracle script to output these views to CSV type files.  These could then be imported into your CAATs tool.  As noted previously the import and tests could be scripted within the tool.

This is really what I am advocating – using the power of CAATs to automate audit testing by comparing against something you know to be configured to standard.