Enhancing Security with Python

Author

Richard Malcolm

Date

February 28, 2025

Enhancing Security with Python: Automating Access Control and Database Analysis

Project Overview

The primary goal of this project is to ensure that only authorized employees can access restricted content by managing an IP allow list and analyzing security logs. The script performs two key tasks:

Updating the IP Allow List: Removes unauthorized IP addresses from the allow list based on a predefined removal list.
Applying SQL Queries for Security Analysis: Extracts insights from an SQLite database to identify suspicious login attempts and ensure proper access control.

By automating these processes, organizations can strengthen their security posture and streamline access management.

How It Works

1. Updating the IP Allow List

The first function, update_allow_list, ensures that only approved IP addresses have access to restricted resources. The function:

Reads the existing allow list from a file.
Reads the list of IPs that should be removed.
Filters out unauthorized IPs.
Updates the allow list file with only the permitted addresses.

import sqlite3

def update_allow_list(allow_file, remove_file):
    # Read allow list
    with open(allow_file, "r") as file:
        ip_addresses = file.read().split()  # Convert to list

    # Read remove list
    with open(remove_file, "r") as file:
        remove_list = file.read().split()

    # Remove IPs found in remove list
    ip_addresses = [ip for ip in ip_addresses if ip not in remove_list]

    # Write updated allow list
    with open(allow_file, "w") as file:
        file.write("\n".join(ip_addresses))
    
    print("Allow list updated.")

This function prevents unauthorized access by dynamically updating access permissions, ensuring that only authorized employees retain access to restricted resources.

2. SQL Queries for Security Analysis

The second function, apply_sql_filters, runs a series of SQL queries against an SQLite database to analyze security-related login attempts and employee data. The queries help identify:

Failed logins after business hours: Potential indicators of unauthorized access attempts.
Logins on specific dates: Useful for investigating security incidents.
Logins from unauthorized locations: Helps detect potential location-based fraud.
Employees by department and location: Ensures security policies are applied correctly.

def apply_sql_filters(db_path):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # Retrieve after-hours failed login attempts
    cursor.execute("""
        SELECT * FROM log_in_attempts 
        WHERE login_time > '18:00' AND success = FALSE
    """)
    print("After-hours failed logins:", cursor.fetchall())
    
    # Retrieve login attempts on specific dates
    cursor.execute("""
        SELECT * FROM log_in_attempts 
        WHERE login_date IN ('2022-05-09', '2022-05-08')
    """)
    print("Logins on specific dates:", cursor.fetchall())
    
    # Retrieve login attempts outside Mexico
    cursor.execute("""
        SELECT * FROM log_in_attempts 
        WHERE country NOT LIKE 'MEX%'
    """)
    print("Logins outside Mexico:", cursor.fetchall())
    
    # Retrieve employees in Marketing in the East building
    cursor.execute("""
        SELECT * FROM employees 
        WHERE department = 'Marketing' AND office LIKE 'East%'
    """)
    print("Marketing employees in East building:", cursor.fetchall())
    
    # Retrieve employees in Finance or Sales
    cursor.execute("""
        SELECT * FROM employees 
        WHERE department IN ('Finance', 'Sales')
    """)
    print("Employees in Finance or Sales:", cursor.fetchall())
    
    # Retrieve all employees not in IT
    cursor.execute("""
        SELECT * FROM employees 
        WHERE department != 'IT'
    """)
    print("Employees not in IT:", cursor.fetchall())
    
    conn.close()

# Example usage
update_allow_list("allow_list.txt", "remove_list.txt")
apply_sql_filters("company_database.db")

This function helps IT teams monitor security events effectively and take proactive measures to prevent potential threats.

Key Benefits of This Approach

Automation: Reduces manual effort and improves efficiency in security monitoring.
Security Enforcement: Ensures only authorized users can access restricted systems.
Proactive Threat Detection: Identifies unusual login attempts, enabling faster response to potential breaches.
Scalability: Easily integrates with existing security frameworks and can be expanded as needed.

Conclusion

This Python-based security automation project is a valuable tool for enforcing access control and enhancing security monitoring in an organization. By updating the IP allow list dynamically and running SQL queries to analyze login patterns, businesses can proactively identify and mitigate security risks.