Oracle eBusiness Database Monitoring Procedure
Document Owner: Pete Stang Published: 12/16/2020
1. Scope
This procedure details the steps taken to review the eBusiness database logging via the
DBA_Audit_Trail table.
Business Unit: GDLS -US GDLS-C
Impacted Roles: GDLS SOC
When: Weekly logging reports will be emailed to the GDLSSOC@[Link] and
SOXemailretention@[Link]
Exclusions: None
2. Procedures
2.1 Inputs
• Baseline spreadsheet
• Comma delimited activity file including SME designation
• PMP Generic and Shared Accounts
Example: Baseline Spreadsheet
Example: Comma Delimited File
Page 1 of 6
GDLS Sensitive – Not for distribution outside of GDLS
Printed copy valid for three (3) business days. See the process repository for current version.
Oracle eBusiness Database Monitoring Procedure Published: 12/16/2020
Example: PMP Generic and Shared Accounts
2.2 Task Steps
2.2.1 Generation of Database Log
The standard database auditing is enabled in Oracle databases with initialization
parameter set to AUDIT_TRAIL = DB. This means it writes the audit data to
[Link]$ except for SYS Operations, which are always written to the operating
system audit trail location. The logons and attempted logons are being audited in
this table. The DBA_AUDIT_TRAIL displays all standard audit trails entries.
Retention period of [Link]$ table is 31 days.
A comma delimited file will be emailed weekly to the GDLS SOC for reviewing
current database activity against the baseline spreadsheet for abnormalities.
2.2.2 Monitoring of Database Log
Using the baseline spreadsheet, GDLS SOC analyst will review weekly report:
• Generic and shared ID list will be reviewed to verify user access to ensure
generic and shared ID is coming from an authorized source.
• VLOOKUP function will be performed between spreadsheets to validate
development and shared ids will never be used for production, and vice
versa.
• User activity will be compared against the comma delimited file for:
Unauthorized users
Users switching between accounts
• Any variances from baseline spreadsheet or user activity will be discussed
with management or DBA.
• If the event is determined to be acceptable, the baseline spreadsheet will
be updated.
• If event is determined to be a security incident, refer to: Security Incident
Response Procedure
Page 2 of 6
GDLS Sensitive – Not for distribution outside of GDLS
Printed copy valid for three (3) business days. See the process repository for current version.
Oracle eBusiness Database Monitoring Procedure Published: 12/16/2020
Example: DBA_AUDIT_TRAIL Columns and Descriptions:
• Note: The comma delimited file will include only the first 3 columns.
Column NULL Description
OS_USERNAME Operating system login username of the user whose actions were audited
USERNAME Name (not ID number) of the user whose actions were audited
USERHOST Client host machine name
TERMINAL Identifier of the user's terminal
TIMESTAMP Date and time of the creation of the audit trail entry (date and time of user
login for entries created by AUDIT SESSION) in the local database session
time zone
OWNER Creator of the object affected by the action
OBJ_NAME Name of the object affected by the action
ACTION NOT Numeric action type code. The corresponding name of the action type is in
NULL the ACTION_NAME column.
ACTION_NAME Name of the action type corresponding to the numeric code in the ACTION
column
NEW_OWNER Owner of the object named in the NEW_NAME column
NEW_NAME New name of the object after a RENAME or the name of the underlying
object
OBJ_PRIVILEG Object privileges granted or revoked by a GRANT or REVOKE statement
E
SYS_PRIVILEGE System privileges granted or revoked by a GRANT or REVOKE statement
ADMIN_OPTION Indicates whether the role or system privilege was granted with the ADMIN
option
GRANTEE Name of the grantee specified in a GRANT or REVOKE statement
AUDIT_OPTION Auditing option set with the AUDIT statement
SES_ACTIONS Session summary (a string of 16 characters, one for each action type in the
order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT,
LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE).
Positions 14, 15, and 16 are reserved for future use. The characters are:
• - - None
• S - Success
• F - Failure
• B - Both
Page 3 of 6
GDLS Sensitive – Not for distribution outside of GDLS
Printed copy valid for three (3) business days. See the process repository for current version.
Oracle eBusiness Database Monitoring Procedure Published: 12/16/2020
Column NULL Description
AUDIT_OPTION Auditing option set with the AUDIT statement
SES_ACTIONS Session summary (a string of 16 characters, one for each action type in the
order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT,
LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE).
Positions 14, 15, and 16 are reserved for future use. The characters are:
• - - None
• S - Success
• F - Failure
• B - Both
LOGOFF_TIME Date and time of user log off
LOGOFF_LREA Logical reads for the session
D
LOGOFF_PREA Physical reads for the session
D
LOGOFF_LWRIT Logical writes for the session
E
LOGOFF_DLOC Deadlocks detected during the session
K
COMMENT_TEX Text comment on the audit trail entry, providing more information about the
T statement audited
Also indicates how the user was authenticated. The method can be one of
the following:
• DATABASE - Authentication was done by password
• NETWORK - Authentication was done by Oracle Net Services or the
Advanced Security option
• PROXY - Client was authenticated by another user; the name of the
proxy user follows the method type
SESSIONID NOT Numeric ID for each Oracle session
NULL
ENTRYID NOT Numeric ID for each audit trail entry in the session
NULL
STATEMENTID NOT Numeric ID for each statement run
NULL
Page 4 of 6
GDLS Sensitive – Not for distribution outside of GDLS
Printed copy valid for three (3) business days. See the process repository for current version.
Oracle eBusiness Database Monitoring Procedure Published: 12/16/2020
RETURNCODE NOT Oracle error code generated by the action. Some useful values:
NULL
• 0 - Action succeeded
• 2004 - Security violation
• 1017 - invalid username/password
• 28000 - account is locked out
PRIV_USED System privilege used to execute the action
CLIENT_ID Client identifier in each Oracle session
ECONTEXT_ID Application execution context identifier
SESSION_CPU Amount of CPU time used by each Oracle session
EXTENDED_TIM Timestamp of the creation of the audit trail entry (timestamp of user login for
ESTAMP entries created by AUDIT SESSION) in UTC (Coordinated Universal Time)
time zone
PROXY_SESSIO Proxy session serial number, if an enterprise user has logged in through the
NID proxy mechanism
GLOBAL_UID Global user identifier for the user, if the user has logged in as an enterprise
user
INSTANCE_NU Instance number as specified by the INSTANCE_NUMBER initialization
MBER parameter
OS_PROCESS Operating System process identifier of the Oracle process
TRANSACTIONI Transaction identifier of the transaction in which the object is accessed or
D modified
SCN System change number (SCN) of the query
SQL_BIND Bind variable data of the query
SQL_TEXT SQL text of the query
OBJ_EDITION_ Name of the edition containing the audited object
NAME
DBID Database identifier of the audited database
2.3 Outputs
Item Description
VLOOKUP between Activity File and PMP U:\GDLS_Global Transfer\NON-Technical
File Data\Compliance\eBusiness Database Logging
Updated Baseline Spreadsheet U:\GDLS_Global Transfer\NON-Technical
Data\Compliance\eBusiness Database Logging
GDLS Security Incident Database [Link]
Page 5 of 6
GDLS Sensitive – Not for distribution outside of GDLS
Printed copy valid for three (3) business days. See the process repository for current version.
Oracle eBusiness Database Monitoring Procedure Published: 12/16/2020
2.4 Exceptions
None
3. Definitions
Term Definition
4. Appendices / References
Document Name Comments
Cybersecurity Maturity Model Certification Control Reference AU.2.042, AU.3.045
(CMMC) Ver.1.02
Security Incident Response Procedure
5. Revision History
Date Issue No. Comments Author Approved By:
12/11/2020 1.0 Final Draft L. North
J. Hand
M. Cruz
D. Blaine
G. Perez
Page 6 of 6
GDLS Sensitive – Not for distribution outside of GDLS
Printed copy valid for three (3) business days. See the process repository for current version.
