9-Detect_Suspicious_Login.r
# -----------------------------------------------------------------------------------# File Name : 9-Detect_Suspicious_Login.r# Author : Pierre Montbleau# Description : Analyzes Oracle database login activity to detect potential security# breaches, including suspicious IP patterns, unusual client programs,# and non-business hour access.# Purpose : To identify and report on potential unauthorized access or compromised# accounts by analyzing login patterns and anomalies.# Call Syntax : source("F:\\DBA\\Scripts\\R\\9-Detect_Suspicious_Login.r")# Parameters : None (connects to predefined Oracle database via ODBC)# Last Modified: 2025-12-17# -----------------------------------------------------------------------------------
# -----------------------------------------------------------------------------------# Load required libraries# -----------------------------------------------------------------------------------library(DBI)library(odbc)library(dplyr)library(lubridate)library(ggplot2)
# -----------------------------------------------------------------------------------# Establish connection to Oracle database# -----------------------------------------------------------------------------------conn <- dbConnect(odbc::odbc(), dsn = "[DB_NAME]", uid = "[USERNAME]", pwd = "[PASSWORD]")
# -----------------------------------------------------------------------------------# Function: detect_suspicious_logins# Description: Retrieves login activity data and analyzes it for suspicious patterns# including IP sharing, client anomalies, and off-hours access.# Parameters:# - conn: Database connection object# Returns: List containing categorized suspicious activities and all login data# -----------------------------------------------------------------------------------detect_suspicious_logins <- function(conn) { # Query the user activity log for login events query <- " SELECT USERNAME, LOGIN_TIME, CLIENT_IP, CLIENT_HOSTNAME, OS_USER, SESSION_ID, ACTION, CLIENT_PROGRAM_NAME, ORACLE_SESSION_ID FROM ATTSTATS.USER_ACTIVITY_LOG WHERE ACTION = 'LOGON' ORDER BY LOGIN_TIME DESC " log_data <- dbGetQuery(conn, query) if (nrow(log_data) == 0) { message("No login data found in the activity log.") return(NULL) } # Convert to proper datetime format log_data$LOGIN_TIME <- as.POSIXct(log_data$LOGIN_TIME) # Analyze for suspicious patterns # 1. Multiple logins from same IP with different usernames ip_user_combo <- log_data %>% group_by(CLIENT_IP) %>% summarise(unique_users = n_distinct(USERNAME), .groups = 'drop') %>% filter(unique_users > 1) # 2. Multiple logins from same username with different IPs in short time user_ip_combo <- log_data %>% group_by(USERNAME) %>% arrange(LOGIN_TIME) %>% mutate(time_diff = as.numeric(difftime(LOGIN_TIME, lag(LOGIN_TIME), units = "hours"))) %>% filter(!is.na(time_diff) & time_diff < 1) %>% # within 1 hour summarise(unique_ips = n_distinct(CLIENT_IP), .groups = 'drop') %>% filter(unique_ips > 1) # 3. Logins from unusual client programs (exclude known legitimate tools) unusual_programs <- log_data %>% group_by(CLIENT_PROGRAM_NAME) %>% summarise(count = n(), .groups = 'drop') %>% filter(!grepl("TOAD|RStudio|JDBC|ODBC", CLIENT_PROGRAM_NAME, ignore.case = TRUE)) # 4. Logins outside normal business hours (8am-6pm) unusual_hours <- log_data %>% mutate(hour = hour(LOGIN_TIME)) %>% filter(hour < 8 | hour > 18) # Compile suspicious activity into structured list suspicious <- list( multiple_users_same_ip = ip_user_combo, multiple_ips_same_user = user_ip_combo, unusual_client_programs = unusual_programs, logins_outside_hours = unusual_hours, all_logins = log_data # Include all logins for visualization ) return(suspicious)}
# -----------------------------------------------------------------------------------# Execute suspicious login detection# -----------------------------------------------------------------------------------suspicious_activity <- detect_suspicious_logins(conn)
# -----------------------------------------------------------------------------------# Display analysis results# -----------------------------------------------------------------------------------if (!is.null(suspicious_activity)) { cat("=== Suspicious Login Activity Report ===\n\n") # 1. Multiple users from same IP if (!is.null(suspicious_activity$multiple_users_same_ip)) { if (nrow(suspicious_activity$multiple_users_same_ip) > 0) { cat("1. Multiple users logging from same IP address:\n") print(suspicious_activity$multiple_users_same_ip) } else { cat("1. No multiple users from same IP detected\n") } } else { cat("1. IP sharing analysis not available\n") } cat("\n") # 2. Multiple IPs for same user if (!is.null(suspicious_activity$multiple_ips_same_user)) { if (nrow(suspicious_activity$multiple_ips_same_user) > 0) { cat("2. Users logging from multiple IPs in short time (within 1 hour):\n") print(suspicious_activity$multiple_ips_same_user) } else { cat("2. No users with multiple IPs detected\n") } } else { cat("2. Multiple IP analysis not available\n") } cat("\n") # 3. Unusual client programs if (!is.null(suspicious_activity$unusual_client_programs)) { if (nrow(suspicious_activity$unusual_client_programs) > 0) { cat("3. Logins from unusual client programs (excluding TOAD, RStudio, JDBC, ODBC):\n") print(suspicious_activity$unusual_client_programs) } else { cat("3. No unusual client programs detected\n") } } else { cat("3. Client program analysis not available\n") } cat("\n") # 4. Outside business hours if (!is.null(suspicious_activity$logins_outside_hours)) { if (nrow(suspicious_activity$logins_outside_hours) > 0) { cat("4. Logins outside business hours (8am-6pm):\n") print(suspicious_activity$logins_outside_hours %>% select(USERNAME, LOGIN_TIME, CLIENT_IP)) } else { cat("4. No logins outside business hours detected\n") } } else { cat("4. Business hours analysis not available\n") } cat("\n") # 5. Visualization of login activity distribution if (!is.null(suspicious_activity$all_logins)) { cat("5. Login Activity Visualization:\n") login_plot <- ggplot(suspicious_activity$all_logins, aes(x = hour(LOGIN_TIME))) + geom_histogram(binwidth = 1, fill = "steelblue", alpha = 0.8) + labs(title = "Login Activity by Hour of Day", x = "Hour of Day (0-23)", y = "Number of Logins") + scale_x_continuous(breaks = 0:23) + theme_minimal() + theme(plot.title = element_text(hjust = 0.5)) print(login_plot) } else { cat("5. Login activity visualization not available\n") } } else { cat("No login data found in the activity log. No analysis performed.\n")}
# -----------------------------------------------------------------------------------# Cleanup and close database connection# -----------------------------------------------------------------------------------dbDisconnect(conn)cat("\n=== Database connection closed ===\n")
# -----------------------------------------------------------------------------------# END SCRIPT# -----------------------------------------------------------------------------------
# -----------------------------------------------------------------------------------# Load required libraries# -----------------------------------------------------------------------------------library(DBI)library(odbc)library(dplyr)library(lubridate)library(ggplot2)
# -----------------------------------------------------------------------------------# Establish connection to Oracle database# -----------------------------------------------------------------------------------conn <- dbConnect(odbc::odbc(), dsn = "[DB_NAME]", uid = "[USERNAME]", pwd = "[PASSWORD]")
# -----------------------------------------------------------------------------------# Function: detect_suspicious_logins# Description: Retrieves login activity data and analyzes it for suspicious patterns# including IP sharing, client anomalies, and off-hours access.# Parameters:# - conn: Database connection object# Returns: List containing categorized suspicious activities and all login data# -----------------------------------------------------------------------------------detect_suspicious_logins <- function(conn) { # Query the user activity log for login events query <- " SELECT USERNAME, LOGIN_TIME, CLIENT_IP, CLIENT_HOSTNAME, OS_USER, SESSION_ID, ACTION, CLIENT_PROGRAM_NAME, ORACLE_SESSION_ID FROM ATTSTATS.USER_ACTIVITY_LOG WHERE ACTION = 'LOGON' ORDER BY LOGIN_TIME DESC " log_data <- dbGetQuery(conn, query) if (nrow(log_data) == 0) { message("No login data found in the activity log.") return(NULL) } # Convert to proper datetime format log_data$LOGIN_TIME <- as.POSIXct(log_data$LOGIN_TIME) # Analyze for suspicious patterns # 1. Multiple logins from same IP with different usernames ip_user_combo <- log_data %>% group_by(CLIENT_IP) %>% summarise(unique_users = n_distinct(USERNAME), .groups = 'drop') %>% filter(unique_users > 1) # 2. Multiple logins from same username with different IPs in short time user_ip_combo <- log_data %>% group_by(USERNAME) %>% arrange(LOGIN_TIME) %>% mutate(time_diff = as.numeric(difftime(LOGIN_TIME, lag(LOGIN_TIME), units = "hours"))) %>% filter(!is.na(time_diff) & time_diff < 1) %>% # within 1 hour summarise(unique_ips = n_distinct(CLIENT_IP), .groups = 'drop') %>% filter(unique_ips > 1) # 3. Logins from unusual client programs (exclude known legitimate tools) unusual_programs <- log_data %>% group_by(CLIENT_PROGRAM_NAME) %>% summarise(count = n(), .groups = 'drop') %>% filter(!grepl("TOAD|RStudio|JDBC|ODBC", CLIENT_PROGRAM_NAME, ignore.case = TRUE)) # 4. Logins outside normal business hours (8am-6pm) unusual_hours <- log_data %>% mutate(hour = hour(LOGIN_TIME)) %>% filter(hour < 8 | hour > 18) # Compile suspicious activity into structured list suspicious <- list( multiple_users_same_ip = ip_user_combo, multiple_ips_same_user = user_ip_combo, unusual_client_programs = unusual_programs, logins_outside_hours = unusual_hours, all_logins = log_data # Include all logins for visualization ) return(suspicious)}
# -----------------------------------------------------------------------------------# Execute suspicious login detection# -----------------------------------------------------------------------------------suspicious_activity <- detect_suspicious_logins(conn)
# -----------------------------------------------------------------------------------# Display analysis results# -----------------------------------------------------------------------------------if (!is.null(suspicious_activity)) { cat("=== Suspicious Login Activity Report ===\n\n") # 1. Multiple users from same IP if (!is.null(suspicious_activity$multiple_users_same_ip)) { if (nrow(suspicious_activity$multiple_users_same_ip) > 0) { cat("1. Multiple users logging from same IP address:\n") print(suspicious_activity$multiple_users_same_ip) } else { cat("1. No multiple users from same IP detected\n") } } else { cat("1. IP sharing analysis not available\n") } cat("\n") # 2. Multiple IPs for same user if (!is.null(suspicious_activity$multiple_ips_same_user)) { if (nrow(suspicious_activity$multiple_ips_same_user) > 0) { cat("2. Users logging from multiple IPs in short time (within 1 hour):\n") print(suspicious_activity$multiple_ips_same_user) } else { cat("2. No users with multiple IPs detected\n") } } else { cat("2. Multiple IP analysis not available\n") } cat("\n") # 3. Unusual client programs if (!is.null(suspicious_activity$unusual_client_programs)) { if (nrow(suspicious_activity$unusual_client_programs) > 0) { cat("3. Logins from unusual client programs (excluding TOAD, RStudio, JDBC, ODBC):\n") print(suspicious_activity$unusual_client_programs) } else { cat("3. No unusual client programs detected\n") } } else { cat("3. Client program analysis not available\n") } cat("\n") # 4. Outside business hours if (!is.null(suspicious_activity$logins_outside_hours)) { if (nrow(suspicious_activity$logins_outside_hours) > 0) { cat("4. Logins outside business hours (8am-6pm):\n") print(suspicious_activity$logins_outside_hours %>% select(USERNAME, LOGIN_TIME, CLIENT_IP)) } else { cat("4. No logins outside business hours detected\n") } } else { cat("4. Business hours analysis not available\n") } cat("\n") # 5. Visualization of login activity distribution if (!is.null(suspicious_activity$all_logins)) { cat("5. Login Activity Visualization:\n") login_plot <- ggplot(suspicious_activity$all_logins, aes(x = hour(LOGIN_TIME))) + geom_histogram(binwidth = 1, fill = "steelblue", alpha = 0.8) + labs(title = "Login Activity by Hour of Day", x = "Hour of Day (0-23)", y = "Number of Logins") + scale_x_continuous(breaks = 0:23) + theme_minimal() + theme(plot.title = element_text(hjust = 0.5)) print(login_plot) } else { cat("5. Login activity visualization not available\n") } } else { cat("No login data found in the activity log. No analysis performed.\n")}
# -----------------------------------------------------------------------------------# Cleanup and close database connection# -----------------------------------------------------------------------------------dbDisconnect(conn)cat("\n=== Database connection closed ===\n")
# -----------------------------------------------------------------------------------# END SCRIPT# -----------------------------------------------------------------------------------