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’]

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.

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;

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.

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

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.

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.

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.