Working towards a certification in cybersecurity, I was given ample chance to practice using SQL, Python and translating information to stakeholders to effectively share necessary information. These are a handful of the projects I had worked in in that time that display my ability using SQL, understanding technical issues and communicating findings with stakeholders.

Audit results for stakeholders

TO: IT Manager, Stakeholders

FROM: Daniel Sbar

DATE: August 5th, 2023

SUBJECT: Internal IT Audit Findings and Recommendations

Dear Colleagues,

Please review the following information regarding the Botium Toys internal audit scope, goals, critical findings, summary and recommendations.

Scope: The following systems are in scope: accounting, end point detection, firewalls, intrusion detection system, security information and event management (SIEM) tool.

User Permissions

Implemented Controls

Procedures and Protocol sets

Ensure current user permissions, controls, procedures, and protocols in place align with necessary compliance requirements for PCI DSS and GDPR.

Ensure current technology is accounted for. Both hardware and system access.

Goals:

To adhere to NIST CSF.

Establish a better process for their systems to ensure they are compliant

Fortify system controls

Implement the concept of least permissions when it comes to user credential management

Establish their policies and procedures, which includes their playbooks

Ensure they are meeting compliance requirements

Critical findings (must be addressed immediately): Due to the current lack of controls at the moment there are a multitude of goals that would need to be implemented in order to meet the audit goals as well as be in compliance with federal and international guidelines.

1. Control of Least Privilege
2. Disaster recovery plans
3. Password, access control, and account management policies, including the implementation of a password management system
4. Separation of Duties
5. Encryption (for secure website transactions)
6. IDS
7. Backups
8. AV software
9. Locks
10. Manual monitoring, maintenance, and intervention for legacy systems
11. Fire detection and prevention systems

Findings (should be addressed, but no immediate need): Although these controls are still important, they are less time sensitive and less severe to be implemented.

1. Time-controlled safe
2. Adequate lighting
3. Locking cabinets
4. CCTV
5. Signage indicating alarm service provider

Summary/Recommendations: Botium Toys currently accepts online orders internationally and should follow international payment guidelines regarding its security of sensitive information. It is recommended that most urgently the organization should focus on becoming in compliance with the relevant guidelines, PCI DSS and GDPR, since the business accepts payments from the E.U. In order to manage the other critical findings to meet the goals of the audit, Botium Toys should be able to use SOC1 and SOC2 for guidance in adapting the concept of least permission in a way that best suits the company’s needs for user access and data protection. Although one hopes not for a disaster, disaster recovery procedures are critically important in the event of a major event that could disrupt business operations and continuity. Integrating an IDS and AV software into the current systems will support the company’s ability to identify and mitigate potential risks, and could help with intrusion detection, since existing legacy systems require manual monitoring and intervention. For the prevention of a breach or any loss of assets at Botium Toys physical location, it would be recommended to also implement controls such as locks to protect physical assets and monitor any threat that arises. There are other controls that could be added with a lower level of severity including, the use of CCTV, adequate lighting, encryption, a time-controlled safe, locking cabinets, fire detection and prevention systems, and signage indicating alarm service providers to improve Botium Toys’ security posture in their physical location.

Network traffic analysis

Summary of the problem found in the DNS and ICMP traffic log.

The UDP protocol reveals that: The DNS server is down or unreachable.

The UDP protocol reveals that: The DNS server is down or unreachable.

The port noted in the error message is used for: Port 53 is mostly used for DNS traffic.

The most likely issue is: This most likely means that DNS is not responding or is not responding correctly.

Analysis of the data and provide at least one cause of the incident.

Time incident occurred: The logs show the first time there were issues for customers to reach the destination port while attempting to use the website was at 1:24 pm today.

How the IT team became aware of the incident: Customers were receiving an error that would give them a message saying “destination port is unreachable” when attempting to visit the website. Customers then notified the IT team of this issue.

The actions taken by the IT department to investigate the incident: The network security team is investigating the issue to return the website to be usable by customers sufficiently until we can fix any problem(s) that may have caused this issue.

Key findings of the IT department's investigation (i.e., details related to the port affected, DNS server, etc.): The network security team conducted packet sniffing tests using tcpdump. It was confirmed that DNS port 53 was unreachable. The next step is to identify whether the DNS server is down or traffic to port 53 is blocked by the firewall.

Note a likely cause of the incident: The DNS server may be unresponsive at this time due to a DoS/DDoS attack or a misconfiguration.

Security incident report

OS hardening techniques

Section 1: Identify the network protocol involved in the incident

The protocol impacted in the incident is Hypertext transfer protocol (HTTP). Running tcpdump and accessing the yummyrecipesforme.com website to detect the problem, capture protocol, and traffic activity in a DNS & HTTP traffic log file provided the evidence needed to come to this conclusion. The malicious file is observed being transported to the users’ computers using the HTTP protocol at the application layer.

Section 2: Document the incident

Several customers contacted the website owner stating that when they visited the website, they were prompted to download and run a file that asked them to update their browsers. Their personal computers have been operating slowly ever since. The website owner tried logging into the web server but noticed they were locked out of their account.

The cybersecurity analyst used a sandbox environment to test the website without impacting the company network. Then, the analyst ran tcpdump to capture the network and protocol traffic packets produced by interacting with the website. The analyst was prompted to download a file claiming it would update the user’s browser, accepted the download and ran it. The browser then redirected the analyst to a fake website (greatrecipesforme.com) that looked identical to the original site (yummyrecipesforme.com).

The cybersecurity analyst inspected the tcpdump log and observed that the browser initially requested the IP address for the yummyrecipesforme.com website. Once the connection with the website was established over the HTTP protocol, the analyst recalled downloading and executing the file. The logs showed a sudden change in network traffic as the browser requested a new IP resolution for the greatrecipesforme.com URL. The network traffic was then rerouted to the new IP address for the greatrecipesforme.com website.

The senior cybersecurity professional analyzed the source code for the websites and the downloaded file. The analyst discovered that an attacker had manipulated the website to add code that prompted the users to download a malicious file disguised as a browser update. Since the website owner stated that they had been locked out of their administrator account, the team believes the attacker used a brute force attack to access the account and change the admin password. The execution of the malicious file compromised the end users’ computers.

Section 3: Recommendation of remedy for brute force attacks

One security measure the team plans to implement to protect against brute force attacks is two-factor authentication (2FA). This 2FA plan will include an additional requirement for users to validate their identification by confirming a one-time password (OTP) sent to either their email or phone. Once the user confirms their identity through their login credentials and the OTP, they will gain access to the system. Any malicious actor that attempts a brute force attack will not likely gain access to the system because it requires additional authorization.

Filtering SQL queries

The organization is working to make their system more secure. It is my job to ensure the system is safe, investigate potential security issues, and update employee computers as needed. The following steps provide examples of how I used SQL with filters to perform security-related tasks.

Retrieve after hours failed login attempts

There was a potential security incident that occurred after business hours (after 18:00). All after hours login attempts that failed need to be investigated.

The following code demonstrates how I created a SQL query to filter for failed login attempts that occurred after business hours.

The first part of the screenshot is my query, and the second part is a portion of the output. This query filters for failed login attempts that occurred after 18:00. First, I started by selecting all data from the log_in_attempts> table. Then, I used a WHERE clause with an AND operator to filter my results to output only login attempts that occurred after 18:00 and were unsuccessful. The first condition is login_time > '18:00', which filters for the login attempts that occurred after 18:00. The second condition is success = FALSE, which filters for the failed login attempts.

Retrieve login attempts on specific dates

A suspicious event occurred on 2022-05-09. Any login activity that happened on 2022-05-09 or on the day before needs to be investigated.

The following code demonstrates how I created a SQL query to filter for login attempts that occurred on specific dates.

The first part of the screenshot is my query, and the second part is a portion of the output. This query returns all login attempts that occurred on 2022-05-09 or 2022-05-08. First, I started by selecting all data from the log_in_attempts table. Then, I used a WHERE clause with an OR operator to filter my results to output only login attempts that occurred on either 2022-05-09 or 2022-05-08. The first condition is login_date = '2022-05-09', which filters for logins on 2022-05-09. The second condition is login_date = '2022-05-08', which filters for logins on 2022-05-08.

Retrieve login attempts outside of Mexico

After investigating the organization’s data on login attempts, I believe there is an issue with the login attempts that occurred outside of Mexico. These login attempts should be investigated.

The following code demonstrates how I created a SQL query to filter for login attempts that occurred outside of Mexico.

The first part of the screenshot is my query, and the second part is a portion of the output. This query returns all login attempts that occurred in countries other than Mexico. First, I started by selecting all data from the log_in_attempts table. Then, I used a WHERE clause with NOT to filter for countries other than Mexico. I used LIKE with MEX% as the pattern to match because the dataset represents Mexico as MEX and MEXICO. The percentage sign (%) represents any number of unspecified characters when used with LIKE.

Retrieve employees in Marketing

My team wants to update the computers for certain employees in the Marketing department. To do this, I have to get information on which employee machines to update.

The following code demonstrates how I created a SQL query to filter for employee machines from employees in the Marketing department in the East building.

The first part of the screenshot is my query, and the second part is a portion of the output. This query returns all employees in the Marketing department in the East building. First, I started by selecting all data from the employees table. Then, I used a WHERE clause with AND to filter for employees who work in the Marketing department and in the East building. I used LIKE with East% as the pattern to match because the data in the office column represents the East building with the specific office number. The first condition is the department = 'Marketing' portion, which filters for employees in the Marketing department. The second condition is the office LIKE 'East%' portion, which filters for employees in the East building.

Retrieve employees in Finance or Sales

The machines for employees in the Finance and Sales departments also need to be updated. Since a different security update is needed, I have to get information on employees only from these two departments.

The following code demonstrates how I created a SQL query to filter for employee machines from employees in the Finance or Sales departments.

The first part of the screenshot is my query, and the second part is a portion of the output. This query returns all employees in the Finance and Sales departments. First, I started by selecting all data from the employees table. Then, I used a WHERE clause with OR to filter for employees who are in the Finance and Sales departments. I used the OR operator instead of AND because I want all employees who are in either department. The first condition is department = 'Finance', which filters for employees from the Finance department. The second condition is department = 'Sales', which filters for employees from the Sales department.

Retrieve all employees not in IT

My team needs to make one more security update on employees who are not in the Information Technology department. To make the update, I first have to get information on these employees.

The following demonstrates how I created a SQL query to filter for employee machines from employees not in the Information Technology department.

The first part of the screenshot is my query, and the second part is a portion of the output. The query returns all employees not in the Information Technology department. First, I started by selecting all data from the employees table. Then, I used a WHERE clause with NOT to filter for employees not in this department.

Summary

I applied filters to SQL queries to get specific information on login attempts and employee machines. I used two different tables, log_in_attempts and employees. I used the AND, OR, and NOT operators to filter for the specific information needed for each task. I also used LIKE and the percentage sign (%) wildcard to filter for patterns.