1 / 34

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

bonita
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Auditing the DBA:What 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 DBA’s ability to overcome them.

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

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

  6. DBA’s 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. 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

  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 can’t 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 User’s Data • SYSOPER (Default schema is PUBLIC) • Same as above, but… • Can’t Access other User’s 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 user’s 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. But…the 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 But…the SYS user can Disable the triggers!

  19. 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

  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 DBA Database 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 Tables Views Procedures A user which owns objects Return

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

More Related