Re: [External] Moving from database to OS audit trail

Depending on your OS configuration and how tight it is - you may be or not be able to use an external table and load the syslog file using the oracle loader.

Here's a practical example how you could do that:

https://www.morganslibrary.org/reference/externaltab.html

On Wed, Sep 16, 2020 at 8:00 PM Redacted sender Jay.Miller for DMARC < dmarc-noreply_at_freelists.org> wrote:

> We did some testing and I got some further clarification.
>
>
>
> Apparently the requirement is that all auditing be logged to the syslog
> file. The DBA on the project tried various settings and could not get it to
> populate both syslog and v$xml_audit_trail (see below for test results).
> Is there some setting we missed trying or is this just not possible?
>
>
>
>
>
> And if not possible, does anyone have an easy way to query the syslog file?
>
>
>
> Thanks!
>
>
>
>
>
> setting 1 -
>
>
>
> audit_file_dest string /app/oracle/diag/adump
>
> audit_sys_operations boolean TRUE
>
> audit_syslog_level string LOCAL7.INFO
>
> audit_trail string XML
>
> unified_audit_sga_queue_size integer 1048576
>
>
>
> 1. Setting audit_trail = XML creates xml audit entries under
> audit_file_dest location and so it shows with v$ view.
>
> It does not create audit entries in syslog directory (in this case it
> is under /app/oracle/diag/syslog).
>
> But since audit_sys_operations = TRUE, all sys operations (i.e. local)
> are stored in the syslog file
>
>
>
> setting 2 -
>
>
>
> audit_file_dest string /app/oracle/diag/adump
>
> audit_sys_operations boolean TRUE
>
> audit_syslog_level string LOCAL7.INFO
>
> audit_trail string XML, EXTENDED
>
> unified_audit_sga_queue_size integer 1048576
>
>
>
>
>
> 1. ran 'create user....' and i see the record in the syslog file (under
> /app/oracle/diag/syslog), but not in the XML file under audit_file_dest and
>
> not in the v$ view
>
> 2. ran 'drop user....' and i see the record in the syslog file (under
> /app/oracle/diag/syslog), but not in the XML file under audit_file_dest and
>
> not in the v$ view
>
>
>
> setting 3 -
>
>
>
> audit_file_dest string /app/oracle/diag/adump
>
> audit_sys_operations boolean TRUE
>
> audit_syslog_level string LOCAL7.INFO
>
> audit_trail string OS
>
> unified_audit_sga_queue_size integer 1048576
>
>
>
> 1. no records under audit_file_dest (expected) and no records in v$
> view(expected) for any audit (sys or other audit items like create/drop
> user, etc.)
>
> 2. ran 'create user, drop user' and all showed in the syslog file
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *dmarc-noreply_at_freelists.org
> *Sent:* Monday, September 14, 2020 1:26 PM
> *To:* jbeckstrom_at_gcrta.org; oracle-l_at_freelists.org
> *Subject:* RE: [External] Moving from database to OS audit trail
>
>
>
> Thank you! We’ll test that out (and cross our fingers that the format is
> acceptable to the security folk).
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Jeffrey Beckstrom
> *Sent:* Monday, September 14, 2020 11:39 AM
> *To:* oracle-l-freelist <oracle-l_at_freelists.org>; Miller, Jay <
> Jay.Miller_at_tdameritrade.com>
> *Subject:* Re: [External] Moving from database to OS audit trail
>
>
>
> We send our audit trail to xml audit trail files. We then query it
> from v$xml_audit_trail
>
>
>
> Jeffrey Beckstrom
> Lead Database Administrator
>
> Information Technology Department
>
> Greater Cleveland Regional Transit Authority
>
> 1240 W. 6th Street
> Cleveland, Ohio 44113
>
>
>
> >>> "" (Redacted sender "Jay.Miller" forDMARC) <
> dmarc-noreply_at_freelists.org> 9/14/20 11:31 AM >>>
>
> We have just been given the requirement to move our auditing from database
> to OS and I was wondering how other people have handled obtaining the data
> which is currently easily available from dba_audit_trail.
>
>
>
> For example things like getting a histogram of login times to see if there
> was a sudden surge in connect activity or finding the name of an app server
> which is locking an account by sending invalid passwords. Really easy now
> but with OS files? How are other people handling this?
>
>
>
> I’m told all the information will be available in Splunk though I have no
> idea how easy that will be to access.
>
>
>
>
>
> TIA,
>
> Jay Miller
>
>
>
>
>
>
>
>
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l