1 / 35

GOLD

GOLD. SILVER. BRONZE. Oracle Auditing COUG Presentation – June 19, 2014. Ray Smith June 2014. Oracle Auditing. Objective : What is available to the DBA with regard to auditing How do you configure the various options What are the impacts of setting up the various options Caveats:

ann-lamb
Download Presentation

GOLD

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. GOLD SILVER BRONZE

  2. Oracle AuditingCOUG Presentation – June 19, 2014 Ray Smith June 2014

  3. Oracle Auditing • Objective : • What is available to the DBA with regard to auditing • How do you configure the various options • What are the impacts of setting up the various options • Caveats: • Based on personal experience • Tests are performed on Oracle Virtualbox(Linux) with RDBMS 12c • Not real data in examples.

  4. Oracle Auditing: Scope During this presentation I would like to cover Mandatory Auditing Standard Database Auditing Audit SYS operations Fine Grained Auditing And now in 12c….. The Unified Audit Trail Excludes : Oracle Database Vault Audit.

  5. Oracle Auditing : Presentation References Oracle Database Security Guide (11G) – E36292-05 Oracle Database Security Guide (12C) – E17607-25 SQL Language Reference (12C) – E17209-15

  6. Oracle Auditing – Mandatory Auditing What is always on: Database Startup / Shutdown Sysdba / Sysoper logons And now in 12c – (if unified auditing is switched on) : Auditing changes – changes made to auditing • Create/Alter/Drop audit policies • Audit/Noaudit actions • Execution of FGA / DBMS_AUDIT_MGMT packages • Alter table statements run on the AUDSYS table • ‘Top level statements by the administrative users ..until the database is opened’. • Database vault changes

  7. Oracle Auditing – Mandatory Auditing (12c) Quick peek - Demo

  8. Oracle Auditing – Standard Database Auditing Henceforth known as Traditional Auditing Oracle includes the Traditional Auditing for backwards compatibility Oracle Recommends you plan to move away from this type of auditing. Requires the database parameter set to something other than ‘none’ • 12c – default setting none (in documentation) but it was set to DB when I installed using DBCA (custom installation). • Options available : • none | os | db [, extended] | xml [, extended] Turn on : AUDIT command Turn off : NOAUDIT command Data stored in SYS.AUD$

  9. Oracle Auditing – Standard Database Auditing

  10. Oracle Auditing – Standard Database Auditing Audit examples Audit create session; -- will record all log on and log off actions Audit create session by rsmith; -- will record all rsmith’s log on/off Audit select on hr.employee by access; -- will capture who/what is querying the hr.employee table (every time) Audit select on hr.employee by session ; -- will capture who/what is querying the hr.employee table (grouped per session)

  11. Oracle Auditing – Standard Database Auditing How to query what objects are being audited? DBA_OBJ_AUDIT_OPTS

  12. Oracle Auditing – Standard Database Auditing How to query what statements are being audited? DBA_STMT_AUDIT_OPTS

  13. Oracle Auditing – Standard Database Auditing How to query what privileges are being audited? DBA_PRIV_AUDIT_OPTS

  14. Oracle Auditing – Standard Database Auditing What can be audited? STMT_AUDIT_OPTION_MAP

  15. Oracle Auditing – Standard Database Auditing What can be audited? SYSTEM_PRIVILEGE_MAP

  16. Oracle Auditing – Standard Database Auditing Views to query DBA_AUDIT_TRAIL - complete audit list DBA_AUDIT_STATEMENT – audit system changes DBA_AUDIT_SESSION - audit sessions DBA_AUDIT_OBJECT - audit objects V$XML_AUDIT_TRAIL – complete audit if XML is used DBA_AUDIT_EXISTS - audit failure

  17. Oracle Auditing – Standard Database Auditing Demo – Traditional Auditing

  18. Oracle Auditing – Standard Database Auditing Performance testing Database : 12c Test – 10,000 individual connections & queries

  19. Oracle Auditing – Audit SYS operations Record operations performed by SYS / SYSOPER

  20. Oracle Auditing – Audit SYS operations Auditing records created in the audit directory (OS) Contents :

  21. Oracle Auditing - FGA Points to note Traditional auditing is object based. FGA auditing has a more granular approach • Can be column specific • Can be column value specific • Can be time specific (disabled/enabled by trigger) • Managed by policies which can be queried in DBA_AUDIT_POLICIES • Data Stored in SYS.FGA_LOG$ • View: DBA_FGA_AUDIT_TRAIL • Configured using DBMS_FGA package

  22. Oracle Auditing - FGA Interesting notes If you audit a table which is accessed via a view, then the OBJECT_NAME in the Audit Trail will be the table being audited, but the sql text will be the query against the view There’s a handler_module that can trigger events, for example – send alert to the DBA if a particular audited activity occurs.

  23. Oracle Auditing - FGA DBA_AUDIT_POLICIES

  24. Oracle Auditing - FGA Demo - FGA

  25. Oracle Auditing – Unified Audit Trail (12c) Basic concept SYS.AUD$ (traditional) SYS.FGA_LOG$ (fga) V$XML_AUDIT_TRAIL (XML) OS FILES (SYS / MANDATORY) ORACLE VAULT AUDIT SYS.UNIFIED_AUDIT_TRAIL

  26. Oracle Auditing – Unified Audit Trail (12c) To setup you have to build the appropriate libraries (with all databases / listener in the $HOME shut down) cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk uniaud_onioracle To turn off you have to rebuild with the option turned off cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk uniaud_offioracle

  27. Oracle Auditing – Unified Audit Trail (12c) Banner changed when enabled

  28. Oracle Auditing – Unified Audit Trail (12c) Points to note Mixed modes are supported Policy managed by ‘Create Audit Policy’ commands Supposed to be faster than previous auditing because it utilizes SGA for auditing with periodic ‘flushes’. Data stored in Read-only area Managed by AUDSYS user, which cannot connect to oracle directly Two roles for auditing : Audit_Admin & Audit_viewer

  29. Oracle Auditing – Unified Audit Trail (12c) Different write modes Immediate write mode • Audit records are immediately written to disk • May have a performance impact Queued write mode • Audit written to SGA • Flushed manually / automatically at intervals • Possible risk of audit loss after crash

  30. Oracle Auditing – Unified Audit Trail (12c) Switching write modes:

  31. Oracle Auditing – Unified Audit Trail (12c) Flushing the audit trail:

  32. Oracle Auditing – Unified Audit Trail (12c) Mandatory auditing on • Create/Alter/Drop audit policies • Audit/Noaudit actions • Execution of FGA / DBMS_AUDIT_MGMT packages • Alter table statements run on the AUDSYS table • ‘Top level statements by the administrative users ..until the database is opened’. • Database vault changes

  33. Oracle Auditing – Unified Audit Trail (12c) Demo

  34. Oracle Auditing – Unified Audit Trail (12c) Performance testing Database : 12c Test – 10,000 individual connections & queries

  35. Oracle Auditing Thank you for listening

More Related