In Oracle JD Edwards EnterpriseOne, security auditing is a critical capability for tracking user behavior, compliance, and system integrity. One of the most important tables used for this purpose is F9312 – Security History Table, which captures key security events such as logins, user creation, and deletions.
This blog explains how to use F9312 effectively with practical SQL queries for common audit requirements.
What is F9312?
The F9312 (Security History File) stores historical security event logs generated by JD Edwards EnterpriseOne Security Server.
It helps track:
- Failed login attempts
- User creation events
- User deletion events
- Other security-related activities
Each record includes:
- User ID
- Event Type (SHEVTYP)
- Event Status (SHEVSTAT)
- Date (SHUPMJ – Julian date format)
- Time and additional audit details
1. Failed Login Attempts for a Specific User
To identify failed login attempts for a user:
SELECT *
FROM SY920.F9312
WHERE SHUSER = 'xxxxx'
AND SHEVTYP = '01'
AND SHEVSTAT = '02';
Explanation:
-
SHUSER→ User ID -
SHEVTYP = '01'→ Login Event -
SHEVSTAT = '02'→ Failed Login Status
👉 This query helps detect brute-force attempts or incorrect password usage patterns.
2. User Creation Events (From a Given Date)
To track when users were added:
SELECT *
FROM SY920.F9312
WHERE SHEVTYP = '05'
AND SHUPMJ >= 125001;
Explanation:
-
SHEVTYP = '05'→ User Creation Event -
SHUPMJ >= 125001→ Records from 01-Jan-2025 onwards (Julian format)
👉 Useful for onboarding audits and compliance checks.
3. User Deletion Events (From a Given Date)
To track deleted users:
SELECT *
FROM SY920.F9312
WHERE SHEVTYP = '06'
AND SHUPMJ >= 125001;
Explanation:
-
SHEVTYP = '06'→ User Deletion Event -
SHUPMJ >= 125001→ From 01-Jan-2025 onwards
👉 Helps ensure no unauthorized user removals occurred.
4. Common SHEVTYP (Event Types)
Below are some commonly used event type codes in F9312:
Here is a List of all Event Tyoe (SHEVTYP)
Why F9312 is Important
Using F9312 effectively helps organizations:
- Strengthen security monitoring
- Detect suspicious login behavior
- Maintain compliance (SOX, audit requirements)
- Track administrative changes in real time
- Improve governance in ERP systems
Best Practices
- Regularly archive F9312 data to avoid performance issues
- Build dashboards for failed login trends
- Alert on repeated failed login attempts
- Combine with user profile tables for deeper analysis
- Restrict access to security audit tables
Final Thoughts
The F9312 Security History table in Oracle JD Edwards EnterpriseOne is a powerful but often underutilized tool. With the right queries and monitoring strategy, it can significantly improve your enterprise security posture and audit readiness.
