Audit basic login anomalies detector
Job details
Name: |
Audit basic login anomalies detector |
Platform: |
Oracle |
Category: |
Audit |
Description: |
Check that looks for anomalies in new sessions logging on the database |
Long description: |
|
Version: |
3.8 |
Default schedule: |
* * * * |
Requires engine install: |
Yes |
Compatibility tag: |
.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’] |
Parameters
Name |
Default value |
Description |
Match database keep time (days) |
60 |
The number of days we keep a record of an action before it is removed from normal behavior table. |
Gathering baseline |
YES |
YES means it will accept all new behavior as normal and gather information for building up a anomalies database. |
Job Summary
- Purpose: The purpose of this job is to monitor and detect anomalies in user login activities on an Oracle database instance, ensuring security and consistency.
- Why: This job is essential because it helps identify unauthorized or unusual access attempts to the database which can potentially lead to data breaches or misuse of data. By detecting discrepancies from normal login patterns, the system can alert administrators to take necessary actions, thus enhancing the overall security posture.
- Manual Checking: You can check for login anomalies manually by executing the following SQL queries in the Oracle database:
SELECT * FROM dbwatch_aud.dbw_ano_bas_login_record ORDER BY DBW_ROW_SEQ;
SELECT * FROM sys.aud$ WHERE sessionid IN (SELECT sessionid FROM dbwatch_aud.dbw_ano_bas_login_record) ORDER BY sessionid, entryid;
Implementation Details
- The monitoring script uses several custom tables and stored procedures/functions to record, check, and analyze login events, generating hash values to mark each unique login action and comparing them against existing records to identify abnormal activities.
- The job employs Oracle database-specific commands within procedural logic to handle the processing of audit logs.
Tables Created
Table Name |
Description |
dbw_ano_bas_login_rowsets |
Maintains a log of processed logon row sets, marking the timestamp and status of verification |
dbw_ano_bas_login_db |
Stores login actions with their MD5 hash checksums for quick verification of existing data |
dbw_ano_bas_login_db_new |
Utilized for new unique login actions, recorded as part of the anomaly detection process |
dbw_ano_bas_login_record |
Logs detailed information about each login activity, including user ID and host details |
dbw_ano_bas_login_readable |
Contains human-readable descriptions of login actions, generated from login records |
Core Functions and Procedures
- dbw_ano_bas_get_md5 – Computes an MD5 checksum of the input string.
- dbw_ano_bas_login_read – Reads logged activities from stored records and inserts readable descriptions into a specified table based on activity type.
- dbw_ano_bas_login_make_cs – Processes each login record to generate checksums and cross-references them against known actions to either update last seen activity or log it as a potential anomaly.
- dbw_ano_bas_login – The main procedure that orchestrates the auditing of new login records, identifying anomalies based on predefined rules, and managing historical data cleanup based on a retention schedule.
Dependencies and Cleanup
- The job relies on several dependencies that ensure proper logging and retrievability of login records. Each dependency has a defined role in anomaly detection or data handling, with specific cleanup actions on failure, such as rolling back inserts or updates to maintain data integrity.
Reporting
- Anomalies in logins are presented in predefined report formats within dbWatch, listing details like the session ID, user ID, terminal, and timestamp among others, helping in forensic analysis and audit reviews.
- These reports serve as a crucial tool for database administrators to trace the source and method of unusual database access attempts, reinforcing security measures.