Monitoring ORACLE log file

[Lucio Emanuel Soldo]

Hi everyone,

How are you doing?

Thank you for your helping.

I have an interesting questions. The Oracle Database is saving logs at /oracle/admin/TESTWSL/adump/ and I would like to mount these file to be able to recolect and monitoring security events, for example users logins.

Is it possible to add the following configuracion inside ossec.conf on the agent:

<localfile>

<location> /oracle/admin/TESTWSL/adump/  </location> <log_format>syslog</log_format> 

</localfile> 

Thank you very much!

[Juan Cabrera]

Hello Lucio,

In order to monitor Oracle logs with the following configuration block:

  <localfile>
    <log_format>syslog</log_format>
    <location>/oracle/admin/TESTWSL/adump/*</location>
  </localfile>

You would have to have the logs in syslog format. Remember that Wazuh can work with the following log formats:
https://documentation.wazuh.com/4.0/user-manual/reference/ossec-conf/localfile.html?highlight=localfile#log-format

On the other hand, for those logs to generate the alerts you are interested in, you should create some custom rules and decoders. For this, you can follow the following guide:
https://documentation.wazuh.com/4.0/user-manual/ruleset/custom.html?highlight=custom%20rules

​

[Lucio Emanuel Soldo]

Hi Juan, how are you doing?

Thank you very much.

I have a couple of questions regards to your answers. In order:

1) Where could I configure the following configuration:

  <localfile> 

      <log_format>syslog</log_format>

      <location>/oracle/admin/TESTWSL/adump/*</location> 

   </localfile>

In the local ossec.conf file agent, in the ossec.conf on the server, both?

2) When creating a specific docoders inside decoders_rule.xml on the server, how should I correlate that specific decoders with the location <location>/oracle/admin/TESTWSL/adump/*</location> ?

I mean, how does wazuh server know to correlate an event from the agent with the decoders?

Thank you very much.

Have a nice day.

[Juan Cabrera]

Hello Lucio,

The localfile block should go in the computer that has the path with those logs (agent or manager).

On the other hand, custom decoders are created in the /var/ossec/etc/decoders/local_decoder.xml file of the manager.

I have not found Oracle example logs. Remember that for these logs to be collected correctly they need to be in syslog format.

Regards,
Juan Cabrera

​

[nic...@humanapis.io]

Hello to all,

We also are working over this, at the moment in order to test and do a little POC this decoder and rules works and appears in kibana gui. Only one rule and all the fileds are match without parsing

cat /var/ossec/etc/decoders/local_decoder.xml

----

<decoder name="Oracle">

  <prematch>^Oracle</prematch>

</decoder>

cat /var/ossec/etc/rules/local_rules.xml

-----

<group name="oracledb,">

  <rule id="105500" level="5">

    <decoded_as>Oracle</decoded_as>

    <description>Oracle</description>

  </rule>

</group>

Some examples of oracle logs:

Feb  9 21:18:42 localhost Oracle Audit[19103]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2943658525' 

Jan 29 22:59:00 ip187 Oracle Audit[17720]: LENGTH : '494' ACTION :[346] 'select count (*), NULL, NULL from v$session_connect_info where network_service_banner like '%AES256 encryption%' or network_service_banner like '%AES192 encryption%' or network_service_banner like '%AES128 encryption%' or network_service_banner like '%RC4_256 encryption%' or network_service_banner like '%RC4_128 encryption%' or network_service_' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[7] 'UNKNOWN' STATUS:[1] '0' DBID:[10] '2943658525' 

Feb  9 21:14:32 localhost Oracle Audit[18869]: LENGTH : '149' ACTION :[8] 'SHUTDOWN' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[0] '' 

If you could help with the decoder of one of this lines Juan will be great in order to kick off the others and understand the syntax.

Thanks in advance.

[Juan Cabrera]

Hello Nicolas,

I help you with some decoders and rulers that can get you started with custom rulers and decoders.

Decoders:

<decoder name="oracle-audit-db">
  <prematch>^Oracle Audit[\d+]: </prematch>
</decoder>

<decoder name="oracle-audit-db">
  <parent>oracle-audit-db</parent>
  <regex offset="after_parent">LENGTH : '(\d+)'</regex>
  <order>length</order>
</decoder>

<decoder name="oracle-audit-db">
  <parent>oracle-audit-db</parent>
  <regex offset="after_parent">ACTION :[(\d+)] '(\.*\w)' </regex>
  <order>action.id, action.value</order>
</decoder>

<decoder name="oracle-audit-db">
  <parent>oracle-audit-db</parent>
  <regex offset="after_parent">DATABASE USER:[(\d+)] '(\.*)'</regex>
  <order>dbuser.id, dbuser.value</order>
</decoder>

<decoder name="oracle-audit-db">
  <parent>oracle-audit-db</parent>
  <regex offset="after_parent">PRIVILEGE :[(\d+)] '(\.*)'</regex>
  <order>privilege.id, privilege.value</order>
</decoder>

<decoder name="oracle-audit-db">
  <parent>oracle-audit-db</parent>
  <regex offset="after_parent">CLIENT USER:[(\d+)] '(\.*)'</regex>
  <order>clientuser.id, clientuser.value</order>
</decoder>

<decoder name="oracle-audit-db">
  <parent>oracle-audit-db</parent>
  <regex offset="after_parent">CLIENT TERMINAL:[(\d+)] '(\.*)'</regex>
  <order>clientterminal.id, clientterminal.value</order>
</decoder>

<decoder name="oracle-audit-db">
  <parent>oracle-audit-db</parent>
  <regex offset="after_parent">STATUS:[(\d+)] '(\.*)'</regex>
  <order>status.id, status.value</order>
</decoder>

<decoder name="oracle-audit-db">
  <parent>oracle-audit-db</parent>
  <regex offset="after_parent">DBID:[(\d+)] '(\.*)'</regex>
  <order>dbid.id, dbid.value</order>
</decoder>

Rules:

<group name="oracledb,">

  <rule id="105500" level="0">
    <decoded_as>oracle-audit-db</decoded_as>
    <description>Grouping of Oracle Audit rules</description>
  </rule>

  <rule id="105501" level="3">
    <if_sid>105500</if_sid>
    <field name="action.value">CONNECT</field>
    <description>Oracle Audit: client connected</description>
  </rule>

  <rule id="105502" level="9">
    <if_sid>105500</if_sid>
    <field name="action.value">DROP TABLE</field>
    <description>Oracle Audit: table dropped</description>
  </rule>

</group>

We can see the alerts generated by these logs in logtest:

# /var/ossec/bin/ossec-logtest 
2021/02/11 17:43:49 ossec-testrule: INFO: Started (pid: 5003).
ossec-testrule: Type one log per line.

Feb  9 21:18:42 localhost Oracle Audit[19103]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2943658525' 

**Phase 1: Completed pre-decoding.
       full event: 'Feb  9 21:18:42 localhost Oracle Audit[19103]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2943658525' '
       timestamp: 'Feb  9 21:18:42'
       hostname: 'localhost'
       program_name: '(null)'
       log: 'Oracle Audit[19103]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2943658525' '

**Phase 2: Completed decoding.
       decoder: 'oracle-audit-db'
       lenght: '159'
       action.id: '7'
       action.value: 'CONNECT'
       dbuser.id: '1'
       dbuser.value: '/'
       privilege.id: '6'
       privilege.value: 'SYSDBA'
       clientuser.id: '6'
       clientuser.value: 'oracle'
       clientterminal.id: '5'
       clientterminal.value: 'pts/0'
       status.id: '1'
       status.value: '0'
       dbid.id: '10'
       dbid.value: '2943658525'

**Phase 3: Completed filtering (rules).
       Rule id: '105501'
       Level: '3'
       Description: 'Oracle Audit: client connected'
**Alert to be generated.

Jan 29 22:59:00 ip187 Oracle Audit[17720]: LENGTH : '494' ACTION :[346] 'select count (*), NULL, NULL from v$session_connect_info where network_service_banner like '%AES256 encryption%' or network_service_banner like '%AES192 encryption%' or network_service_banner like '%AES128 encryption%' or network_service_banner like '%RC4_256 encryption%' or network_service_banner like '%RC4_128 encryption%' or network_service_' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[7] 'UNKNOWN' STATUS:[1] '0' DBID:[10] '2943658525' 

**Phase 1: Completed pre-decoding.
       full event: 'Jan 29 22:59:00 ip187 Oracle Audit[17720]: LENGTH : '494' ACTION :[346] 'select count (*), NULL, NULL from v$session_connect_info where network_service_banner like '%AES256 encryption%' or network_service_banner like '%AES192 encryption%' or network_service_banner like '%AES128 encryption%' or network_service_banner like '%RC4_256 encryption%' or network_service_banner like '%RC4_128 encryption%' or network_service_' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[7] 'UNKNOWN' STATUS:[1] '0' DBID:[10] '2943658525' '
       timestamp: 'Jan 29 22:59:00'
       hostname: 'ip187'
       program_name: '(null)'
       log: 'Oracle Audit[17720]: LENGTH : '494' ACTION :[346] 'select count (*), NULL, NULL from v$session_connect_info where network_service_banner like '%AES256 encryption%' or network_service_banner like '%AES192 encryption%' or network_service_banner like '%AES128 encryption%' or network_service_banner like '%RC4_256 encryption%' or network_service_banner like '%RC4_128 encryption%' or network_service_' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[7] 'UNKNOWN' STATUS:[1] '0' DBID:[10] '2943658525' '

**Phase 2: Completed decoding.
       decoder: 'oracle-audit-db'
       lenght: '494'
       action.id: '346'
       action.value: 'select count (*), NULL, NULL from v$session_connect_info where network_service_banner like '%AES256 encryption%' or network_service_banner like '%AES192 encryption%' or network_service_banner like '%AES128 encryption%' or network_service_banner like '%RC4_256 encryption%' or network_service_banner like '%RC4_128 encryption%' or network_service_'
       dbuser.id: '1'
       dbuser.value: '/'
       privilege.id: '4'
       privilege.value: 'NONE'
       clientuser.id: '0'
       clientuser.value: ''
       clientterminal.id: '7'
       clientterminal.value: 'UNKNOWN'
       status.id: '1'
       status.value: '0'
       dbid.id: '10'
       dbid.value: '2943658525'

**Phase 3: Completed filtering (rules).
       Rule id: '105501'
       Level: '3'
       Description: 'Oracle Audit: client connected'
**Alert to be generated.

Feb  9 21:14:32 localhost Oracle Audit[18869]: LENGTH : '149' ACTION :[8] 'SHUTDOWN' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[0] '' 

**Phase 1: Completed pre-decoding.
       full event: 'Feb  9 21:14:32 localhost Oracle Audit[18869]: LENGTH : '149' ACTION :[8] 'SHUTDOWN' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[0] '' '
       timestamp: 'Feb  9 21:14:32'
       hostname: 'localhost'
       program_name: '(null)'
       log: 'Oracle Audit[18869]: LENGTH : '149' ACTION :[8] 'SHUTDOWN' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[0] '' '

**Phase 2: Completed decoding.
       decoder: 'oracle-audit-db'
       lenght: '149'
       action.id: '8'
       action.value: 'SHUTDOWN'
       dbuser.id: '1'
       dbuser.value: '/'
       privilege.id: '6'
       privilege.value: 'SYSDBA'
       clientuser.id: '6'
       clientuser.value: 'oracle'
       clientterminal.id: '5'
       clientterminal.value: 'pts/0'
       status.id: '1'
       status.value: '0'
       dbid.id: '0'
       dbid.value: ''

**Phase 3: Completed filtering (rules).
       Rule id: '105500'
       Level: '0'
       Description: 'Grouping of Oracle Audit rules'

Remember that you can review the following guide to make others that you need:
https://documentation.wazuh.com/4.0/user-manual/ruleset/custom.html

On the other hand, if you need help, use your own topic in google group instead of posting in someone else’s topic.

Regards,
Juan Cabrera

​

[nic...@humanapis.io]

Hello Juan,

Many thanks for the decoders and rules, this helps us a lot.

Sorry for use the Lucio topic the idea was help him also with the same subject.

Again thanks and have a nice day.

[Lucio Emanuel Soldo]

Hi Nicolás!

No problema about the use!

Juan, thank you very much!!!

I`m waiting for my IT department solving a problem on Oracle audit, then I´m gonna try to use these decoders and rules!!!...so...THANK YOU AGAIN  JUAN!!

[Lucio Emanuel Soldo]

Hi everyone,

I have a question, maybe so simple, maybe so terrible!..:-)

Well ,first of all, I could configure the ORACLE DB in my SERVER_A to send audit security logs to its local file /var/log/message through syslog protocolo. The agent in the SERVER_A has the correct configuration in its ossec.conf:

 
  <localfile> 

       <log_format>syslog</log_format>

       <location>/var/log/messages</location> 

 </localfile>

I understand I must not configure any decoders because the log is native format from syslog protocol located at /var/log/messages, or not?

For example, if the users sysdba login to DB this login should be saved on /var/log/messages, for that reason I don´t have to generate any decoders...

Thank you very much.

[Juan Cabrera]

Hello Lucio,

It depends on the log that is being generated. Just because the format used is Syslog, does not mean that there exists a decoder/rule that triggers an event.

To test whether or not there is a decoder/rule that triggers an alert or if you need to create a custom one, you can paste a sample log by logtest to check if an alert is generated.

For this, use the binary located in /var/ossec/bin/wazuh-logtest.

You have more information at the following link: wazuh-logtest for version 4.1 or at ossec-logtest for previous versions.

Regards,
Juan Cabrera

​

[Lucio Emanuel Soldo]

Hi Juan, how are you doing?

So clear ! Right now I could read de logs from its source /var/log/messages using a tail -f command, so I have a sample from this file , for example when login to Oracle DB with de sysdba users.

I´m gonna use the new wazuh-logtest.

Thank you very much!

[Danish Ibrar]

How did you enable these logs as I don't see any log file in this directory for my Oracle DB...
I working on the same integration...