1. finance
2. accounting and auditing

GOLD
SILVER
BRONZE
Oracle Auditing
COUG Presentation – June 19, 2014
Ray Smith
June 2014
© CGI Group Inc. 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:
• Based on personal experience
• Tests are performed on Oracle Virtualbox (Linux) with RDBMS 12c
• Not real data in examples.
3
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.
4
Oracle Auditing : Presentation References
• Oracle Database Security Guide (11G) – E36292-05
• Oracle Database Security Guide (12C) – E17607-25
• SQL Language Reference (12C) – E17209-15
5
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
6
Oracle Auditing – Mandatory Auditing (12c)
• Quick peek - Demo
7
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$
8
Oracle Auditing – Standard Database Auditing
9
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)
10
Oracle Auditing – Standard Database Auditing
How to query what objects are being audited?
• DBA_OBJ_AUDIT_OPTS
11
Oracle Auditing – Standard Database Auditing
How to query what statements are being audited?
• DBA_STMT_AUDIT_OPTS
12
Oracle Auditing – Standard Database Auditing
How to query what privileges are being audited?
• DBA_PRIV_AUDIT_OPTS
13
Oracle Auditing – Standard Database Auditing
What can be audited?
• STMT_AUDIT_OPTION_MAP
14
Oracle Auditing – Standard Database Auditing
What can be audited?
• SYSTEM_PRIVILEGE_MAP
15
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
16
Oracle Auditing – Standard Database Auditing
Demo – Traditional Auditing
17
Oracle Auditing – Standard Database Auditing
Performance testing
Database : 12c
Test – 10,000 individual connections & queries
Action
Average time
noaudit
9:31
Audit create session (DB)
9:40
Audit create session (OS)
10:06
Audi Select by Access (DB)
9:40
Audit create session + Select by Access
9:40
18
Oracle Auditing – Audit SYS operations
Record operations performed by SYS / SYSOPER
19
Oracle Auditing – Audit SYS operations
Auditing records created in the audit directory (OS)
Contents :
20
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
21
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.
22
Oracle Auditing - FGA
DBA_AUDIT_POLICIES
23
Oracle Auditing - FGA
Demo - FGA
24
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
25
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_on ioracle
To turn off you have to rebuild with the option turned off
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_off ioracle
26
Oracle Auditing – Unified Audit Trail (12c)
Banner changed when enabled
27
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
28
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
29
Oracle Auditing – Unified Audit Trail (12c)
Switching write modes:
30
Oracle Auditing – Unified Audit Trail (12c)
Flushing the audit trail:
31
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
32
Oracle Auditing – Unified Audit Trail (12c)
Demo
33
Oracle Auditing – Unified Audit Trail (12c)
Performance testing
Database : 12c
Test – 10,000 individual connections & queries
Action
Average time
noaudit
9:31
Audit create session (DB)
9:40
Audit create session (OS)
10:06
Audi Select by Access (DB)
9:40
Audit create session + Select by Access
9:40
Audit create session (UNIFIED) queue mode 10:01
34
Oracle Auditing
Thank you for listening
35