GOLD
This presentation is the property of its rightful owner.
Sponsored Links
1 / 35

GOLD PowerPoint PPT Presentation


  • 70 Views
  • Uploaded on
  • Presentation posted in: General

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:

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Gold

GOLD

SILVER

BRONZE


Oracle auditing coug presentation june 19 2014

Oracle AuditingCOUG Presentation – June 19, 2014

Ray Smith

June 2014


Oracle auditing

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.


Oracle auditing scope

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.


Oracle auditing presentation references

Oracle Auditing : Presentation References

Oracle Database Security Guide (11G) – E36292-05

Oracle Database Security Guide (12C) – E17607-25

SQL Language Reference (12C) – E17209-15


Oracle auditing mandatory auditing

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


Oracle auditing mandatory auditing 12c

Oracle Auditing – Mandatory Auditing (12c)

Quick peek - Demo


Oracle auditing standard database auditing

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$


Oracle auditing standard database auditing1

Oracle Auditing – Standard Database Auditing


Oracle auditing standard database auditing2

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)


Oracle auditing standard database auditing3

Oracle Auditing – Standard Database Auditing

How to query what objects are being audited?

DBA_OBJ_AUDIT_OPTS


Oracle auditing standard database auditing4

Oracle Auditing – Standard Database Auditing

How to query what statements are being audited?

DBA_STMT_AUDIT_OPTS


Oracle auditing standard database auditing5

Oracle Auditing – Standard Database Auditing

How to query what privileges are being audited?

DBA_PRIV_AUDIT_OPTS


Oracle auditing standard database auditing6

Oracle Auditing – Standard Database Auditing

What can be audited?

STMT_AUDIT_OPTION_MAP


Oracle auditing standard database auditing7

Oracle Auditing – Standard Database Auditing

What can be audited?

SYSTEM_PRIVILEGE_MAP


Oracle auditing standard database auditing8

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


Oracle auditing standard database auditing9

Oracle Auditing – Standard Database Auditing

Demo – Traditional Auditing


Oracle auditing standard database auditing10

Oracle Auditing – Standard Database Auditing

Performance testing

Database : 12c

Test – 10,000 individual connections & queries


Oracle auditing audit sys operations

Oracle Auditing – Audit SYS operations

Record operations performed by SYS / SYSOPER


Oracle auditing audit sys operations1

Oracle Auditing – Audit SYS operations

Auditing records created in the audit directory (OS)

Contents :


Oracle auditing fga

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


Oracle auditing fga1

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.


Oracle auditing fga2

Oracle Auditing - FGA

DBA_AUDIT_POLICIES


Oracle auditing fga3

Oracle Auditing - FGA

Demo - FGA


Oracle auditing unified audit trail 12c

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


Oracle auditing unified audit trail 12c1

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


Oracle auditing unified audit trail 12c2

Oracle Auditing – Unified Audit Trail (12c)

Banner changed when enabled


Oracle auditing unified audit trail 12c3

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


Oracle auditing unified audit trail 12c4

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


Oracle auditing unified audit trail 12c5

Oracle Auditing – Unified Audit Trail (12c)

Switching write modes:


Oracle auditing unified audit trail 12c6

Oracle Auditing – Unified Audit Trail (12c)

Flushing the audit trail:


Oracle auditing unified audit trail 12c7

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


Oracle auditing unified audit trail 12c8

Oracle Auditing – Unified Audit Trail (12c)

Demo


Oracle auditing unified audit trail 12c9

Oracle Auditing – Unified Audit Trail (12c)

Performance testing

Database : 12c

Test – 10,000 individual connections & queries


Oracle auditing1

Oracle Auditing

Thank you for listening


  • Login