sn01: primer: auditing oracle database...

www.arcsight.com 1© 2010 ArcSight Confidential

© 2010 ArcSight, Inc. All rights reserved. ArcSight and the ArcSight logo are trademarks of ArcSight, Inc. All other product and company names may be trademarks or registered trademarks of their respective owners.

SN01: Primer: Auditing Oracle Database Activity

Tom D’AquinoSenior Curriculum Developer

September 2010


Agenda

Introduction– Why collect Oracle audit logs– Which Oracle audit logs should be collected– How to enable Oracle auditing

ArcSight SmartConnectors– Options– What to watch for

Use cases– SQL injection attacks– Privilege escalation– Access abuse by authorized users

Content– Rules– Dashboards/Data Monitors– Reports

Conclusion


Introduction


Why Collect Oracle Audit Logs?

Attacks on Databases is old news– July 19, 2006: “Hackers are striking databases in record numbers, trying to

pilfer a rich trove of personal and financial data…”http://www.computerworld.com/s/article/9001878/SQL_injection_attacks_against_databases_rise_sharply

Trend continues but with more sophistication– February 9, 2010: “SQL injections have evolved in their purpose and

sophistication. Originally meant as a tool to attack a merchant’s database and steal data. The attack was reconfigured last summer to install viruses on users’ computers that contain a remote control component.”http://information-security-resources.com/2010/02/09/targeted-sequel-injection-attacks-on-the-rise/

Knowledge is POWER!– You’ll be surprised by what you find…

Introduction

http://www.computerworld.com/s/article/9001878/SQL_injection_attacks_against_databases_rise_sharply�

http://www.computerworld.com/s/article/9001878/SQL_injection_attacks_against_databases_rise_sharply�

http://information-security-resources.com/2010/02/09/targeted-sequel-injection-attacks-on-the-rise/�

http://information-security-resources.com/2010/02/09/targeted-sequel-injection-attacks-on-the-rise/�


Which Oracle Audit Logs Should Be Collected?

DB audit trail– Audits regular user activity

• I recommend DB, extended but OS is also an option (writes data to OS file) ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE; Requires restart of DB

• DB,extended logs all activity to sys.aud$ table in the database Audit SYS operations

– Audits DBA user activity• I recommend enabling SYS auditing ALTER SYSTEM SET audit_sys_operations=true SCOPE=SPFILE; Requires restart of DB

• SYS operations are logged to OS audit log Assumes DBA does not have control over OS audit log

Show parameter audit– Shows status of Oracle audit configuration

Introduction


Which Oracle Audit Logs Should Be Collected? – Example

Introduction


How to Enable Oracle Auditing

Audit statements Be careful with these statements

– AUDIT ALL• Audits alter system commands, create table, etc.

– AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE• Not included with the “AUDIT ALL” statement

– AUDIT EXECUTE PROCEDURE• Not included with the “AUDIT ALL” statement

Query several views to see audit status– DBA_STMT_AUDIT_OPTS– DBA_PRIV_AUDIT_OPTS– DBA_OBJ_AUDIT_OPTS

Introduction


How to Enable Oracle Auditing

Query several views to see audit status DBA_STMT_AUDIT_OPTS DBA_PRIV_AUDIT_OPTS DBA_OBJ_AUDIT_OPTS

Introduction


ArcSight SmartConnectors


Collecting Regular User Audit Logs

Oracle Audit DB SmartConnector– Collects audit logs written to DBA_AUDIT_TRAIL view when audit_trail

is set to db or db,extended– Requires select privileges on following tables/views

• DBA_AUDIT_TRAIL view• DBA_COMMON_AUDIT_TRAIL view• V$Instance table

– Retrieves audit logs from Oracle database remotely via JDBC connection

Oracle audit Syslog SmartConnector– Collects audit logs written to Syslog when audit_trail is set to OS– Syslog Daemon, Syslog File and Syslog Pipe SmartConnectors can be

used



Collecting SYS Operations Audit Logs

Oracle audit Windows event Log– On a Windows server, SYS operations are logged to Windows

application event Log– ArcSight SmartConnector will process Oracle SYS audit events

Oracle SYSDBA Audit (multiple options)– On a Unix server, SYS operations are logged to an OS file in

ORACLE_HOME• Files are usually named “ora_<pid>.aud”• One file exists for each SYSDBA session• File is updated continously until SYSDBA session ends



Oracle Instances – Single vs. Multiple

Single Oracle instance– Use Oracle SYSDBA Audit DB to process audit logs– ArcSight SmartConnector must be installed on same server as Oracle

Multiple Oracle instances– Use Oracle SYSDBA Multiple Folder Audit DB to process audit logs– ArcSight SmartConnector can be installed on separate server from Oracle– Processes events in “batch” mode or “realtime” mode– Batch mode requires an external script to move complete audit files to a new

folder for processing– Realtime mode requires a database user for querying remote databases to

identify ongoing SYSDBA sessions



Use Cases


SQL Injection Attacks

Web users insert unverified input

Database application user performs unusual queries– Use of “union” SQL statement

• select … where name = ‘test’ union select banner from v$version where ‘1’=‘1’

– Retrieving table names• select … where name = ‘test’ union select object_name from sys.all_objects

where ‘1’=‘1’

– Retrieving column names• select … where name = ‘test’ union select column_name from

sys.all_tab_columns where ‘1’=‘1’

Use Cases


SQL Injection Attacks

Privilege escalation– Regular user creates a PL/SQL package to exploit Oracle privilege

escalation vulnerability• Package contains SQL EXECUTE IMMEDIATE 'GRANT DBA TO MYUSER';

• Creating and executing the package will generate audit logs

Access abuse by authorized users– SYSDBA viewing production user data such as SSN, credit card, etc.

Use Cases


Content


Rules – SQL Injection Attack

Rule conditions

Content


Rules – Privilege Escalation Attack

Rule conditions

Content


Rules – Oracle Access Abuse

Rule conditions

Content


Dashboards and Data Monitors

Oracle events and Oracle alerts filters

Content



Top Oracle users and top Oracle events data monitors

Content



Oracle status data monitor

Content


Sample Dashboard

Content


Reports – Query for Oracle Events

Content


Reports – Query for Oracle Users

Content


Reports – Query for Oracle Alerts

Content


Sample Report Output

Content


Conclusion

Collecting Oracle audit data is well worth the effort Lots of Oracle audit collection options Lots of opportunities for use cases and content


Your Feedback Builds a Better Conference!

Download session replays after the conference:https://protect724.arcsight.com/community/protect10/sessions

Excellent Good Fair Poor

Rate the speaker a b c d

Rate the content e f g h

Please provide comments: (*) enter any comments/feedback

Text to 32075 (USA & Canada) or 447786204951 (Non-USA) Type ARCS <space> 01 and the letter to each response

SMS body example: ARCS 01ae*your comments


ArcSight, Inc.Corporate Headquarters: 1 888 415 ARST

EMEA Headquarters: +44 (0)844 745 2068Asia Pac Headquarters: +65 6248 4795

www.arcsight.com

sn01: primer: auditing oracle database...

Documents