SQL Scripting

Scenario

I am a security professional at a large organization. Part of my job is to investigate security issues and keep the system secure. I recently discovered some potential security issues that involve login attempts and employee machines.

My task is to examine the organization’s data in the employees and log_in_attempts tables.

Project Description

My organization has assigned specific roles to my position:

Ensure the system is safe.
Investigate all potential security issues.
Update employee computers as needed.

Each of the following sub-headers provides examples of how I used SQL with filters to perform security-related tasks.

Retrieve after-hours failed log-in attempts

Management wants to know all failed log-in attempts after 6:00 p.m. I used the > and AND operators to find the requested data.

NOTE: MySQL stores Boolean values as 1 for TRUE and 0 for FALSE. So, TRUE is represented as 1, and FALSE is represented as 0 in the success column.

Retrieve log-in attempts on specific dates

A suspicious event occurred on 2022-05-09. To investigate this event, I review all login attempts which occurred on this day and the day before.

I use the OR operator to find all login attempts that occurred on 2022-05-09 or 2022-05-08.

Retrieve log-in attempts outside of Mexico

There’s been suspicious activity with login attempts, but the team has determined that this activity didn't originate in Mexico. Now, I need to investigate login attempts that occurred outside of Mexico.

I use the NOT and LIKE operators to achieve this. I also use the % wild card to account for variations in how Mexico is written by teammates, such as "MEX" or "MEXICO."

Retrieve employees in Marketing

My team wants to perform security updates on specific employee machines in the Marketing department. I'm responsible for getting information on these employee machines and will need to query the employees table.

I specifically need information about employees in the Marketing department located in all offices in the East building (such as 'East-170' or 'East-320').

I use the = AND and LIKE operators to retrieve the information. I also use the % wildcard to account for variations in East building names.

Retrieve employees in Finance or Sales

My team now needs to perform a different security update on machines for employees in the Sales and Finance departments.

I use the OR operator to locate all of them.

Retrieve all employees not in IT

My team needs to make one more update to employee machines. The employees in the Information Technology department already had this update, but employees in all other departments need it.

I use the NOT operator as a filter to locate employees and machines in other departments so that I can determine which machines need to be updated.

Summary

This activity showed the power of using SQL as a querying tool to manipulate and search through large volumes of data. Throughout the activity, I used different filters or operators to illustrate the functionality of SQL for cybersecurity purposes.

Page updated

Google Sites

Report abuse