RE: Sarbanes Oxley reporting

We produce the reports that the outside auditors said we had to produce.
We list every usage of the DBA ID's on production.  We also have to list
every usage of the SYS ID so we concatenate the Oracle produced
$ORACLE_HOME/rdbms/audit files by database into a "report".  This report
is useless, but it is what the auditors asked for.  The report is
emailed to management every week and they have to keep a record
acknowledging that the reports have been reviewed.
 
Our big effort during our recent audit was to map every end-user to the
capabilities of every application screen that the user had access to and
comparing this to the limitations of the user job position.  That is, if
you have job X you cannot have the capabilities to perform task Y2 such
as the person who orders material cannot be the perform who authorizes
payment of an invoice for obvious reasons.  Only in our case the review
was carried down to the ability to update small pieces of information
deemed reverent to each job.  IT had long ago created a user to program
mapping report, but the addition of the user position restrictions to
the reporting was a major task.  Many customers lost access to specific
screens and some programs had to be changed to not be able to update
certain columns.  We can now provide this information on an ongoing
basis.
 
-- Mark D Powell -- 
Phone (313) 592-5148 
 



________________________________

        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
        Sent: Monday, February 12, 2007 1:48 PM
        To: Oracle-L Freelists
        Subject: Sarbanes Oxley reporting
        
        

        Those of you that work in the USA, or even for USA based
        companies know what I'm referring to here.
        
        I am curious what type of reporting you do to document the 
        tests for your SOX controls in regards to Oracle databases. 
        
        The Sarbanes Oxley legislation is rather loosely written,
        leaving its interpretation pretty much open to negotiation
        between a company and its auditors.
        
        The reason for asking is that my reports have been accused 
        of being too verbose, or rather, have too much data, and not
        enough information.
        
        I disagree, and if anyone reading them would simply read
        the instructions on the first page, they would not have so
        much difficulty.  
        
        But I digress.
        
        It would be most interesting to see what kind of reports others
        are using to Satisfy SOX requirements for Oracle.
        
        Comments on possibly improving the reports I am currently
        generating would also be welcome. 
        
        The following types of reports for Sarbanes Oxley
        are supplied for various tests throughout the year.  
        All are in Excel.
        
        Each Excel file is for a single database.
        
        ** database_roles.xls - a list of all roles, with all privileges

        granted to all roles. It is recursive, in that when a role
        is assigned to a role, it can be clicked on to drill down
        on the privileges.  One role per page.
        
        This one does require some guidance if the auditor is 
        unfamiliar with the privileges.
        
        ** oar.xls - oracle application roles - (something of a misnomer
here)
        This Excel file includes the following worksheets:
        
          System Accounts:  All known Oracle/Application/Busisness
specific 
          database accounts are documented here, with the account owner
and purpose.
          eg. SYSTEM, SCOTT, SAPR3, FNDAPP, etc.  accounts
        
          Known Roles : Similar to System Accounts, but for Database
Roles
        
          Account Reconciliation: List of accounts in the database.
Those not found 
          in the System Accounts worksheet are flagged in Red.
        
          Access Reconciliation: Roles assigned to accounts.  Accounts
not found
          in the System Accounts worksheet are flagged in Red.
        
          Role2User:  Roles assigned to users/roles, in ROLE order.
Unknown accounts 
          flagged in red.
        
          Role2Privilege:  Similar to Role2User, but for individual
privileges.
        
          User2Privilege:  Similar to Role2User, but for user/role
mapping to
          privileges, in user/role order.
        
        ** ora_parms.xls - database parameters report. This reports
those
        parameters that are deemed relevant to security. eg.
REMOTE_OS_AUTHENT
        
        ** profiles.xls - report on database profiles
        one page for each profile (including DEFAULT) and a page 
        for the function used for password verification for each
profile.
        
        ** sessaud.xls - report on logons/logon attempts 
        basically a dump of dba_audit_sessions for the 
        relevant time period.  Not really much use other 
        than as evidence of which accounts have logged on to
        the database. Excel's autofilter feature makes it
        easy to see the list of accounts that have logged on.
        
        ** baseline.xls - this report details what objects 
        have changed in the database, and when.  One worksheet
        for each type of object.  Ideally one should be able
        to trace a changed object back to a change control
        ticket.
        
        Changed/new/deleted objects are highlighted in blue. 
        
        ---
        
        That does it for my reports.  I'm looking for ideas on
        how to consolidate this data into something auditor are
        more likely to read without looking quite so confused 
        when doing so.
        
        Discussion of how others are satisfying SOX reporting 
        requirements for Oracle databases should be interesting
        and useful.
        
        
        Jared

Other related posts: