Data Breach Investigation at GlobalTech Solutions

Scenario: Investigating Suspicious Network Activities

As part of a cybersecurity audit simulation, my team and I were tasked with investigating suspicious activities in GlobalTech Solutions' network logs. Our goal was to identify any potential security breaches, unauthorized access attempts, and areas that needed updates. 

1) Identify Suspicious Login Attempts

The objective is to find login attempts made outside normal business hours.

SQL Query

SELECT *
FROM login_attempts
WHERE login_time > '18:00' AND success = 0;

Example Output

+----+---------+---------------+------------+---------+
| id | user_id | ip_address    | login_time | success |
+----+---------+---------------+------------+---------+
| 45 | 1023    | 192.168.1.105 | 20:15:30   | FALSE   |
| 46 | 2056    | 10.0.0.25     | 19:05:12   | FALSE   |
| 47 | 3078    | 172.16.0.100  | 21:30:45   | FALSE   |
+----+---------+---------------+------------+---------+

Actionable insights

Take a closer look at the users associated with these failed attempts.

Look for repeated failed attempts from the same IP, which could mean a brute-force attack.

2) Analyze Login Attempts from Specific Countries

The objective is to find all of the login attempts from countries flagged for suspicious activity. The flagged countries are 'Brazil', 'India', and 'South Africa'.

SQL Query

SELECT * 
FROM login_attempts 
WHERE country IN ('Brazil', 'India', 'South Africa');

Example Output

+----+---------+---------------+------------+--------------+---------+
| id | user_id | ip_address    | login_date | country      | success |
+----+---------+---------------+------------+--------------+---------+
| 201| 8045    | 203.0.113.42  | 2023-06-18 | Brazil       | FALSE   |
| 202| 9067    | 198.51.100.73 | 2023-06-18 | India        | FALSE   |
| 203| 7056    | 198.51.100.80 | 2023-06-19 | South Africa | TRUE    |
+----+---------+---------------+------------+--------------+---------+

Findings: Some login attempts, both successful and unsuccessful, came from flagged countries.
Actionable Insights: Investigate whether the logins were valid and implement security measures such as restricting access from specific locations or multi-factor authentication for users.

3) Chart login data

The objective is to create a simple bar chat showing the number of failed login attempts in each country over the past week.

SQL Query

SELECT country, COUNT(*) as failed_attempts
FROM login_attempts
WHERE success = FALSE AND login_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY country
ORDER BY failed_attempts DESC
LIMIT 5;

Example Output

+--------------+-----------------+
| country      | failed_attempts |
+--------------+-----------------+
| Brazil       | 156             |
| India        | 98              |
| South Africa | 75              |
| Canada       | 42              |
| Australia    | 23              |
+--------------+-----------------+

Actionable insights: Monitor and implement security measures like rate-limiting for countries with a large number of failed attempts. 

4) Get Employees in Marketing Department (west building)

The objective is to query the employees table to identify all of the employees in the marketing department for offices in the West building for a security update.

SQL Query

SELECT employee_id, name, department, office
FROM employees
WHERE department = 'Marketing' AND office LIKE 'West-%';

Example Output

+-------------+----------------+------------+----------+
| employee_id | name           | department | office   |
+-------------+----------------+------------+----------+
| 1431        | Joe Sampson    | Marketing  | West-130 |
| 1852        | Alicia Johnson | Marketing  | West-324 |
+-------------+----------------+------------+----------+

5) Get employees in the Finance and sales department

The objective was to use filters in SQL to create a query that shows all employees in the Sales or Finance departments for a security update.

SQL Query

SELECT employee_id, name, department
FROM employees
WHERE department IN ('Sales', 'Finance');

Example Output

+-------------+----------------+-----------+
| employee_id | name           | department|
+-------------+----------------+-----------+
| 2001        | Sammy Lee      | Sales     |
| 2002        | Rachel Bean    | Finance   |
+-------------+----------------+-----------+

6) Review User Permissions

The objective is to generate a list of users with access to the financial_reports folder, and to make sure permissions are only accessible to authorized personnel.

SQL Query

SELECT u.username, u.department, p.folder_path
FROM users u
JOIN permissions p ON u.user_id = p.user_id
WHERE p.folder_path = '/financial_reports' AND u.department != 'Finance';

Example Output

+----------+------------+-------------------+
| username | department | folder_path       |
+----------+------------+-------------------+
| jsmith   | Sales      | /financial_reports|
| ajonas   | IT         | /financial_reports|
+----------+------------+-------------------+

Actionable Insights:

Adjust access controls so that only Finance department members can access financial reports.

GlobalTech Solutions Security Audit Report 

Key Findings: 

1. Multiple failed login attempts after business hours. 

2. High number of failed login attempts from Brazil, India, and South Africa.

3. Non-Finance department users have access to financial reports. 

Recommendations: 

1. Implement stronger password policies and consider multi-factor authentication. 

2. Investigate and block traffic from high-risk countries. 

3. Restrict access to admin pages. 

4. Ensure only Finance department users have access to financial reports.