Decoder for oracle audit log
507 views
Skip to first unread message
mariano hinjos
Dec 21, 2021, 9:59:25 AM12/21/21
to Wazuh mailing list
Hi
I have a path with different oracle .aud files
-rw-r----- 1 oracle dba 1259 Dec 16 18:16 TESTWSL_ora_28346_20211216165343648403143795.aud
-rw-r----- 1 oracle dba 1259 Dec 16 18:16 TESTWSL_ora_28316_20211216165316120689143795.aud
-rw-r----- 1 oracle dba 794 Dec 21 12:49 TESTWSL_ora_20462_20211221124953748563143795.aud
-rw-r----- 1 oracle dba 793 Dec 21 12:50 TESTWSL_ora_20471_20211221125000657156143795.aud
-rw-r----- 1 oracle dba 794 Dec 21 13:49 TESTWSL_ora_24062_20211221134913572660143795.aud
-rw-r----- 1 oracle dba 793 Dec 21 13:49 TESTWSL_ora_24070_20211221134918714668143795.aud
I have configured the agent like this
-rw-r----- 1 oracle dba 1259 Dec 16 18:16 TESTWSL_ora_28346_20211216165343648403143795.aud
-rw-r----- 1 oracle dba 1259 Dec 16 18:16 TESTWSL_ora_28316_20211216165316120689143795.aud
-rw-r----- 1 oracle dba 794 Dec 21 12:49 TESTWSL_ora_20462_20211221124953748563143795.aud
-rw-r----- 1 oracle dba 793 Dec 21 12:50 TESTWSL_ora_20471_20211221125000657156143795.aud
-rw-r----- 1 oracle dba 794 Dec 21 13:49 TESTWSL_ora_24062_20211221134913572660143795.aud
-rw-r----- 1 oracle dba 793 Dec 21 13:49 TESTWSL_ora_24070_20211221134918714668143795.aud
I have configured the agent like this
<localfile>
<log_format>multi-line-regex</log_format>
<location>/oracle/oradata/TESTWSL/oraarch/adump/*</location>
<multiline_regex match="start" replace="no-replace">^\w+ \w+ \d+ \d+:\d+:\d+ \d+ \W\d+:\d+</multiline_regex>
</localfile>
for the server I have created the following decoder and its corresponding rule.
<decoder name="AUDORACLE">
<prematch>^ORACLE_HOME</prematch>
</decoder>
<decoder name="Oracle Authenticated">
<parent>AUDORACLE</parent>
<regex>USERID:[\d+] "(\w+)" USERHOST:[\d+] "\w+\\(\w+\d+)" TERMINAL:[\d+] "\w+\d+" ACTION:[\d+] "\d+" RETURNCODE:[\d+] "\d+" COMMENT$TEXT:[\d+] "(\w+) by: DATABASE; Client address: \(ADDRESS=\(PROTOCOL=tcp\)\(HOST=(\d+.\d+.\d+.\d+)\)</regex>
<order>oracle.user, oracle.origen, oracle.tipo, oracle.iporigen</order>
</decoder>
<decoder name="Oracle Authenticatedv2">
<parent>AUDORACLE</parent>
<regex>DATABASE USER:[\d+] '(\w+)'</regex>
<order>oracle.user</order>
</decoder>
<log_format>multi-line-regex</log_format>
<location>/oracle/oradata/TESTWSL/oraarch/adump/*</location>
<multiline_regex match="start" replace="no-replace">^\w+ \w+ \d+ \d+:\d+:\d+ \d+ \W\d+:\d+</multiline_regex>
</localfile>
for the server I have created the following decoder and its corresponding rule.
<decoder name="AUDORACLE">
<prematch>^ORACLE_HOME</prematch>
</decoder>
<decoder name="Oracle Authenticated">
<parent>AUDORACLE</parent>
<regex>USERID:[\d+] "(\w+)" USERHOST:[\d+] "\w+\\(\w+\d+)" TERMINAL:[\d+] "\w+\d+" ACTION:[\d+] "\d+" RETURNCODE:[\d+] "\d+" COMMENT$TEXT:[\d+] "(\w+) by: DATABASE; Client address: \(ADDRESS=\(PROTOCOL=tcp\)\(HOST=(\d+.\d+.\d+.\d+)\)</regex>
<order>oracle.user, oracle.origen, oracle.tipo, oracle.iporigen</order>
</decoder>
<decoder name="Oracle Authenticatedv2">
<parent>AUDORACLE</parent>
<regex>DATABASE USER:[\d+] '(\w+)'</regex>
<order>oracle.user</order>
</decoder>
output example
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/112_64
System name: Linux
Node name: esmas060d
Release: 4.14.35-2047.509.2.3.el7uek.x86_64
Version: #2 SMP Tue Dec 14 14:13:57 PST 2021
Machine: x86_64
VM name: VMWare Version: 6
Instance name: TESTWSL
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 20462, image: oracle@esmas060d
Tue Dec 21 12:49:53 2021 +01:00
LENGTH : '180'
ACTION :[7] 'CONNECT'
DATABASE USER:[8] 'XXXXXXX'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[14] 'XXXXX.XXXXXX'
CLIENT TERMINAL:[8] 'ESMAL155'
STATUS:[4] '1017'
DBID:[10] '3980474210'
but it doesn't work and I don't register anything. Could it be because the fields are on separate lines?
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/112_64
System name: Linux
Node name: esmas060d
Release: 4.14.35-2047.509.2.3.el7uek.x86_64
Version: #2 SMP Tue Dec 14 14:13:57 PST 2021
Machine: x86_64
VM name: VMWare Version: 6
Instance name: TESTWSL
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 20462, image: oracle@esmas060d
Tue Dec 21 12:49:53 2021 +01:00
LENGTH : '180'
ACTION :[7] 'CONNECT'
DATABASE USER:[8] 'XXXXXXX'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[14] 'XXXXX.XXXXXX'
CLIENT TERMINAL:[8] 'ESMAL155'
STATUS:[4] '1017'
DBID:[10] '3980474210'
but it doesn't work and I don't register anything. Could it be because the fields are on separate lines?
any suggestion, thanks in advance
Juan Carlos
Dec 21, 2021, 1:27:21 PM12/21/21
to Wazuh mailing list
Hi Mariano,
The regular expression provided as the multiline_regex (^\w+ \w+ \d+ \d+:\d+:\d+ \d+ \W\d+:\d+) is looking to match the date string: "Tue Dec 21 12:49:53 2021 +01:00", however the parent decoder is looking for the ORACLE_HOME which comes before the start of the multiline_regex expression.
Then the children decoders you have provided have a different set of fields than those shown in your output example and they are attempting to find a very specific regular expression without any line breaks.
I don't know if the output sample is just the beginning of the file or if all events indicate the header that starts with "Oracle Database 11g ...". If there is no easily identifiable header with each message and you want to start collecting each event at the timestamp then I suggest using <out_format> (https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/localfile.html#out-format) to provide an easy handle for the decoders to use, and then use sibling decoders to individually extract each of the fields present (https://documentation.wazuh.com/current/user-manual/ruleset/ruleset-xml-syntax/sibling-decoders.html).
So you log collection configuration will be:
<localfile>
<log_format>multi-line-regex</log_format>
<location>/var/log/oracletest</location>
Then the children decoders you have provided have a different set of fields than those shown in your output example and they are attempting to find a very specific regular expression without any line breaks.
I don't know if the output sample is just the beginning of the file or if all events indicate the header that starts with "Oracle Database 11g ...". If there is no easily identifiable header with each message and you want to start collecting each event at the timestamp then I suggest using <out_format> (https://documentation.wazuh.com/current/user-manual/reference/ossec-conf/localfile.html#out-format) to provide an easy handle for the decoders to use, and then use sibling decoders to individually extract each of the fields present (https://documentation.wazuh.com/current/user-manual/ruleset/ruleset-xml-syntax/sibling-decoders.html).
So you log collection configuration will be:
<localfile>
<log_format>multi-line-regex</log_format>
<location>/var/log/oracletest</location>
<multiline_regex match="start" replace="no-replace">^\w+ \w+ \d+ \d+:\d+:\d+ \d+ \W\d+:\d+</multiline_regex>
<out_format>Multiline Oracle Audit Log $(log)</out_format>
</localfile
And your decoders may then be for example:
<decoder name="OracleAudit">
<prematch>^Multiline Oracle Audit Log</prematch>
</decoder>
<decoder name="OracleAudit">
<parent>OracleAudit</parent>
<regex>LENGTH :'(\d+)'</regex>
<order>length</order>
</decoder>
<decoder name="OracleAudit">
<parent>OracleAudit</parent>
<regex>ACTION :[\d+] '(\.+)'</regex>
<order>action</order>
</decoder>
<decoder name="OracleAudit">
<parent>OracleAudit</parent>
<regex>DATABASE USER:[\d+] '(\.+)'</regex>
<order>srcuser</order>
</decoder>
<decoder name="OracleAudit">
<parent>OracleAudit</parent>
<regex>CLIENT USER:[\d+] '(\.+)'</regex>
<order>dstuser</order>
</decoder>
Note that you can continue adding sibling decoders by giving them the same parent name and extracting fields one at a time.
Finally you must create rules to match these messages, you can create the following parent rule and then create children rule to identify the various events:
</localfile
And your decoders may then be for example:
<decoder name="OracleAudit">
<prematch>^Multiline Oracle Audit Log</prematch>
</decoder>
<decoder name="OracleAudit">
<parent>OracleAudit</parent>
<regex>LENGTH :'(\d+)'</regex>
<order>length</order>
</decoder>
<decoder name="OracleAudit">
<parent>OracleAudit</parent>
<regex>ACTION :[\d+] '(\.+)'</regex>
<order>action</order>
</decoder>
<decoder name="OracleAudit">
<parent>OracleAudit</parent>
<regex>DATABASE USER:[\d+] '(\.+)'</regex>
<order>srcuser</order>
</decoder>
<decoder name="OracleAudit">
<parent>OracleAudit</parent>
<regex>CLIENT USER:[\d+] '(\.+)'</regex>
<order>dstuser</order>
</decoder>
Note that you can continue adding sibling decoders by giving them the same parent name and extracting fields one at a time.
Finally you must create rules to match these messages, you can create the following parent rule and then create children rule to identify the various events:
<group name="oracle">
<rule id="100002" level="0">
<decoded_as>OracleAudit</decoded_as>
<description>Oracle audit event</description>
</rule>
</group>
I hope this helps,
<rule id="100002" level="0">
<decoded_as>OracleAudit</decoded_as>
<description>Oracle audit event</description>
</rule>
</group>
I hope this helps,
Please let us know if you have any other questions.
Best Regards,
Juan Carlos Tello
mariano hinjos
Dec 22, 2021, 6:57:29 AM12/22/21
to Wazuh mailing list
thanks it works
I have another question, when I put a conditional(field name) in a rule I don't finish making it work, do I have something wrong? only see 100910
<group name="AUDORACLEV2,">
<rule id="100910" level="5">
<match>Oracle Audit</match>
<description>Entrada</description>
</rule>
<rule id="100911" level="5">
<if_sid>100910</if_sid>
<field name="data.oracle.action">100</field>
<match>Authenticated by</match>
<description>La prueba de LOGON ES CORRECTA</description>
</rule>
</group>
<rule id="100910" level="5">
<match>Oracle Audit</match>
<description>Entrada</description>
</rule>
<rule id="100911" level="5">
<if_sid>100910</if_sid>
<field name="data.oracle.action">100</field>
<match>Authenticated by</match>
<description>La prueba de LOGON ES CORRECTA</description>
</rule>
</group>
Juan Carlos
Dec 23, 2021, 6:09:32 AM12/23/21
to Wazuh mailing list
Hi Mariano,
The rule must reference the fields as they are decoded, in your case this is "oracle.action". In order to avoid conflicts with other fields Wazuh writes all decoded fields under the `data.` object.
So this should work in your case:
<group name="AUDORACLEV2,">
<rule id="100910" level="5">
<match>Oracle Audit</match>
<description>Entrada</description>
</rule>
<rule id="100911" level="5">
<if_sid>100910</if_sid>
<rule id="100910" level="5">
<match>Oracle Audit</match>
<description>Entrada</description>
</rule>
<rule id="100911" level="5">
<if_sid>100910</if_sid>
<field name="oracle.action">100</field>
<match>Authenticated by</match>
<description>La prueba de LOGON ES CORRECTA</description>
</rule>
</group>
Best Regards,
Juan Carlos Tello
Reply all
Reply to author
Forward
0 new messages