Oracle Database Server

• Supported Versions
• What is Discovered and Monitored
• Configuration
• Settings for Access Credentials
• Sample Events

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.

Setting	Value
Name	<set name>
Device Type	Generic
Access Protocol	SNMP
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:

Setting	Value
Name	phoenix_agent_accelops
Device Type	Oracle Database Server
Access Protocol	JDBC
Used For	Performance Monitoring
Pull Interval (minutes)	5
Port	1521
Instance Name	orcl2
User Name	The user you created for performance monitoring
Password	The 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'