Auditing the DBA: What nontechnical managers and auditors should know'

1
Auditing the DBAWhat non-technical managers and
auditors should know.

• Presented By
• Cam Larner
• President
• Absolute Technologies, Inc.
• January 17, 2007 Version 1

2
Intro

• You are a manager or project lead
• You need to secure E-Biz Suite for SOX compliance
  purposes
• You have or are implementing controls for
  application end users
• Your DBA has the access and power to overcome or
  tamper with these controls without detection
• You need to mitigate DBA risk

3
Background

• In the context of SOX, external auditors are
  beginning to scrutinize DBA access and requesting
  controls and systematic proof of such to attain
  compliance.
• After all, the systematic controls you have
  established for application end users will have
  little impact on your DBAs ability to overcome
  them.

4
Outline

• Database Basics
• Auditing the DBA
• Issues
• Recommendations

5
DBA Database Basics

• DBAs Primary Functions
• Database Objects
• Database Access
• Database Operations
• DBA Access in Oracle 9i / E Biz Suite

Next
6
DBAs Primary Functions

• Database Creation, Startup and Shutdown
• Application Implementation/Upgrade
• Maintenance, Backup Recovery
• Performance Optimization
• Security/User Management
• Trouble Shooting

Return
7
Database Objects
Users
Roles
Database Links
Views
Procedures
Indexes
Tables
Packages
Synonyms
Triggers
Functions
Sequences
Return
8
Database Access
User vs Schema?

• Users
• Connect to the database
• Privileges
• Provide access to specific data or objects
• Roles
• Bundle privileges for easy assignment to users

Return
9
Database Operations

• Select Data from Tables and Views
• DML Insert, Update and Delete Records
• DDL Create, Alter and Drop Objects
• Startup and Shutdown Database

Return
10
DBA Access in Oracle 9i / E Biz Suite

• Default Database Users/Schemas
• Administrative Privileges
• Administrative Roles
• Connection Authentication
• File System Entry Points to the Database
• Application Access

The DBA has the keys to the kingdom!
Next
11
Default Database Users/Schemas

• SYS
• The master account. Owns the Data Dictionary.
• PUBLIC
• All users have access to this Schema.
• SYSTEM
• Has all DB privs, but cant alter SYS objects.
• APPS
• The E Business Suite Master Account
• APPLSYS
• The Application Object Library Master Account

Return
12
Administrative Privileges

• SYSDBA (Default schema is SYS)
• Database creation
• Instance startup and shutdown
• Archive and Recovery
• Can Access any Users Data
• SYSOPER (Default schema is PUBLIC)
• Same as above, but
• Cant Access other Users Data

Return
13
Administrative Roles

• DBA
• (All system privileges WITH ADMIN OPTION)
• SELECT_CATALOG_ROLE
• (Data Dictionary Views)
• EXECUTE_CATALOG_ROLE
• (Data Dictionary Packages and Procedures)
• DELETE_CATALOG_ROLE
• (AUD Table)

Return
14
Connection Authentication

• Oracle Operating System Account Groups
• OSDBA (dba in unix)
• OSOPER (oper in unix)
• REMOTE_LOGIN_PASSWORDFILE
• None
• Exclusive
• Shared
• O7_DICTIONARY_ACCESSIBILITY TRUE
• Users may be granted access to SYS
• Users may logon to SYS remotely and without OS
  authentication

Return
15
File System Entry Points to the Database

• FNDCPASS file executable
• (Change an application users password as
  changed by the FND anonymous user)
• ORACLE_HOME/reports60/server/CGIcmd.dat
  (Contains APPS password)

Return
16
Application Access

• SYSADMIN via APPS User
• EXAMINE via APPS User
• All underlying tables of E-Biz Suite

Return
17
Approaches to Auditing the DBA

• SQL Audit (AUDIT_TRAIL TRUE)
• Database Initialization Parameter
• Session
• When a user logs in or out of the database.
• Statement
• When a user tries to delete any table.
• Privilege
• When a user tries to delete a table using an
  assigned privilege.
• Object
• When a user tries to delete a specific table.

Butthe SYS user owns the audit trail!
18
Approaches to Auditing the DBA

• Database Triggers (Application Auditor)
• Table Level (DML)
• System Level
• DDL
• Session connection
• Server errors
• Database startup

Butthe SYS user can Disable the triggers!
19
Approaches to Auditing the DBA
Butthe SYS user can Disable Log Miner, FGA, or
any init parameter.

• Log Miner
• Redo and Archive Logs
• DML
• DDL
• Fine Grained Auditing (FGA)
• Monitor select statements at the row level.
• AUDIT_SYS_OPERATIONS TRUE
• Database Initialization Parameter

20
Issues / Discussion

• Common Misconception
• Should we audit at the Application or Database
  level?

End User
Data is not stored in the application layer,
but in the database layer.
Application
On Commit
Database
DBA
Operating System
21
Issues / Discussion

• When is SYSDBA access necessary?
• SYSDBA has control over SYS objects, AUD (the
  SQL Audit table) and Initialization Parameters
• Alternatives to Support DBA role
• SYSOPER (Startup and Shutdown)
• SYSTEM (Maintenance, Security)
• NAMED ACCOUNT w/ DBA Role (Maintenance, Security)

22
Issues / Discussion

• Securing audit mechanisms from the DBA
• Triggers
• Logminer views, redo and archive logs
• SYS.AUD audit trail table
• File system audit directories
• Database initialization parameters
• AUDIT_FILE_DEST
• AUDIT_SYS_OPERATIONS
• AUDIT_TRAIL

23
Issues / Discussion

• Operating system authentication takes precedence
  over password file authentication. Specifically,
  if you are a member of the OSDBA or OSOPER group
  for the operating system, and you connect as
  SYSDBA or SYSOPER, you will be connected with
  associated administrative privileges regardless
  of the username/password that you specify.
• Oracle9i Database Administrator's Guide

24
Recommendations

• Segregate DBA duties and access
• Database and Application Support
• Security, Access and Auditing
• Limit use of SYSDBA
• Limit OS user assignment of the dba group
• Utilize named accounts when possible

25
Recommendations

• Audit DBA activity on Key Application Objects
• Triggers (Application Auditor)
• Log Miner
• SQL Audit
• Activate AUDIT_SYS_OPERATIONS
• Initialization Parameter
• Protect the AUDIT_FILE_DEST log directory from
  the DBA
• Copy audit log files to secure directories
• Rsync (unix)
• Unison (unix)

Ask how App Auditor can help you secure the
audit trail.
26
Recommendations

• It is suggested that you create at least one
  additional administrator user, and grant that
  user the DBA role, to use when performing daily
  administrative tasks. It is recommended that you
  do not use SYS and SYSTEM for these purposes.
• Oracle9i Database Administrator's Guide

27
Recommendations

• To maintain the integrity of the data
  dictionary, tables in the SYS schema are
  manipulated only by Oracle. They should never be
  modified by any user or database administrator,
  and no one should create any tables in the schema
  of user SYS.
• Oracle9i Database Administrator's Guide

28
Commentary

• It may be said by many DBAs that the DBA role is
  a trusted role, or that a good DBA could overcome
  almost any restrictions or audit trail deployed
  for control and compliance purposes.
• Whether that is true or not, is not the point.
• The reality is that external auditors are
  starting to scrutinize DBA access and requesting
  controls and systematic proof of such to attain
  compliance. Any particular approach may not be
  bullet proof, but each hurdle or preventive
  measure deployed reduces the overall risk as
  assessed by the auditor.

29
Hurdles to Mitigate Risk
Fraud
DBA
Use Named Accounts
Limit SYSDBA Usage
Audit Access
Audit Transactions
Secure Audit Trail
30
Application Auditor

• Audit/Alert/Prevent
• DML transactions
• DDL operations
• DBA activity
• IT Staff activity
• Application user activity
• Audit Session Connections
• Audit Server Errors
• Secure the Audit Schema from the DBA
• Visit www.absolute-tech.com to lean more.

31
References

• Oracle9i Database Administrator's Guide Release 2
  (9.2)
• Oracle Privacy Security Auditing
• by Arup Nanda Donald K. Burleson

32
Thank You! www.absolute-tech.com
33
User Vs. Schema

User
Schema
Connects to the database
Views
Tables
Procedures
A user which owns objects
Return
34
E- Biz Suite Schema Map
End User
SYS
APPS
SCOTT
PUBLIC
AP
GL
SYSTEM
APPLSYS
AR