Re: Sarbanes Oxley reporting

Thanks for the input Mark.

We too had to map users to roles in the app. Fortunately I did not have to deal with that one.

I left out one report earlier. A list of all database files with permissions.

Jared

On 2/12/07, Powell, Mark D <mark.powell_at_eds.com> wrote:

>

> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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
>
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l