Fortinet black logo

External Systems Configuration Guide

Oracle Database Server

Oracle Database Server

Supported Versions

  • Oracle Database 10g
  • Oracle Database 11g
  • Oracle Database 12c

What is Discovered and Monitored

Protocol Information discovered Metrics collected Used for
SNMP Application type Process level CPU and memory utilization Performance Monitoring
WMI Application type, service mappings Process level metrics: uptime, CPU utilization, Memory utilization, Read I/O KBytes/sec, Write I/O KBytes/sec Performance Monitoring
JDBC Generic database information: version, Character Setting, Archive Enabled, Listener Status, Instance Status, Last backup date,
JDBC

Database performance metrics: Buffer cache hit ratio, Row cache hit ratio, Library cache hit ratio, Shared pool free ratio, Wait time ratio, Memory Sorts ratio, Host CPU Util ratio, CPU Time ratio, Disk Read/Write rates (operations and MBps), Network I/O Rate, Enqueue Deadlock rate, Database Request rate, User Transaction rate, User count, Logged on user count, Session Count, System table space usage, User table space usage, Temp table space usage, Last backup date, Days since last backup

Table space performance metrics: Table space name, table space type, table space usage, table space free space, table space next extent

Performance Monitoring
Syslog Listener log, Alert log, Audit Log
JDBC None Database audit trail: Successful and failed database logon, Various database operation audit trail including CREATE/ALTER/DROP/TRUNCATE operations on tables, table spaces, databases, clusters, users, roles, views, table indices, triggers etc. Security Monitoring

Event Types

In ADMIN > Device Support > Event, search for "oracle database" in the Description column to see the event types associated with this device.

Rules

In RESOURCE > Rules, search for "oracle database" in the Description column to see the rules associated with this application or device.

Reports

In RESOURCE > Reports , search for "oracle database" in the Name column to see the reports associated with this application or device.

Configuration

SNMP

FortiSIEM uses SNMP to discover and monitor this device. Make sure SNMP is enabled for the device as directed in its product documentation. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide.

JDBC for Database Performance Monitoring - Oracle Database Server

To configure your Oracle Database Server for performance monitoring by FortiSIEM, you must create a read-only user who has select permissions for the database. This is the user you will use to create the access credentials for FortiSIEM to communicate with your database server.

  1. Open the SQLPlus application.
  2. Log in with a system-level account.
  3. Connect to your instance as sysdba.
    SQL> conn / as sysdba;
    Connected.
  4. Create a non-admin user account. (Note: If you already created the phoenix_agent user, you can skip this step.)
    SQL> create user phoenix_agent identified by
    "accelops";
    User created.
    
  5. Assign permissions to the user.

    grant select on dba_objects to phoenix_agent;

    grant select on dba_tablespace_usage_metrics to phoenix_agent;

    grant select on dba_tablespaces to phoenix_agent;

    grant select on nls_database_parameters to phoenix_agent;

    grant select on v_$backup_set to phoenix_agent;

    grant select on v_$instance to phoenix_agent;

    grant select on v_$parameter to phoenix_agent;

    grant select on v_$session to phoenix_agent;

    grant select on v_$sql to phoenix_agent;

    grant select on v_$sysmetric to phoenix_agent;

    grant select on v_$version to phoenix_agent;

    grant select on gv_$session to phoenix_agent;

    grant select on gv_$service_stats to phoenix_agent;

  6. Verily that the permissions were successfully assigned to the user.

    select count(*) from dba_objects;

    select count(*) from dba_tablespace_usage_metrics;

    select count(*) from dba_tablespaces;

    select count(*) from gv$service_stats;

    select count(*) from nls_database_parameters;

    select count(*) from v$backup_set order by start_time desc;

    select count(*) from v$instance;

    select count(*) from v$parameter;

    select count(*) from v$session;

    select count(*) from v$sql;

    select count(*) from v$sysmetric;

    select count(*) from v$version;

JDBC for Database Auditing - Oracle Database Server

Required Environmental Variables

Make sure that these environment variables are set

  • ORACLE_HOME= C:\app\Administrator\product\11.2.0\dbhome_1
  • ORACLE_BASE= C:\app\Administrator
  1. Create audit trail views by executing cataaudit.sql as the sysdb user.
    Linux:
    su- oracle
     sqlplus /nolog
     conn / as sysdba;
     @$ORACLE_HOME/rdbms/admin/cataudit.sql;
    quit
    
    Windows:
    sqlplus /nolog
    conn / as sysdba;
    @%ORACLE_HOME%/rdbms/admin/cataudit.sql;
    quit
  2. Enable auditing by modifying the Oracle instance initialization file init<SID>.ora.
    This is typically located in $ORACLE_BASE/admin/<SID>/pfile where DIS is the Oracle instance
    AUDIT_TRAIL = DB 
    or 
    AUDIT_TRAIL = true
  3. Restart the database.
    su - oracle
     sqlplus /nolog
     conn / as sysdba;
     shutdown immediate;
     startup;
     quit
  4. Create a user account and grant select privileges to that user.
    su - oracle
     sqlplus /nolog
     conn / as sysdba
     Create user phoenix_agent identified by "phoenix_agent_pwd"   (NOTE: please correct this set -- above steps showed that we created phoenix_agent already, just add the grant steps and utilize the "accelops" password;
     Grant connect to phoenix_agent;
     Grant select on dba_audit_trail to phoenix_agent;
     Grant select on v_$session to phoenix_agent;
  5. Turn on auditing.
    su - oracle
    sqlplus /nolog
    conn / as sysdba;
    audit session;
    quit;
  6. Fetch the audit data to make sure the configuration was successful.
    su - oracle;
     sqlplus phoenix_agent/phoenix_agent_pwd;
     select count (*) from dba_audit_trail;

    You should see the count changing after logging on a few times.

Configuring listener log and error log via SNARE - Oracle side

  1. Install and configure Epilog application to send syslog to FortiSIEM
    1. Download Epilog from Epilog download site and install it on your Windows Server.
    2. Launch Epilog from Start→All Programs→InterSect Alliance→Epilog for windows
    3. Configure Epilog application as follows
      1. Select Log Configuration on left hand panel, click Add button to add Oracle Listener log file to be sent to FortiSIEM. Also make sure the Log Type is OracleListenerLog.
      2. Click Add button to add Oracle Alert log file to be sent to FortiSIEM. Also make sure the Log Type is OracleAlertLog.
      3. After adding both the files, SNARE Log Configuration will show both the files included as follows
      4. Select Network Configuration on left hand panel. On the right, set the destination address to that of FortiSIEM server, port to 514 and make sure that syslog header is enabled. Then click Change Configuration button.
      5. Click the "Apply the latest audit configuration" link on the left hand side to apply the changes to Epilog applications. DHCP logs will now sent to FortiSIEM in real time.

You can now configure FortiSIEM to communicate with your device. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide.

Settings for Access Credentials

SNMP Access Credentials for All Devices

Use these Access Method Definition settings to allow FortiSIEM to communicate with your device over SNMP. Set the Name and Community String.

SettingValue
Name<set name>
Device TypeGeneric
Access ProtocolSNMP
Community String<your own>

Settings for Oracle Database Server JDBC Access Credentials for Performance Monitoring

Use these Access Method Definition settings to allow FortiSIEM to communicate with your Oracle database server over JDBC:

SettingValue
Namephoenix_agent_accelops
Device TypeOracle Database Server
Access ProtocolJDBC
Used ForPerformance Monitoring
Pull Interval (minutes)5
Port1521
Instance Nameorcl2
User NameThe user you created for performance monitoring
PasswordThe password associated with the user

Sample Events

System Level Database Performance Metrics
[PH_DEV_MON_PERF_ORADB]:[eventSeverity]=PHL_INFO, [hostIpAddr]=10.1.2.8, [hostName]=Host-10.1.2.8, [appGroupName]=Oracle Database Server,
[appVersion]=Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production, [instanceName]=orcl, [instanceStatus]=OPEN, [charSetting]=ZHS16GBK, [archiveEnabled]=FALSE,
 [lastBackupDate]=1325566287, [listenerStatus]=OPEN,[dbBufferCacheHitRatio]=100,[dbMemorySortsRatio]=100,[dbUserTransactionPerSec]=0.13,[dbPhysicalReadsPerSec]=0,
[dbPhysicalWritesPerSec]=0.48,[dbHostCpuUtilRatio]=0,[dbNetworkKBytesPerSec]=0.58,[dbEnqueueDeadlocksPerSec]=0,[dbCurrentLogonsCount]=32,[dbWaitTimeRatio]=7.13,[dbCpuTimeRatio]=92.87,
[dbRowCacheHitRatio]=100,[dbLibraryCacheHitRatio]=99.91,[dbSharedPoolFreeRatio]=18.55,[dbSessionCount]=40,[dbIOKBytesPerSec]=33.26,[dbRequestsPerSec]=3.24,
[dbSystemTablespaceUsage]= 2.88,[dbTempTablespaceUsage]= 0,[dbUsersTablespaceUsage]= 0.01,[dbUserCount]= 2,[dbInvalidObjectCount]= 4
Table Space Performance Metrics
[PH_DEV_MON_PERF_ORADB_TABLESPACE]:[eventSeverity]=PHL_INFO, [appGroupName]=Oracle Database Server, [instanceName]=orcl, [tablespaceName]=UNDOTBS1, [tablespaceType]=UNDO,
[tablespaceUsage]=0.01, [tablespaceFreeSpace]=4193886, [tablespaceNextExtent]=0

[PH_DEV_MON_PERF_ORADB_TABLESPACE]:[eventSeverity]=PHL_INFO, [appGroupName]=Oracle Database Server, [instanceName]=orcl, [tablespaceName]=USERS, [tablespaceType]=PERMANENT,
[tablespaceUsage]=0.01, [tablespaceFreeSpace]=4193774, [tablespaceNextExtent]=0
Oracle Audit Trail (FortiSIEM Generated Events)
<134>Apr 10 12:51:42 abc-desktop java: [ORADB_PH_Logoff]:[eventSeverity]=PHL_INFO, [retCode]=0, [eventTime]=2009-04-10 14:29:22:111420, [rptIp]=172.16.10.40, [srcIp]=QA-V-CtOS-ora.abc.net, [user]=DBSNMP, [logonTime]=2009-04-10 14:29:22:111420, [logoffTime]=2009-04-10 14:29:22, [privUsed]=CREATE_SESSION,
Oracle Audit Log
<172>Oracle Audit[25487]: LENGTH : '153' ACTION :[004] 'bjn' DATABASE USER:[9] 'user' PRIVILEGE :[4] 'NONE' CLIENT USER:[9] 'user' CLIENT TERMINAL:[14] 'terminal' STATUS:[1] '0']

<172>Oracle Audit[6561]: LENGTH : '158' ACTION :[6] 'COMMIT' DATABASE USER:[8] 'user' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'user' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[9] '200958341'

<172>Oracle Audit[28061]: LENGTH: 265 SESSIONID:[9] 118110747 ENTRYID:[5] 14188 STATEMENT:[5] 28375 USERID:[8] user ACTION:[3] 100 RETURNCODE:[1] 0 COMMENT$TEXT:[99] Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.90.217.247)(PORT=4566)) PRIV$USED:[1] 5
Oracle Listener Log
<46>Dec 13 06:07:08 WIN03R2E-110929    OracleListenerLog    0    12-OCT-2011 16:17:52 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599744)) * status * 0
Oracle Alert Log
<46>Dec 13 06:07:08 WIN03R2E-110929 OracleAlertLog    0    ORA-00312: online log 3 thread 1: 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG'

Oracle Database Server

Supported Versions

  • Oracle Database 10g
  • Oracle Database 11g
  • Oracle Database 12c

What is Discovered and Monitored

Protocol Information discovered Metrics collected Used for
SNMP Application type Process level CPU and memory utilization Performance Monitoring
WMI Application type, service mappings Process level metrics: uptime, CPU utilization, Memory utilization, Read I/O KBytes/sec, Write I/O KBytes/sec Performance Monitoring
JDBC Generic database information: version, Character Setting, Archive Enabled, Listener Status, Instance Status, Last backup date,
JDBC

Database performance metrics: Buffer cache hit ratio, Row cache hit ratio, Library cache hit ratio, Shared pool free ratio, Wait time ratio, Memory Sorts ratio, Host CPU Util ratio, CPU Time ratio, Disk Read/Write rates (operations and MBps), Network I/O Rate, Enqueue Deadlock rate, Database Request rate, User Transaction rate, User count, Logged on user count, Session Count, System table space usage, User table space usage, Temp table space usage, Last backup date, Days since last backup

Table space performance metrics: Table space name, table space type, table space usage, table space free space, table space next extent

Performance Monitoring
Syslog Listener log, Alert log, Audit Log
JDBC None Database audit trail: Successful and failed database logon, Various database operation audit trail including CREATE/ALTER/DROP/TRUNCATE operations on tables, table spaces, databases, clusters, users, roles, views, table indices, triggers etc. Security Monitoring

Event Types

In ADMIN > Device Support > Event, search for "oracle database" in the Description column to see the event types associated with this device.

Rules

In RESOURCE > Rules, search for "oracle database" in the Description column to see the rules associated with this application or device.

Reports

In RESOURCE > Reports , search for "oracle database" in the Name column to see the reports associated with this application or device.

Configuration

SNMP

FortiSIEM uses SNMP to discover and monitor this device. Make sure SNMP is enabled for the device as directed in its product documentation. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide.

JDBC for Database Performance Monitoring - Oracle Database Server

To configure your Oracle Database Server for performance monitoring by FortiSIEM, you must create a read-only user who has select permissions for the database. This is the user you will use to create the access credentials for FortiSIEM to communicate with your database server.

  1. Open the SQLPlus application.
  2. Log in with a system-level account.
  3. Connect to your instance as sysdba.
    SQL> conn / as sysdba;
    Connected.
  4. Create a non-admin user account. (Note: If you already created the phoenix_agent user, you can skip this step.)
    SQL> create user phoenix_agent identified by
    "accelops";
    User created.
    
  5. Assign permissions to the user.

    grant select on dba_objects to phoenix_agent;

    grant select on dba_tablespace_usage_metrics to phoenix_agent;

    grant select on dba_tablespaces to phoenix_agent;

    grant select on nls_database_parameters to phoenix_agent;

    grant select on v_$backup_set to phoenix_agent;

    grant select on v_$instance to phoenix_agent;

    grant select on v_$parameter to phoenix_agent;

    grant select on v_$session to phoenix_agent;

    grant select on v_$sql to phoenix_agent;

    grant select on v_$sysmetric to phoenix_agent;

    grant select on v_$version to phoenix_agent;

    grant select on gv_$session to phoenix_agent;

    grant select on gv_$service_stats to phoenix_agent;

  6. Verily that the permissions were successfully assigned to the user.

    select count(*) from dba_objects;

    select count(*) from dba_tablespace_usage_metrics;

    select count(*) from dba_tablespaces;

    select count(*) from gv$service_stats;

    select count(*) from nls_database_parameters;

    select count(*) from v$backup_set order by start_time desc;

    select count(*) from v$instance;

    select count(*) from v$parameter;

    select count(*) from v$session;

    select count(*) from v$sql;

    select count(*) from v$sysmetric;

    select count(*) from v$version;

JDBC for Database Auditing - Oracle Database Server

Required Environmental Variables

Make sure that these environment variables are set

  • ORACLE_HOME= C:\app\Administrator\product\11.2.0\dbhome_1
  • ORACLE_BASE= C:\app\Administrator
  1. Create audit trail views by executing cataaudit.sql as the sysdb user.
    Linux:
    su- oracle
     sqlplus /nolog
     conn / as sysdba;
     @$ORACLE_HOME/rdbms/admin/cataudit.sql;
    quit
    
    Windows:
    sqlplus /nolog
    conn / as sysdba;
    @%ORACLE_HOME%/rdbms/admin/cataudit.sql;
    quit
  2. Enable auditing by modifying the Oracle instance initialization file init<SID>.ora.
    This is typically located in $ORACLE_BASE/admin/<SID>/pfile where DIS is the Oracle instance
    AUDIT_TRAIL = DB 
    or 
    AUDIT_TRAIL = true
  3. Restart the database.
    su - oracle
     sqlplus /nolog
     conn / as sysdba;
     shutdown immediate;
     startup;
     quit
  4. Create a user account and grant select privileges to that user.
    su - oracle
     sqlplus /nolog
     conn / as sysdba
     Create user phoenix_agent identified by "phoenix_agent_pwd"   (NOTE: please correct this set -- above steps showed that we created phoenix_agent already, just add the grant steps and utilize the "accelops" password;
     Grant connect to phoenix_agent;
     Grant select on dba_audit_trail to phoenix_agent;
     Grant select on v_$session to phoenix_agent;
  5. Turn on auditing.
    su - oracle
    sqlplus /nolog
    conn / as sysdba;
    audit session;
    quit;
  6. Fetch the audit data to make sure the configuration was successful.
    su - oracle;
     sqlplus phoenix_agent/phoenix_agent_pwd;
     select count (*) from dba_audit_trail;

    You should see the count changing after logging on a few times.

Configuring listener log and error log via SNARE - Oracle side

  1. Install and configure Epilog application to send syslog to FortiSIEM
    1. Download Epilog from Epilog download site and install it on your Windows Server.
    2. Launch Epilog from Start→All Programs→InterSect Alliance→Epilog for windows
    3. Configure Epilog application as follows
      1. Select Log Configuration on left hand panel, click Add button to add Oracle Listener log file to be sent to FortiSIEM. Also make sure the Log Type is OracleListenerLog.
      2. Click Add button to add Oracle Alert log file to be sent to FortiSIEM. Also make sure the Log Type is OracleAlertLog.
      3. After adding both the files, SNARE Log Configuration will show both the files included as follows
      4. Select Network Configuration on left hand panel. On the right, set the destination address to that of FortiSIEM server, port to 514 and make sure that syslog header is enabled. Then click Change Configuration button.
      5. Click the "Apply the latest audit configuration" link on the left hand side to apply the changes to Epilog applications. DHCP logs will now sent to FortiSIEM in real time.

You can now configure FortiSIEM to communicate with your device. For more information, refer to sections "Discovery Settings" and "Setting Credentials" in the User Guide.

Settings for Access Credentials

SNMP Access Credentials for All Devices

Use these Access Method Definition settings to allow FortiSIEM to communicate with your device over SNMP. Set the Name and Community String.

SettingValue
Name<set name>
Device TypeGeneric
Access ProtocolSNMP
Community String<your own>

Settings for Oracle Database Server JDBC Access Credentials for Performance Monitoring

Use these Access Method Definition settings to allow FortiSIEM to communicate with your Oracle database server over JDBC:

SettingValue
Namephoenix_agent_accelops
Device TypeOracle Database Server
Access ProtocolJDBC
Used ForPerformance Monitoring
Pull Interval (minutes)5
Port1521
Instance Nameorcl2
User NameThe user you created for performance monitoring
PasswordThe password associated with the user

Sample Events

System Level Database Performance Metrics
[PH_DEV_MON_PERF_ORADB]:[eventSeverity]=PHL_INFO, [hostIpAddr]=10.1.2.8, [hostName]=Host-10.1.2.8, [appGroupName]=Oracle Database Server,
[appVersion]=Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production, [instanceName]=orcl, [instanceStatus]=OPEN, [charSetting]=ZHS16GBK, [archiveEnabled]=FALSE,
 [lastBackupDate]=1325566287, [listenerStatus]=OPEN,[dbBufferCacheHitRatio]=100,[dbMemorySortsRatio]=100,[dbUserTransactionPerSec]=0.13,[dbPhysicalReadsPerSec]=0,
[dbPhysicalWritesPerSec]=0.48,[dbHostCpuUtilRatio]=0,[dbNetworkKBytesPerSec]=0.58,[dbEnqueueDeadlocksPerSec]=0,[dbCurrentLogonsCount]=32,[dbWaitTimeRatio]=7.13,[dbCpuTimeRatio]=92.87,
[dbRowCacheHitRatio]=100,[dbLibraryCacheHitRatio]=99.91,[dbSharedPoolFreeRatio]=18.55,[dbSessionCount]=40,[dbIOKBytesPerSec]=33.26,[dbRequestsPerSec]=3.24,
[dbSystemTablespaceUsage]= 2.88,[dbTempTablespaceUsage]= 0,[dbUsersTablespaceUsage]= 0.01,[dbUserCount]= 2,[dbInvalidObjectCount]= 4
Table Space Performance Metrics
[PH_DEV_MON_PERF_ORADB_TABLESPACE]:[eventSeverity]=PHL_INFO, [appGroupName]=Oracle Database Server, [instanceName]=orcl, [tablespaceName]=UNDOTBS1, [tablespaceType]=UNDO,
[tablespaceUsage]=0.01, [tablespaceFreeSpace]=4193886, [tablespaceNextExtent]=0

[PH_DEV_MON_PERF_ORADB_TABLESPACE]:[eventSeverity]=PHL_INFO, [appGroupName]=Oracle Database Server, [instanceName]=orcl, [tablespaceName]=USERS, [tablespaceType]=PERMANENT,
[tablespaceUsage]=0.01, [tablespaceFreeSpace]=4193774, [tablespaceNextExtent]=0
Oracle Audit Trail (FortiSIEM Generated Events)
<134>Apr 10 12:51:42 abc-desktop java: [ORADB_PH_Logoff]:[eventSeverity]=PHL_INFO, [retCode]=0, [eventTime]=2009-04-10 14:29:22:111420, [rptIp]=172.16.10.40, [srcIp]=QA-V-CtOS-ora.abc.net, [user]=DBSNMP, [logonTime]=2009-04-10 14:29:22:111420, [logoffTime]=2009-04-10 14:29:22, [privUsed]=CREATE_SESSION,
Oracle Audit Log
<172>Oracle Audit[25487]: LENGTH : '153' ACTION :[004] 'bjn' DATABASE USER:[9] 'user' PRIVILEGE :[4] 'NONE' CLIENT USER:[9] 'user' CLIENT TERMINAL:[14] 'terminal' STATUS:[1] '0']

<172>Oracle Audit[6561]: LENGTH : '158' ACTION :[6] 'COMMIT' DATABASE USER:[8] 'user' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'user' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[9] '200958341'

<172>Oracle Audit[28061]: LENGTH: 265 SESSIONID:[9] 118110747 ENTRYID:[5] 14188 STATEMENT:[5] 28375 USERID:[8] user ACTION:[3] 100 RETURNCODE:[1] 0 COMMENT$TEXT:[99] Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.90.217.247)(PORT=4566)) PRIV$USED:[1] 5
Oracle Listener Log
<46>Dec 13 06:07:08 WIN03R2E-110929    OracleListenerLog    0    12-OCT-2011 16:17:52 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599744)) * status * 0
Oracle Alert Log
<46>Dec 13 06:07:08 WIN03R2E-110929 OracleAlertLog    0    ORA-00312: online log 3 thread 1: 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG'