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

I remember submitting an enhancement request to Oracle years ago asking for the ability to log to both os and db simultaneously. That would give both the security of 'os' and query capabilities of 'db'. I think that faded into oblivion.

Henry

On Thu, Sep 17, 2020 at 1:30 AM Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> 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