Auditing the DBA: What non-technical managers and auditors should know.

Auditing the DBA:What non-technical managers and auditors should know. Presented By Cam Larner President Absolute Technologies, Inc. January 17, 2007 Version 1

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

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 DBA’s ability to overcome them.

Outline • Database Basics • Auditing the DBA • Issues • Recommendations

DBA & Database Basics • DBA’s Primary Functions • Database Objects • Database Access • Database Operations • DBA Access in Oracle 9i / E Biz Suite Next

DBA’s Primary Functions • Database Creation, Startup and Shutdown • Application Implementation/Upgrade • Maintenance, Backup & Recovery • Performance Optimization • Security/User Management • Trouble Shooting Return

Database Objects Users Roles Database Links Views Procedures Indexes Tables Packages Synonyms Triggers Functions Sequences Return

User Role Privilege Privilege 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

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

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

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 can’t alter SYS objects. • APPS …The E Business Suite Master Account • APPLSYS …The Application Object Library Master Account Return

Administrative Privileges • SYSDBA (Default schema is SYS) • Database creation • Instance startup and shutdown • Archive and Recovery • Can Access any User’s Data • SYSOPER (Default schema is PUBLIC) • Same as above, but… • Can’t Access other User’s Data Return

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

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

File System Entry Points to the Database • FNDCPASS file executable (Change an application user’s password as changed by the FND “anonymous” user) • $ORACLE_HOME/reports60/server/CGIcmd.dat (Contains APPS password) Return

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

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. But…the SYS user owns the audit trail!

Approaches to Auditing the DBA • Database Triggers (Application Auditor) • Table Level (DML) • System Level • DDL • Session connection • Server errors • Database startup But…the SYS user can Disable the triggers!

Approaches to Auditing the DBA But…the 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

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 DBA Database Operating System

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)

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

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

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

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.

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

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

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.

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

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.

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

Thank You! www.absolute-tech.com

User Vs. Schema User Schema Connects to the database Tables Views Procedures A user which owns objects Return

E- Biz Suite Schema Map End User SYS APPS SCOTT PUBLIC SYSTEM APPLSYS GL AP AR

Auditing the DBA: What non-technical managers and auditors should know.