Splunk DB Connect Tail for Oracle E-Business Sign-on Audit

Integrigy has received a lot of great feedback about our Framework for logging and auditing the Oracle E-Business Suite.  The Framework is posted here.  The Framework is a direct result of our consulting experience and clients have found it equally useful to both those wanting to improve their auditing capabilities as well as those just starting to implement logging and auditing.  Our goal with the Framework is to provide a clear explanation of the native auditing and logging features available, present an approach and strategy for using these features and a straight-forward configuration steps to implement the approach.

The Framework is also specifically designed to help clients meet compliance and security standards such as Sarbanes-Oxley (SOX), Payment Card Industry (PCI), FISMA, and HIPAA. The foundation of the Framework is PCI DSS requirement 10.2.

Splunk DB Connect

The Framework defines three levels of maturity. Level one identifies basic logging, level two calls for passing log data to a centralized log management solution and level three is a continuous improvement loop where increasingly more data is correlated. Level two is the key step. Given the complexity of the Oracle E-Business Suite and compliance requirements for protection and non-repudiation of log data, a centralized logging solution is required.

Splunk, ArcSight, Envision and the Oracle Audit Vault all offer solutions for centralized logging. Recently a client was asking for assistance to implement our Framework using Splunk. Splunk has a native parser for Oracle Syslog as well as a free application to import data directly from tables. Splunk’s DB Connect provides real-time integration is an ideal solution to pull data from the E-Business Suite’s Sign-On Audit tables.

Sign-On Audit

Sign-On Audit is optional functionality to track end-user navigation activity in the professional forms (not Web or HTML forms).  It has three levels: Login, What Responsibility was used, and What Forms were visited.  For each option, the length of time is captured.  Only Navigation activity is a captured – it is important to understand that what the end-user did in the form, be it viewed a record or updated a record, is not captured.  If the requirement is to capture the end-user actions in the form, auditing must be enabled using Oracle E-Business Suite AuditTrail or third-party tools are required.

Sign-On Audit is turned off/on by the system profile option “Sign-On: Audit Level.”  If enabled, Sign-On Audit needs to regularly purge the data it collects.  This can be done using the Purge Concurrent Request and/or Manager Data concurrent program.

Sign-On Audit data is collected in real-time and can be viewed through standard reports, a Form, or by using SQL. The following are the tables for Sign-On audit data that can be used by Splunk’s DB Connect:

• APPLSYS.FND_SIGNON
• APPLSYS.FND_LOGIN_RESPONSIBILITIES
• APPLSYS.FND_LOGIN_RESP_FORMS
• APPLSYS.FND_UNSUCCESSFUL_LOGINS

How to Tail Sign-On Audit activity using Splunk DB Connect

Below is a description of how to get starting using Splunk and DB Connect to implement Integrigy’s Framework for logging and auditing for the Oracle E-Business Suite. The sample is for how to tail the table APPLSYS.FND_LOGINS such that every hour Splunk will log into the E-Business Suite’s database and check if there are any new rows in the table. The high-level summary is a follows:

1. Do this first in a development or test instance, do not attempt first in production.
2. Obtain the documentation for the Splunk DB Connector and Integrigy’s Framework whitepaper.
3. For this example, enable Sign-On audit if you have done so already.
4. Install the Splunk DB Connector. To finish the installation you will need to install Java 1.6 (or greater) and/or reference the location of the Java Home. You will also need the Oracle JDBC driver. The installation of the Oracle JDBC driver for Splunk is well documented in the DB Connector instructions. The JAR file must be placed within the Splunk file system.
5. Within Splunk create a database connection to the E-Business Suite. Integrigy’s recommendation is to create an appropriately privileged account (do not use APPS).
6. Create an input to the Splunk database. These are referred to as ‘Database Inputs’. This is a key step. As a quick note be sure to reference all Oracle objects in UPPER CASE:
   
   1. Choose “Tail”.
   2. Select the database connection you defined earlier.
   3. For the table APPLSYS.FND_LOGINS, the following Specific SQL can be used to ignore scheduled concurrent program activity. Copy the following SQL exactly, including the last line with Splunk’s syntax for the rising column:

SELECT  U.USER_NAME,U.PERSON_PARTY_ID, LI.*

from APPLSYS.FND_LOGINS LI, APPLSYS.FND_USER U

WHERE LI.TERMINAL_ID IS NULL

AND LI.USER_ID = U.USER_ID

{{AND $rising_column$ > ?}}

4. Identify the rising column, enter: LOGIN_ID
5. Identify the index, as a quick demo to get going just use the default, enter: default
6. Identify a Host Field value, you can enter the database SID, for example, VIS121
7. Select the output format, use: multi-line key-value format
8. Identify the timestamp column, enter: START_TIME
9. Set the polling frequency or interval to hourly (default if left blank will be auto): 1h

7. Test by logging into the Oracle E-Business Suite and then looking at Splunk.
8. To fully implement the Integrigy Framework for logging and auditing the Oracle E-Business Suite, database auditing well as E-Business Suite auditing and Page Access Tracking  will need to be enabled, but you can repeat step five above for each table identified for logging E-Business Suite end-user navigation. The SQL used will differ from the above but should be straight forward. Keep in mind too that you will need enable both Sign-On Audit and Page Access Tracking in order to log end-user navigation within the Oracle E-Business Suite.

Figure 1 – Example of Searching Splunk for Oracle E-Business Suite Sign-On for the User SYSADMIN

If you have questions, please contact us at info@integrigy.com.

References

• Integrigy Guide to Auditing and Logging in Oracle E-Business Suite
• Splunk DB Connector download: http://apps.splunk.com/app/958/
• Documentation for DB Connector (look for PDF link in lower left): http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Abouttheconnector
• Really good YouTube tutorial on the DB Connector: http://www.youtube.com/watch?v=aBU54PbSDHI