4 Database Projects for IT and Cybersecurity Portfolios

Before You Start

You do not need previous MySQL experience to complete the first project. You should be comfortable installing local software, opening files, reading a support request carefully, and running commands without blindly changing values you do not understand. Project 1 introduces the database concepts as you need them, and the Atlas Technical Primer & Naming Reference is there when a term is unfamiliar.

Keep each project independent

Every project has its own setup script. When you move from one project to the next, load that project's seed instead of carrying forward your previous changes:

Project 1 seed
   ↓
complete Project 1
   ↓
load Project 2 seed
   ↓
complete Project 2

Do not try to preserve a single evolving atlas_ops database across the full guide. The continuing company and story give the projects context; the independent seeds give every learner the same technical starting point.

Setup is not the portfolio project

Installing MySQL is necessary, but it is not the evidence you are trying to show an employer. The portfolio value comes from what you do after the environment works: supporting a business request, classifying real database records in context, remediating excessive access, and investigating and recovering from suspicious changes.

The setup section is therefore intentionally explicit. There is no benefit in making installation harder than it needs to be.

Set Up the Atlas Environment

Before you start the first project, you need a working MySQL server and a way to run SQL against it.

This is setup, not the project.

The goal is simple: install MySQL, load the supplied Avenloch training database, switch away from the MySQL administrative account, and prove that the environment is in the correct starting state.

If you already have MySQL installed, read the note below before changing anything.

Already use MySQL or MariaDB? Do not uninstall, downgrade, replace, or reconfigure an existing database environment just for this guide. Use a separate lab system, virtual machine, or another isolated installation instead. The Ubuntu/Debian instructions below specifically assume a fresh system with no existing MySQL installation.

MySQL Server and MySQL Workbench are different things

If this is your first time working with a database, the names can be confusing.

MySQL Community Server is the database server. It stores the Atlas data and executes the SQL you send to it.

MySQL Workbench is a graphical client. It gives you a window where you can connect to MySQL, open SQL files, run queries, and inspect the results.

For these projects, the server is required. Workbench is recommended because it makes the first few projects easier to follow.

Oracle notes that MySQL Workbench is developed and tested against MySQL Server 8.0. It can connect to MySQL 8.4 and later, but some Workbench features may not support newer server versions. That does not affect what we are using it for here: connecting to the local server, opening SQL scripts, running queries, and viewing results.

Oracle's Workbench download page now offers a newer release line, MySQL Workbench 26. The Workbench steps in this guide were written for Workbench 8.0, so a menu or button label may differ slightly in Workbench 26. If Workbench does not behave as described, the mysql command-line client installed with the server can run every file in this lab with its source command.

• MySQL Community Server 8.4 Long-Term Support (LTS)
• MySQL Workbench
• MySQL Workbench Manual

Version note: This guide targets the MySQL 8.4 LTS series. Oracle listed MySQL Community Server 8.4.11 LTS when this section was last verified. If the patch number has changed by the time you read this, use the current 8.4 LTS release rather than hunting for the exact patch shown here.

Download the project files first

The SQL lab is published as individual .sql files, and each tab of this guide lists the files it uses. For the environment setup and Project 1, download these three files into one folder that is easy to find:

• avenloch-atlas-00-lab-accounts.sql
• avenloch-atlas-p1-01-setup.sql
• avenloch-atlas-p1-02-admin-grants.sql

SHA-256 checksums (how to check a download):

05477e4fb611ef64ee4664d9e78a10c88705d7368b03375700f6c536eaa5ecec  avenloch-atlas-00-lab-accounts.sql
6ed1950ab7de47ed95ee54e70f5ad06f801e309ce7af6907c758579da7966d89  avenloch-atlas-p1-01-setup.sql
6850717ef5082ccc1f2732e0909d781dd519c0d4c8f360f2b2f46aa9edb593f8  avenloch-atlas-p1-02-admin-grants.sql

Use a web browser to download them; command-line downloaders may be refused and save an error page instead (Check your downloads explains how the checksums catch that).

Do not run them yet.

The files are separated deliberately. The first creates the synthetic lab accounts. The second creates the Project 1 version of the Atlas database. The third grants the lab accounts the permissions they need. Project 1 also has a verification file, which you download at the end of that project (Run the supplied verification file).

Load order for every project

Each project has its own independent starting state. Before you begin a project, load that project's files in this order. Each tab lists and links its own files again.

Once, before any project:  avenloch-atlas-00-lab-accounts.sql
Project 1:  avenloch-atlas-p1-01-setup.sql, then avenloch-atlas-p1-02-admin-grants.sql
           after the work: avenloch-atlas-p1-03-verification.sql
Project 2:  avenloch-atlas-p2-01-setup.sql, then avenloch-atlas-p2-02-admin-grants.sql
Project 3:  avenloch-atlas-p3-01-setup.sql, then avenloch-atlas-p3-02-admin-setup.sql
           optional, after remediation: avenloch-atlas-p3-03-validation-queries.sql
Project 4:  avenloch-atlas-p4-01-setup.sql, then avenloch-atlas-p4-02-admin-setup.sql
           optional orientation queries: avenloch-atlas-p4-03-investigation-starter.sql

Windows

For a first installation on Windows, use Oracle's recommended MSI installation path.

Oracle documents the MSI plus MySQL Configurator as the simplest way to install and configure MySQL Server on Windows.

Official MySQL 8.4 Windows installation documentation

1. Install MySQL Community Server

Open the MySQL Community Server 8.4 LTS download page, select Microsoft Windows, and download the MSI package.

Run the installer.

When the server installation finishes, launch MySQL Configurator when prompted. The server is installed at this point, but it is not ready to use until it has been configured.

2. Configure it for a local development machine

When MySQL Configurator asks how the server will be used, choose:

Configuration Type: Development

Oracle describes the Development profile as the option intended for a personal workstation running other applications. It allocates the least memory of the standard profiles, which is appropriate for this lab.

For networking, keep:

TCP/IP: Enabled
Port: 3306

3306 is the default port for the classic MySQL protocol.

You do not need other computers to connect to this database. If Configurator offers Open Windows Firewall port for network access, leave that unchecked for this local lab.

Keep the server configured as a Windows service so it can start normally with Windows.

3. Create the MySQL root password

Configurator will ask you to create a password for the MySQL root account.

root is MySQL's administrative database account. It is separate from your Windows account.

Create a password and save it somewhere secure. You will use root briefly to create the training environment. You will not do the actual projects as root.

Do not use the example Avenloch lab passwords as your MySQL root password.

If the installer reports a missing Windows prerequisite, use Oracle's installation documentation rather than downloading runtime files from an unofficial mirror. MySQL 8.4 Server requires the Microsoft Visual C++ 2019 Redistributable on Windows.

4. Install MySQL Workbench

Install MySQL Workbench.

On Windows, Oracle also supports managing Workbench through MySQL Installer. Either route is fine for this project.

Open Workbench when the installation is finished.

macOS

Oracle provides a native DMG installer for MySQL on macOS.

Official MySQL 8.4 macOS installation documentation

1. Choose the correct download

On the MySQL Community Server 8.4 LTS page, choose the build that matches your Mac:

• ARM / arm64 for Apple Silicon Macs
• x86-64 for Intel Macs

Download the DMG and open it.

2. Install and configure MySQL

Run the package installer inside the DMG.

For a new installation, Oracle's installer asks you to define the MySQL root password and whether the server should start after configuration.

Create and save the root password.

Start the server if the installer gives you that option.

3. Install Workbench

Download MySQL Workbench for the same Mac architecture.

Oracle distributes Workbench for macOS as a DMG. Open it and move Workbench into Applications when prompted.

Ubuntu or Debian

For Ubuntu and Debian, this guide uses Oracle's MySQL APT Repository so you can deliberately install the MySQL 8.4 LTS series.

Official MySQL APT Repository instructions

Stop if MySQL, MariaDB, or another MySQL-compatible server is already installed. Oracle's fresh-install instructions assume that no MySQL installation is already present. Replacing or migrating an existing database installation is a separate process and is outside the scope of this project.

1. Add Oracle's MySQL APT repository

Download the repository configuration package from:

MySQL APT Repository

Then install the downloaded package. Replace the placeholder with the actual filename you downloaded:

sudo dpkg -i /PATH/mysql-apt-config_<version>_all.deb

During the repository configuration, select the MySQL 8.4 LTS series if it is not already selected.

Update the package information:

sudo apt-get update

2. Install MySQL Server

sudo apt-get install mysql-server

Oracle's APT installation also installs the MySQL client and common database files.

When the installer asks for a MySQL root password, create one and save it.

Oracle allows Linux users to leave this blank and use local socket authentication instead. Do not take that branch for this lab. Giving root a password keeps the setup path consistent with the Windows and macOS instructions and makes the first Workbench connection easier to understand.

The MySQL service should start automatically after installation.

Check it with:

systemctl status mysql

You are looking for an active/running service.

3. Choose your SQL client

If MySQL Workbench is available for your supported Linux distribution, you can use it and follow the same Workbench steps used throughout this article.

MySQL Workbench installation documentation

If Workbench is not available for your system, the mysql command-line client installed with MySQL Server is enough to complete the projects. The SQL itself does not depend on Workbench.

Connect to MySQL for the first time

Open MySQL Workbench.

Create a connection to the local MySQL server using:

Connection Name: Local MySQL 8.4 - Admin
Hostname: localhost
Port: 3306
Username: root

localhost means the MySQL server is running on the same computer you are using.

3306 is the default MySQL TCP/IP port we kept during setup.

Enter the root password you created during installation and test the connection.

If Workbench connects successfully, open a new SQL tab.

Make sure the server actually works

Run:

SELECT VERSION();

You should see a version in the MySQL 8.4 series.

Then run:

SELECT 'Atlas setup check' AS message;

You should get one row containing:

Atlas setup check

Finally:

SHOW DATABASES;

On a clean installation, you will see MySQL's system databases. atlas_ops should not exist yet.

If any of these fail, fix the MySQL installation before loading the project files. That keeps a server-installation problem from looking like a broken Atlas project.

Load the Project 1 Atlas database

Stay connected as root for this section.

Training environment: these files change the MySQL server they run on. Use the dedicated lab installation from this guide, never a server that holds real data or accounts you care about.

• avenloch-atlas-00-lab-accounts.sql deletes and recreates three MySQL accounts: av_it_admin, av_reporting and atlas_app (all @localhost). An existing account with one of those names is replaced and loses its privileges.
• avenloch-atlas-p1-01-setup.sql drops and recreates the atlas_ops training database.

Close any Workbench connection that uses one of the lab accounts before you run the accounts file. The cleanup step at the end of this guide removes everything these files create.

You are going to run three supplied scripts in this order:

1. avenloch-atlas-00-lab-accounts.sql
2. avenloch-atlas-p1-01-setup.sql
3. avenloch-atlas-p1-02-admin-grants.sql

You only need to run the accounts file once. Running it again resets the three accounts to their original passwords and removes their permissions, so if you ever rerun it, rerun the current project's admin file afterwards.

Open the first script in Workbench

Use Workbench's Open SQL Script control and select:

avenloch-atlas-00-lab-accounts.sql

Before executing it, look at the filename in the editor so you know you opened the intended file.

Before you run each of these files, skim it in the editor: every account it creates ends in @'localhost', and every database it touches is atlas_ops or atlas_preincident. If you see anything else, you have the wrong file: close it without running it.

Then use Execute All or Selection to run the script.

Workbench executes only the selected text when part of the editor is highlighted. For these setup files, do not select a few lines and run only part of the script. Run the complete file.

Repeat the process for:

avenloch-atlas-p1-01-setup.sql

and then:

avenloch-atlas-p1-02-admin-grants.sql

If Workbench reports an error, stop at the first error. Do not keep running the remaining setup files and hope the environment fixes itself.

Switch to the Avenloch lab account

You no longer need to work as MySQL root.

Create another Workbench connection:

Connection Name: Avenloch - IT Admin
Hostname: localhost
Port: 3306
Username: av_it_admin
Password: AvItAdmin-LabOnly-2026!

That password is intentionally included with the synthetic training environment so everyone can reproduce the same lab. The same lab-only passwords appear in plain text inside the downloaded .sql files. That is acceptable here only because these accounts exist on your local lab server and nowhere else.

It is not an example of a password you should use on a real system. When you finish, the cleanup step deletes these accounts.

Connect as av_it_admin.

Run:

SELECT USER();

The result should show the av_it_admin account being used for the connection.

From this point forward, use the Avenloch lab account unless a project specifically tells you that an administrative setup step requires otherwise.

Check that Atlas loaded correctly

First select the Atlas database:

USE atlas_ops;

Then list its tables:

SHOW TABLES;

You should see the Atlas tables used throughout these projects, including records for customers, projects, sites, missions, datasets, findings, invoices, payments, employees, contractors, Atlas users, and database-change auditing.

You can also count the tables:

SELECT COUNT(*) AS atlas_table_count
FROM information_schema.tables
WHERE table_schema = 'atlas_ops';

The supplied Project 1 environment should return:

14

That confirms that the full schema loaded.

Now check the scenario itself.

Check the Verdant Mesa sites

Run:

SELECT site_id, site_name
FROM sites
WHERE project_id = 'PRJ-1060'
ORDER BY site_id;

At the start of Project 1, the result should include:

SITE-204    Orin Valley North Block

SITE-205 should not exist yet.

That is intentional. Creating South Terrace Block is part of the project.

Check the customer contact

Run:

SELECT contact_id, first_name, last_name, contact_role
FROM customer_contacts
WHERE contact_id = 'CONT-102';

You should find Ana Solis with the role:

Field Operations Manager

Do not change it yet.

The support request in Project 1 will give you the updated information and ask you to correct the record.

Your environment is ready

If you can:

• connect to the server as av_it_admin,
• run USE atlas_ops;,
• see all 14 Atlas tables,
• confirm that SITE-205 does not exist yet,
• and confirm Ana Solis is still listed as Field Operations Manager,

then stop.

You are at the correct starting point.

From here on, setup is over. The next work happens inside Avenloch Intelligence, where you are supporting the Atlas Operations system.

Project 1: Support a New Survey Site in MySQL

You have MySQL running. Atlas is loaded. Now you can actually work with it.

This first project is deliberately the most guided of the four. The point is not to see how much SQL you already know. It is to get comfortable moving through a relational database while completing a believable support request.

By the end, you will have:

• traced a customer through related project and site records;
• used SELECT, WHERE, ORDER BY, and JOIN;
• inspected table structure before writing data;
• inserted a new site and collection mission;
• updated an existing customer-contact record;
• verified the finished work across multiple related tables.

If terms such as primary key, foreign key, or JOIN are still new, keep the Atlas Technical Primer & Naming Reference nearby. You do not need to memorize the database before starting.

Your support request

Avenloch's Operations team has received approval to expand an existing agricultural-monitoring project.

Maya Chen, the Operations Coordinator, sends the following request:

Atlas Support Request - Add South Terrace Block

Customer: Verdant Mesa Agriculture

Project: Orin Valley Crop Health Monitoring (PRJ-1060)

Verdant Mesa has approved a new crop-monitoring area called South Terrace Block.

Add the new site to Atlas using the following information:

• Reserved site ID: SITE-205
• Site name: South Terrace Block
• Latitude: -31.149870
• Longitude: -64.835220
• Site type: Agricultural Block
• Status: ACTIVE
• Location note: Newly approved crop-monitoring block.

Schedule the site's first collection mission:

• Reserved mission ID: MIS-310
• Date: October 3, 2026
• Collection: Multispectral Aerial Survey
• Operator: Daniel Rios
• Status: SCHEDULED

One customer-contact record also needs a correction. Ana Solis is now Director of Field Operations at Verdant Mesa Agriculture.

Verify the changes before closing the request.

That is all the business information you need.

Your job is to figure out how those facts fit into Atlas.

Start by looking around

A common beginner mistake is to start changing data before understanding what is already there.

Don't.

Start with the database itself:

USE atlas_ops;

Throughout these examples, the semicolon (;) marks the end of the SQL statement.

USE makes atlas_ops the current database for the statements that follow. MySQL keeps that database selected for the session until you choose another one.

MySQL 8.4 Reference Manual - USE Statement

Now list the tables:

SHOW TABLES;

SHOW TABLES does exactly what its name suggests: it lists the tables and views you can see in the selected database.

MySQL 8.4 Reference Manual - SHOW TABLES

Some of the names should be fairly self-explanatory even if this is your first database:

customers
customer_contacts
projects
sites
missions
datasets
findings
invoices
payments
employees

There are other tables in Atlas, but you do not need all of them for this ticket.

The important path for the main request is:

customer
   ↓
project
   ↓
site
   ↓
mission

The mission also points to an employee record for the operator.

employees
   ↓
missions.operator_employee_id

That relationship is what makes this a relational database. Instead of copying the full customer, project, site, and employee information into every mission record, Atlas stores identifiers that connect the records.

Atlas also uses foreign-key constraints to help keep those relationships valid. For example, a mission cannot reference a site ID that does not exist in sites. MySQL rejects an INSERT or UPDATE that would create a child foreign-key value without a matching parent record.

MySQL 8.4 Reference Manual - FOREIGN KEY Constraints

That is why the order of work matters later: create the site first, then create the mission that points to it.

Inspect a table before you use it

Run:

DESCRIBE sites;

Then:

DESCRIBE missions;

DESCRIBE shows information about a table's columns. MySQL documents DESCRIBE as a synonym for EXPLAIN; in practice, DESCRIBE table_name is commonly used to inspect table structure.

MySQL 8.4 Reference Manual - DESCRIBE / EXPLAIN

Look at the output rather than rushing past it.

For sites, you should see columns such as:

site_id
project_id
site_name
latitude
longitude
site_type
site_status
location_notes

For missions:

mission_id
site_id
mission_date
collection_type
operator_employee_id
mission_status

That already tells you something important.

The support request gave you business facts. The table structure tells you where those facts belong.

Find the customer before you touch the project

A SELECT statement retrieves data.

The basic pattern is:

SELECT columns_you_want
FROM table_name
WHERE condition;

MySQL uses the WHERE clause to keep only rows that satisfy the condition you give it.

MySQL 8.4 Reference Manual - SELECT Statement

Find Verdant Mesa:

SELECT customer_id,
       customer_name,
       industry,
       account_status
FROM customers
WHERE customer_name = 'Verdant Mesa Agriculture';

You should find:

CUST-002    Verdant Mesa Agriculture

Notice that we are selecting the columns we actually want instead of starting every query with SELECT *.

SELECT * is useful when you are quickly exploring a small table, but explicit columns make the purpose of a query easier to understand later.

Now find the customer's project:

SELECT project_id,
       customer_id,
       project_name,
       project_type,
       project_status
FROM projects
WHERE customer_id = 'CUST-002';

The relevant record is:

PRJ-1060    Orin Valley Crop Health Monitoring

You have now followed the first relationship manually:

CUST-002 → PRJ-1060

Use a JOIN to see the relationship directly

You do not normally need to query one table, copy an ID, query the next table, copy another ID, and repeat forever.

A JOIN lets one query combine related records from multiple tables.

For this query:

SELECT c.customer_name,
       p.project_id,
       p.project_name,
       p.project_status
FROM customers AS c
JOIN projects AS p
  ON p.customer_id = c.customer_id
WHERE c.customer_id = 'CUST-002';

c and p are aliases. They are simply shorter names for the customers and projects tables inside this query.

The important part is:

ON p.customer_id = c.customer_id

That tells MySQL how the two tables are related.

MySQL's documentation describes the ON clause as the condition used to specify how tables should be joined, while WHERE limits the rows included in the final result.

MySQL 8.4 Reference Manual - JOIN Clause

This is the mental model to keep:

customers.customer_id
        │
        └──── projects.customer_id

The matching identifier is the bridge between the records.

Check the existing sites

Before adding South Terrace Block, look at the sites already assigned to PRJ-1060:

SELECT site_id,
       site_name,
       latitude,
       longitude,
       site_type,
       site_status
FROM sites
WHERE project_id = 'PRJ-1060'
ORDER BY site_id;

ORDER BY site_id sorts the returned rows by site ID. The database does not need that clause to find the records, but it makes the result predictable and easier to compare as the project grows.

At this point in the project, you should see Orin Valley North Block and you should not see SITE-205.

This check matters for two reasons.

First, it confirms you are working in the correct project.

Second, it confirms that the record you are about to create does not already exist.

That is a good habit any time you are about to add or change data: read first, write second, verify afterward.

Add South Terrace Block

MySQL uses INSERT to add a new row to an existing table.

The form we need is:

INSERT INTO table_name
  (column_1, column_2, column_3)
VALUES
  (value_1, value_2, value_3);

MySQL 8.4 Reference Manual - INSERT Statement

For South Terrace Block:

INSERT INTO sites
  (
    site_id,
    project_id,
    site_name,
    latitude,
    longitude,
    site_type,
    site_status,
    location_notes
  )
VALUES
  (
    'SITE-205',
    'PRJ-1060',
    'South Terrace Block',
    -31.149870,
    -64.835220,
    'Agricultural Block',
    'ACTIVE',
    'Newly approved crop-monitoring block.'
  );

There are two details worth noticing.

SITE-205 is the site's primary key. It uniquely identifies this site record.

PRJ-1060 is a foreign key in the sites table. It connects the new site to the existing Orin Valley project.

That is why the record becomes part of the correct customer workflow without copying the customer name into sites.

Verify the insert immediately

Run:

SELECT site_id,
       project_id,
       site_name,
       latitude,
       longitude,
       site_status
FROM sites
WHERE site_id = 'SITE-205';

Do not move on until the values match the support request.

If Workbench reports that one row was affected and this SELECT returns SITE-205 with the expected values, the insert succeeded.

If you accidentally run the INSERT twice: MySQL should reject the second attempt because SITE-205 is a primary key and must remain unique. If you see a duplicate-key error for SITE-205, run the verification SELECT before doing anything else. The record may already be correct.

If the row is wrong, stop before creating the mission that depends on it. The goal is to understand and verify the state you have, not to keep issuing writes until an error disappears.

Find the collection operator

The support request gives you a person:

Daniel Rios

The missions table does not store his name. It stores:

operator_employee_id

So find his employee record:

SELECT employee_id,
       first_name,
       last_name,
       department,
       job_title
FROM employees
WHERE first_name = 'Daniel'
  AND last_name = 'Rios';

You should find:

EMP-004    Daniel Rios    Field Operations    Field Operations Lead

This is another relational pattern:

employees.employee_id
        │
        └──── missions.operator_employee_id

Atlas stores EMP-004 in the mission record rather than repeating Daniel's name and job title every time he operates a mission.

Schedule the first mission

You have already seen one complete INSERT.

Before reading the query below, see whether you can map the support-request values to the missions columns yourself:

mission_id
site_id
mission_date
collection_type
operator_employee_id
mission_status

There are two details to check before you write the row.

First, SITE-205 now exists, so the mission's site_id foreign key has a valid parent record.

Second, mission_date is a MySQL DATE column. MySQL displays DATE values in YYYY-MM-DD form, so October 3, 2026 is written as:

2026-10-03

MySQL 8.4 Reference Manual - DATE, DATETIME, and TIMESTAMP Types

The finished insert should look like this:

INSERT INTO missions
  (
    mission_id,
    site_id,
    mission_date,
    collection_type,
    operator_employee_id,
    mission_status
  )
VALUES
  (
    'MIS-310',
    'SITE-205',
    '2026-10-03',
    'Multispectral Aerial Survey',
    'EMP-004',
    'SCHEDULED'
  );

Now verify it:

SELECT mission_id,
       site_id,
       mission_date,
       collection_type,
       operator_employee_id,
       mission_status
FROM missions
WHERE mission_id = 'MIS-310';

At this point the relationship is:

Verdant Mesa Agriculture
        ↓
PRJ-1060
        ↓
SITE-205
        ↓
MIS-310
        ↓
EMP-004 / Daniel Rios

You have not created a disconnected site record and a disconnected mission record.

You have extended an existing business workflow and preserved the relationships around it.

Correct Ana Solis's role

The last part of the ticket is different.

Ana already exists in Atlas. You are not adding another contact. You are changing one value on the existing record.

Find her first:

SELECT cc.contact_id,
       c.customer_name,
       cc.first_name,
       cc.last_name,
       cc.business_email,
       cc.contact_role
FROM customer_contacts AS cc
JOIN customers AS c
  ON c.customer_id = cc.customer_id
WHERE cc.first_name = 'Ana'
  AND cc.last_name = 'Solis';

You should find CONT-102 at Verdant Mesa Agriculture with the existing role:

Field Operations Manager

MySQL uses UPDATE to modify existing rows.

A basic update looks like:

UPDATE table_name
SET column_name = new_value
WHERE condition;

MySQL 8.4 Reference Manual - UPDATE Statement

The WHERE clause is especially important in an UPDATE.

Without it, an update can affect every row in the table.

You already identified the exact record, so use its unique contact ID:

UPDATE customer_contacts
SET contact_role = 'Director of Field Operations'
WHERE contact_id = 'CONT-102';

Then verify it:

SELECT contact_id,
       first_name,
       last_name,
       contact_role
FROM customer_contacts
WHERE contact_id = 'CONT-102';

The final role should be:

Director of Field Operations

The pattern should look familiar now:

SELECT → change → SELECT again

That simple habit is worth keeping.

Close the ticket with one verification query

The individual records look right.

Now prove that the new site and mission connect correctly to the existing customer and project.

Run:

SELECT c.customer_name,
       p.project_name,
       s.site_name,
       m.mission_id,
       m.mission_date,
       e.first_name,
       e.last_name
FROM customers AS c
JOIN projects AS p
  ON p.customer_id = c.customer_id
JOIN sites AS s
  ON s.project_id = p.project_id
JOIN missions AS m
  ON m.site_id = s.site_id
JOIN employees AS e
  ON e.employee_id = m.operator_employee_id
WHERE s.site_id = 'SITE-205';

The result should connect the following values in one row:

Verdant Mesa Agriculture
Orin Valley Crop Health Monitoring
South Terrace Block
MIS-310
2026-10-03
Daniel
Rios

There is no new SQL trick hidden in this final query. It is the same JOIN ... ON pattern you already used, extended across more related tables.

This query is more useful than a screenshot of an isolated INSERT.

It demonstrates that you understand how the new record fits into the larger system.

Run the supplied verification file

Download the Project 1 verification file:

• avenloch-atlas-p1-03-verification.sql

SHA-256 checksum (how to check a download):

3b334880e1ae256e8bcbe89906109e648374378f2d5401b98e2d09e2d6fe8f8b  avenloch-atlas-p1-03-verification.sql

Run it after you complete the work.

It checks:

• SITE-205;
• MIS-310;
• Ana Solis's updated role;
• the customer → project → site → mission relationship.

The verification file is not a replacement for understanding your work. It is a final check that the supplied environment reached the expected state.

What you actually practiced

You did more here than "learn basic SQL."

You received a business request, found the relevant data model, identified existing records, created dependent records in the correct order, changed an existing record precisely, and verified the final state.

That is closer to the way SQL appears in many IT and security jobs.

You may not be hired to be the database administrator.

You may still need to understand enough SQL to investigate an application issue, verify a record, correct data, trace a relationship, or safely support a system that happens to use a relational database.

That is the point of this project.

Save evidence for your portfolio

You do not need twenty screenshots.

A compact Project 1 entry can show:

1. The business objective: One or two sentences explaining that you supported Avenloch's Atlas Operations system by adding a new monitoring site, scheduling its first collection mission, and correcting a customer-contact record.

2. One relational query: Use the final customer → project → site → mission → employee query. Capture the SQL and result together if possible.

3. One short technical explanation: Explain how primary and foreign keys allowed you to add the site and mission without duplicating the customer/project data.

4. Optional schema evidence: If you create or capture a simple entity-relationship diagram (ERD), include the portion showing customers → projects → sites → missions.

An isolated screenshot of a basic SELECT says relatively little on its own.

The stronger evidence is that you understand what the data represents, how the records relate, what changed, and how you verified the completed business request.

Before publishing screenshots, crop out anything from your own computer that you do not want public, such as local usernames, filesystem paths, saved connection names, or credentials. The Avenloch records are synthetic; your workstation details may not be.

Before Project 2

Project 1 was intentionally guided.

Project 2 will give you the database and the business evidence, but it will stop telling you exactly which query to run at every step.

The problem also changes.

Instead of updating Atlas, you will need to decide how the information inside it should be handled.

That means SQL becomes a tool for answering a business and security question rather than the end goal itself.

Project 2: Classify the Data Inside Atlas

Project 1 was mostly about learning how Atlas is put together.

Project 2 changes the question.

You are no longer being asked to add or correct a record. You are being asked to decide how existing information should be handled.

That requires SQL, but SQL is not the hard part.

The hard part is understanding what the data represents.

Data classification is about context and impact

A database column does not become sensitive because its name looks technical.

latitude is just a number.

The business context around that number is what matters.

Consider two unrelated examples:

• the latitude and longitude of a public conference venue;
• the latitude and longitude of a private equipment staging point whose exact access location is shared only with an assigned team.

The data type is the same.

The information does not have the same business meaning.

That is the idea you will work with throughout this project.

NIST's federal security-categorization guidance uses a different model than Avenloch's internal labels, but the underlying reasoning is useful here. FIPS 199 categorizes federal information and information systems according to the potential impact associated with confidentiality, integrity, and availability. Its impact model considers compromise through events such as unauthorized access or disclosure, modification or destruction, and disruption of access or use. NIST SP 800-60 provides guidance for mapping information types to those security categories.

• NIST FIPS 199 - Standards for Security Categorization
• NIST SP 800-60 Vol. 1 Rev. 1
• NIST Risk Management Framework - Categorize Step

Avenloch is not using NIST's federal LOW / MODERATE / HIGH impact levels in this exercise.

Its labels are a fictional internal information-handling scheme. They are driven primarily by who should receive the information and the consequences of inappropriate disclosure or access. Integrity and availability still matter when you think about business impact, but they do not turn Avenloch's labels into NIST security categories.

Avenloch uses:

PUBLIC
INTERNAL
CONFIDENTIAL
RESTRICTED

You will find the definitions and examples for each level in the Project 2 section of the Atlas Project Casebook.

The important part is the reasoning:

What is this information, who needs it, is it already public, and what could happen if it were disclosed, changed, or used by the wrong person?

Classification is not always a whole-table decision

Beginners sometimes imagine data classification as:

customers = confidential
sites = restricted
invoices = confidential

Real systems are not always that clean.

Atlas gives you a useful example.

The sites table contains the same kinds of fields for every location:

site_name
latitude
longitude
site_type
site_status
location_notes

But one row can describe an operating facility whose general location is already public, while another row can describe an unannounced development parcel or a controlled field-access point.

So your review can be more specific than:

The sites table is Confidential.

You may instead conclude that a particular record or set of fields requires a particular handling level because of what that record represents.

This is also why the Project 2 Casebook section identifies the Relevant Atlas data for each mapped site.

The map gives you geographic context.

The site description gives you business context.

The database gives you the actual record.

You need all three.

Load the Project 2 environment

Project 2 uses its own clean Atlas starting state.

Do not keep working in the database you changed during Project 1. The project seeds are independent so every learner starts each scenario from the same evidence.

Download the Project 2 files:

• avenloch-atlas-p2-01-setup.sql
• avenloch-atlas-p2-02-admin-grants.sql

SHA-256 checksums (how to check a download):

4781a8ffe2356ac5b919bf98365532eec1ef51d703f23ea40290762d2f622b62  avenloch-atlas-p2-01-setup.sql
cbb1bcce7c6bc07382447dff4b9be270413767c39ee63e2d3f9369d9dc31f2f2  avenloch-atlas-p2-02-admin-grants.sql

If you have not created the lab accounts yet, run avenloch-atlas-00-lab-accounts.sql from the Environment Setup files first.

Connect using the MySQL administrative account you created during environment setup and run:

avenloch-atlas-p2-01-setup.sql
avenloch-atlas-p2-02-admin-grants.sql

in that order.

The first script drops and recreates the synthetic atlas_ops training database with the Project 2 case state. The second restores the normal Avenloch lab-account permissions for that state.

Training environment: do not run the project setup scripts against a MySQL instance that contains real data you care about.

When both scripts finish, reconnect as av_it_admin and use that account for the classification queries in this project.

Your assignment

Nadia Mercer, Avenloch's IT & Security Manager, is reviewing how information stored in Atlas should be handled.

You have been asked to perform a classification review of representative Atlas records.

Use:

1. the Project 2 Atlas database;
2. the Project 2 section of the Atlas Project Casebook;
3. Avenloch's four-level handling standard inside that Casebook section.

For each required item, record:

• the data or record reviewed;
• the Avenloch classification;
• your reason;
• the handling you recommend.

You are not being asked to invent a new classification policy.

You are applying the supplied one.

You are also not expected to research the fictional customers or locations online. The database and case file are the complete evidence set for this project.

Required review set

Review these ten items:

That set is deliberate.

It includes:

• the same type of location data used in different business contexts;
• customer information;
• project operations;
• analytical output;
• financial information;
• personnel information;
• security-relevant account information.

You should be able to reach a defensible intended decision for each item using only the supplied evidence.

Keep the decision scope narrow

Classify the fields named in the review set, not every column that happens to appear in a query.

For example, the FND-504 query joins several tables so you can learn which customer and project the finding belongs to. Those joined customer/project fields are context for your decision. The item you are classifying is still the finding summary, priority, and review status named in the assignment.

The same rule applies to the invoice, project, account, and mapped-site queries.

This keeps the exercise focused and prevents a useful context field from accidentally becoming a second classification problem.

Start with the policy, not the SQL

Before querying Atlas, open the Project 2 Casebook section and read Exhibit 1 - Avenloch Data Handling Standard.

Do not try to memorize it.

Instead, turn it into a set of questions you can use while reviewing a record.

For each item, ask:

1. Has this information already been approved for public release?

A facility can exist in the real world without every detail about Avenloch's work at that facility being public.

Pay attention to exactly what the Project 2 Casebook section says is public.

2. Does the record reveal nonpublic business activity?

Examples could include:

• an unannounced development;
• an active project schedule;
• customer financial information;
• analytical findings that have not been released.

3. Does it expose an operational or security detail?

Ask whether the information identifies things such as:

• a controlled access point;
• a restricted operating location;
• account/access information;
• details intentionally limited to a specific team.

4. Who actually needs the information?

"Not public" does not automatically mean "Restricted."

A routine project-status field may be normal internal information used by many employees.

A field that reveals a controlled operating location may need much narrower access.

5. What could happen if the data were disclosed or changed?

Avenloch's handling labels in this project are primarily about disclosure and access, but do not ignore why the record matters to the business.

A customer finding can be sensitive because someone could read information that has not been released. It can also be important because an unauthorized change could affect a customer decision or deliverable.

That integrity distinction becomes much more important in Project 4.

Use SQL to establish what the record actually contains

Project 1 gave you the SQL patterns you need.

This project will not walk through every query one at a time.

You are expected to use SELECT, WHERE, and JOIN to retrieve the records you need.

If the SQL itself is slowing you down, use the following queries as starting points.

They retrieve evidence.

They do not tell you the classification.

Site and project context

For a mapped site:

SELECT s.site_id,
       s.site_name,
       s.latitude,
       s.longitude,
       s.site_type,
       s.site_status,
       s.location_notes,
       p.project_id,
       p.project_name,
       p.project_status,
       c.customer_name
FROM sites AS s
JOIN projects AS p
  ON p.project_id = s.project_id
JOIN customers AS c
  ON c.customer_id = p.customer_id
WHERE s.site_id = 'SITE-202';

Change the final site ID when you review another mapped location.

The result tells you what Atlas contains.

The Project 2 Casebook section tells you what those fields mean in the business.

Use the extra project/customer columns as context. Classify only the site fields named in the review set.

Do not classify the record from the SQL result alone.

Customer-contact context

For Ana Solis:

SELECT cc.contact_id,
       cc.first_name,
       cc.last_name,
       cc.business_email,
       cc.business_phone,
       cc.contact_role,
       c.customer_name
FROM customer_contacts AS cc
JOIN customers AS c
  ON c.customer_id = cc.customer_id
WHERE cc.contact_id = 'CONT-102';

Then compare the record with the Project 2 Casebook section's explanation of how Avenloch uses customer contacts.

Project context

For PRJ-1060:

SELECT project_id,
       project_name,
       project_type,
       start_date,
       target_delivery_date,
       project_status
FROM projects
WHERE project_id = 'PRJ-1060';

The question is not simply whether "project data is sensitive."

Ask what these particular fields reveal and how the Project 2 Casebook section says active project operations are handled.

Trace a finding back to the business work

FND-504 does not directly contain a project ID.

It belongs to a dataset.

The dataset belongs to a mission.

The mission belongs to a site.

The site belongs to a project.

The project belongs to a customer.

finding
   ↓
dataset
   ↓
mission
   ↓
site
   ↓
project
   ↓
customer

You can trace that relationship with a join:

SELECT f.finding_id,
       f.finding_type,
       f.finding_summary,
       f.priority,
       f.review_status,
       d.dataset_id,
       m.mission_id,
       s.site_id,
       s.site_name,
       p.project_id,
       p.project_name,
       c.customer_name
FROM findings AS f
JOIN datasets AS d
  ON d.dataset_id = f.dataset_id
JOIN missions AS m
  ON m.mission_id = d.mission_id
JOIN sites AS s
  ON s.site_id = m.site_id
JOIN projects AS p
  ON p.project_id = s.project_id
JOIN customers AS c
  ON c.customer_id = p.customer_id
WHERE f.finding_id = 'FND-504';

There is nothing special about the SQL here.

It is the same JOIN ... ON pattern from Project 1, extended far enough to answer the business question:

What customer and project does this finding belong to?

The customer, project, site, mission, and dataset columns are evidence that explains the finding's context. Your classification decision for this review item still applies to the finding summary, priority, and review status.

Financial context

For INV-603:

SELECT i.invoice_id,
       i.invoice_amount,
       i.issued_date,
       i.due_date,
       i.invoice_status,
       p.project_id,
       p.project_name,
       c.customer_name
FROM invoices AS i
JOIN projects AS p
  ON p.project_id = i.project_id
JOIN customers AS c
  ON c.customer_id = p.customer_id
WHERE i.invoice_id = 'INV-603';

You do not need to expose payment-card data to practice classifying commercial financial information.

Atlas does not contain card numbers or bank-routing numbers for this exercise.

Personnel and account context

Retrieve the employee record:

SELECT employee_id,
       first_name,
       last_name,
       department,
       job_title,
       employment_status
FROM employees
WHERE employee_id = 'EMP-004';

Then retrieve the Atlas application account:

SELECT atlas_user_id,
       contractor_id,
       username,
       account_type,
       atlas_role,
       account_status
FROM atlas_users
WHERE atlas_user_id = 'AUSR-008';

Remember the distinction from the technical primer:

employee/contractor record = information about a person and their work relationship.

Atlas account record = information about an identity used to access the Atlas application, including its account type, assigned Atlas role, and current status.

They may deserve different handling even though both can ultimately relate to a person. The Project 2 Casebook section tells you how Avenloch treats account/access information.

Write the classification review

The final two pages of the Project 2 section of the Atlas Project Casebook contain the Atlas Data Classification Review worksheet.

It already lists the ten assigned records and the exact Atlas fields you are evaluating. Complete the remaining columns:

• Classification
• Decision rationale
• Recommended handling

That keeps the finished work in the same professional document as the case evidence and gives you two clean, landscape pages that can be captured as portfolio screenshots.

The classification should be written as text:

PUBLIC
INTERNAL
CONFIDENTIAL
RESTRICTED

If you use the color system from the Project 2 Casebook section in the finished portfolio artifact, keep the written label too. Color should reinforce the decision, not replace it.

Your reason should connect the database to the business fact

Avoid explanations like:

Confidential because coordinates are sensitive.

That does not show much reasoning.

A stronger explanation has the form:

The Atlas fields identify a location associated with [business circumstance from the Project 2 Casebook section]. Because [relevant public/nonpublic/access fact], the record fits Avenloch's definition of [classification].

You are showing the connection between:

database value
      +
business context
      +
handling policy
      =
classification decision

Recommended handling should follow from the classification

You do not need to design a full data-loss-prevention program.

A short recommendation is enough.

Depending on your decision, examples of handling language might include:

• approved for public release;
• available to Avenloch personnel for normal business use;
• limited to personnel with a business need;
• shared only through approved internal/customer channels;
• restricted to the assigned project or security team;
• not included in public screenshots or portfolio evidence.

The recommendation should make sense for the classification you chose.

Watch for the main trap in this project

Do not classify the column name.

Classify the information represented by the record.

The strongest example is the mapped location data.

These records all contain:

latitude
longitude

The Project 2 Casebook section intentionally gives those coordinates different business circumstances.

If you assign the same classification to every coordinate simply because all coordinates are "location data," you have missed the point of the exercise.

The same caution applies in the other direction.

Do not automatically label every internal database field RESTRICTED simply because it is not public.

Classification should be proportional to the context and potential impact.

What this has to do with security

Classification is not an access-control mechanism by itself.

Writing:

RESTRICTED

next to a record does not technically stop anyone from querying it.

Classification tells the organization how information should be handled.

Controls such as:

• account privileges;
• application authorization;
• restricted views;
• sharing rules;
• encryption;
• logging;
• monitoring

are ways an organization can enforce or support those decisions.

That distinction matters because Project 3 starts with a very practical question:

If we know what someone should be allowed to access, does their database account actually match that requirement?

Project 2 gives you the policy reasoning.

Project 3 turns that reasoning into access control.

Save the result as a portfolio artifact

Your strongest evidence from this project is the Atlas Data Classification Review itself.

A useful portfolio version should show:

• the record or data type reviewed;
• the relevant Atlas fields;
• the classification you assigned;
• the business reason;
• the recommended handling.

You do not need to publish every database row you queried.

The finished review is stronger evidence because it shows that you can move from technical data to a documented security decision.

A short project summary might explain that you:

Reviewed representative customer, operational, financial, geospatial, personnel, and account data in a fictional MySQL-backed business system; correlated database records with business context; and applied an internal handling standard to document classification and handling recommendations.

Before publishing screenshots, keep the same rule from Project 1: crop out your own usernames, local file paths, saved connection names, or credentials.

The Avenloch environment is synthetic.

Your workstation may not be.

Before Project 3

Project 1 asked you to make a business change.

Project 2 asked you to make security decisions about existing information.

Project 3 gives you a narrower objective and less procedural guidance.

A contractor has direct MySQL access that does not match the work they actually need to perform.

Your job will be to inspect the current privileges, determine the legitimate requirement, correct the access, and prove that the account can still do its job without retaining unnecessary permissions.

Project 3: Fix Excessive Database Access

The first two projects were about understanding the system.

Now you are going to change who can access it.

A contractor has a legitimate reason to use part of the Atlas database. The problem is that the MySQL account created for that work can currently do far more than the job requires.

Your task is to:

1. establish the contractor's legitimate business need;
2. inspect the account's current MySQL privileges;
3. compare the current access with the approved requirement;
4. remove unnecessary privileges;
5. preserve the access the contractor actually needs;
6. prove both the allowed and denied behavior after the change.

This project gives you less procedural guidance than Projects 1 and 2.

The business requirement is clear.

You need to turn it into a correct technical access model.

Least privilege starts with the job

NIST describes least privilege as restricting a user or process to the minimum authorizations and resources needed to perform its function. NIST SP 800-53 Rev. 5 control AC-6 applies the same principle to organizational systems: allow only the authorized access necessary to accomplish assigned tasks.

• NIST SP 800-53 Rev. 5 Update 1 - Security and Privacy Controls
• NIST CSRC - Least Privilege

That sounds simple until a real account already has broad access.

Then you have to answer two separate questions:

What can this account do now?

and:

What does this person actually need to do?

The difference between those answers is the remediation scope.

Load the Project 3 environment

Each project uses its own clean Atlas starting state.

Do not continue from the database you changed in Project 1 or inspected in Project 2.

Download the Project 3 files:

• avenloch-atlas-p3-01-setup.sql
• avenloch-atlas-p3-02-admin-setup.sql
• avenloch-atlas-p3-03-validation-queries.sql (optional): the checks from the validation step later in this project, in one file. Its comments say which statements should succeed and which should fail after your remediation.

SHA-256 checksums (how to check a download):

8d21c624ad492bb6a9a3a6c52bdf0043ad491873488a4e8bc615414febf7a23e  avenloch-atlas-p3-01-setup.sql
58905b66ea201e20c3399ca1c15f9ee1dec5103036ea006e8a01ceec9095dec3  avenloch-atlas-p3-02-admin-setup.sql
1c59677c7d19dd24c083a528fa5212ff14029ca4fe9f9dad51ba8a8c50457aff  avenloch-atlas-p3-03-validation-queries.sql

If you have not created the lab accounts yet, run avenloch-atlas-00-lab-accounts.sql from the Environment Setup files first.

Training environment: avenloch-atlas-p3-01-setup.sql drops and recreates the synthetic atlas_ops database, and avenloch-atlas-p3-02-admin-setup.sql deletes and recreates the tibarra_ext MySQL account. Do not run the training scripts against a server that contains data or accounts you care about.

Connect using the MySQL administrative account you created during environment setup and run:

avenloch-atlas-p3-01-setup.sql
avenloch-atlas-p3-02-admin-setup.sql

Run them in that order.

The first script rebuilds atlas_ops with the Project 3 data and creates the approved quality assurance (QA) view.

The second creates the contractor's direct MySQL account and deliberately gives it excessive privileges so you have something real to investigate and fix.

When setup is complete, return to your av_it_admin connection for the business-side inspection.

Read the Project 3 case evidence

Open the Project 3 section of the Atlas Project Casebook.

The Project 3 Casebook section tells you:

• who Tomas Ibarra is;
• which Avenloch project he is assigned to;
• what work he needs to perform;
• the approved database-access requirement;
• which database object already exists to support that work.

This is important:

Do not design a new access requirement.

The business has already decided what Tomas needs.

Your job is to determine whether the database account matches that requirement and correct it if it does not.

Application account and database account are not the same thing

You saw AUSR-008 in Project 2.

That row represents Tomas's Atlas application identity:

atlas_users.username = tibarra

Project 3 is concerned with a different identity:

'tibarra_ext'@'localhost'

That is a direct MySQL account.

The two accounts are related to the same contractor, but they are not interchangeable.

Application permissions would be enforced by Atlas.

Direct MySQL permissions are enforced by the MySQL server itself.

MySQL account names contain both a username and a host component. In this lab, 'tibarra_ext'@'localhost' means the account is defined for a local connection to the MySQL server.

MySQL 8.4 Reference Manual - Specifying Account Names

This distinction matters because changing the atlas_users table would not change what the direct MySQL account is allowed to do.

For this project, the authorization you need to inspect is controlled with MySQL privileges.

Confirm the business scope

Use Atlas to verify Tomas's contractor record:

SELECT contractor_id,
       first_name,
       last_name,
       company_name,
       assigned_project_id,
       engagement_start,
       engagement_end,
       engagement_status
FROM contractors
WHERE contractor_id = 'CTR-001';

You should be able to establish that Tomas is assigned to:

PRJ-1071 - Aster Island Coastal Change Survey

You can also connect the contractor record to his Atlas application identity:

SELECT au.atlas_user_id,
       au.username,
       au.account_type,
       au.atlas_role,
       au.account_status,
       c.contractor_id,
       c.first_name,
       c.last_name,
       c.assigned_project_id
FROM atlas_users AS au
JOIN contractors AS c
  ON c.contractor_id = au.contractor_id
WHERE au.atlas_user_id = 'AUSR-008';

This gives you identity and assignment context.

It does not tell you the direct MySQL privileges.

For that, you need to inspect the MySQL account.

The approved QA view

Avenloch already created:

vw_aster_project_qa

A database view is a stored query that can be used like a virtual table.

The view in this project is intentionally limited to PRJ-1071 and exposes selected QA information about:

• the project;
• sites;
• missions;
• datasets;
• findings and review status.

It does not expose the entire Atlas schema.

MySQL supports granting privileges directly on a view. The project view is configured with SQL SECURITY DEFINER, which means the underlying-object privilege checks are performed using the view's definer when the view is invoked. A user can therefore be given SELECT on the view without also receiving direct SELECT access to every underlying table used by the view.

• MySQL 8.4 Reference Manual - Using Views
• MySQL 8.4 Reference Manual - CREATE VIEW and SQL SECURITY

You are not being asked to create or redesign this view.

The security task is to decide what privileges the direct contractor account should retain.

Inspect the contractor account from the contractor side

Create a new local Workbench connection for the synthetic training account:

Connection Name: Avenloch - Tomas Contractor DB
Hostname: localhost
Port: 3306
Username: tibarra_ext
Password: TibarraExt-LabOnly-2026!

The password is synthetic and exists only for this local training environment. The cleanup step at the end of this guide deletes the account.

Connect as tibarra_ext.

Confirm which MySQL identity you are using:

SELECT USER();

Now inspect the privileges assigned to the current account:

SHOW GRANTS;

MySQL documents SHOW GRANTS as the statement used to display privileges and roles assigned to a MySQL account. A user can display the grants for its own current account without needing the administrative access required to inspect another account.

MySQL 8.4 Reference Manual - SHOW GRANTS

Save this output.

It is your before-state evidence.

Read the privilege level carefully

A MySQL grant has both an action and a scope.

For example:

SELECT ON atlas_ops.some_object

means something very different from:

SELECT ON atlas_ops.*

The asterisk means the privilege applies across the database scope rather than to one named object.

Likewise:

INSERT
UPDATE
DELETE

are write privileges.

MySQL's own privilege guidance recommends granting an account only the privileges it needs.

MySQL 8.4 Reference Manual - Privileges Provided by MySQL

Compare what you found in SHOW GRANTS with the approved requirement in the Project 3 Casebook section.

Document the difference before you change anything.

Establish a safe before-state

You can test read access without changing data.

While connected as tibarra_ext, query the approved view:

SELECT project_id,
       project_name,
       site_id,
       site_name,
       finding_id,
       priority,
       review_status
FROM atlas_ops.vw_aster_project_qa
ORDER BY site_id, finding_id;

This access is legitimate.

Now test whether the contractor account can read something outside the approved QA scope:

SELECT payment_id,
       invoice_id,
       payment_amount,
       payment_status
FROM atlas_ops.payments
LIMIT 3;

Record what happens.

Do not prove excessive write access by changing a real customer, finding, invoice, or payment record.

SHOW GRANTS already tells you whether the account possesses write privileges.

A security test should not create the incident it is trying to prevent.

Plan the access change before you run it

At this point you should have three pieces of evidence:

Business requirement: What Tomas needs for the assigned work.

Current grants: What 'tibarra_ext'@'localhost' can actually do.

Approved database object: The existing view that provides the required QA data.

Write the desired after-state in your access-review matrix before running a privilege command.

A good access review should make it possible for another administrator to understand:

current access → business requirement → proposed access → validation

without reverse-engineering your SQL.

Use the administrative account for the privilege change

Disconnect from tibarra_ext.

For the next step, use the MySQL administrative account you created during setup.

This is one of the few places in the guide where using that account is intentional.

Your normal av_it_admin account has broad privileges within the atlas_ops training database, but it is not configured as a MySQL account administrator. Inspecting or changing another account's grants requires additional administrative privilege.

In a production environment, you would normally use a delegated administrative account with only the account-management authority required for the task rather than routinely operating as the MySQL root account.

For this local lab, the administrative account keeps the distinction visible:

business/database work → av_it_admin

MySQL account administration → administrative account

Inspect the named account again

As the administrative account, run:

SHOW GRANTS FOR 'tibarra_ext'@'localhost';

The output should match the before-state you captured while connected as the contractor.

GRANT and REVOKE

MySQL uses:

GRANT

to assign privileges and:

REVOKE

to remove them.

Their general form is:

GRANT privilege
ON database.object
TO 'user'@'host';

and:

REVOKE privilege
ON database.object
FROM 'user'@'host';

• MySQL 8.4 Reference Manual - GRANT
• MySQL 8.4 Reference Manual - REVOKE

MySQL permits privileges at different scopes, including an entire database or one table/view.

That is the technical mechanism you need.

The Project 3 Casebook section gives you the approved after-state.

Construct the privilege change yourself.

Do not drop tibarra_ext. The business requirement says the contractor still needs direct read-only QA access. The problem is excessive authorization, not the existence of the account.

When you finish, run:

SHOW GRANTS FOR 'tibarra_ext'@'localhost';

Save the new output as your after-state evidence.

Validate the result from the contractor account

Reconnect as:

tibarra_ext

Do not assume that a clean-looking SHOW GRANTS output is the end of the change.

Test behavior.

Required action: should succeed

Run the approved QA query again:

SELECT project_id,
       project_name,
       site_id,
       site_name,
       finding_id,
       priority,
       review_status
FROM atlas_ops.vw_aster_project_qa
ORDER BY site_id, finding_id;

If the remediation is correct, Tomas can still perform the approved QA work.

Unapproved read: should fail

SELECT *
FROM atlas_ops.payments
LIMIT 1;

A permission error is the expected security result.

This is not a broken query.

It is evidence that the access control is working.

Unapproved write: should fail safely

Use an UPDATE whose condition can never match a row:

UPDATE atlas_ops.findings
SET priority = 'LOW'
WHERE 1 = 0;

A correctly remediated account should receive a permission error.

1 = 0 is always false, so the statement cannot match or change a training record even if the account still has UPDATE.

This is useful for a privilege test because MySQL still has to authorize the UPDATE statement. MySQL documents that UPDATE privilege is required for columns that are actually being updated.

MySQL 8.4 Reference Manual - UPDATE Statement

You can use the same non-destructive pattern for customer data:

UPDATE atlas_ops.customers
SET account_status = 'INACTIVE'
WHERE 1 = 0;

Again, the expected result after correct remediation is a permission error.

A denied action is part of the evidence

Projects often show only successful commands.

For access control, a controlled failure can be just as important.

Your evidence should demonstrate both sides:

Allowed:
SELECT from the approved QA view

Denied:
SELECT from unrelated financial data
UPDATE a protected base table

That proves the account was not simply disabled.

It was narrowed to the work it actually needs to perform.

Complete the access change record

The Project 3 section of the Atlas Project Casebook includes two portfolio-ready pieces:

Access review matrix

Use it to record:

• the resource or action;
• the legitimate business requirement;
• the before state;
• the intended/implemented after state;
• the validation result.

Database access change record

Document:

• the account reviewed;
• the reason for the review;
• the excessive access identified;
• the privilege change you implemented;
• evidence used to validate the final state;
• any relevant note about the approved QA view.

The goal is not to produce a pretend enterprise change-management form with twenty empty fields.

It is to leave enough evidence that another technical person can understand what changed and why.

What you actually practiced

This project is not primarily about memorizing GRANT and REVOKE.

You:

• separated an application identity from a direct database account;
• translated a job requirement into an access requirement;
• inspected the actual privileges assigned to a MySQL account;
• recognized the difference between database-wide and object-specific scope;
• used a restricted view as the approved access boundary;
• removed unnecessary read/write authorization;
• validated both permitted and prohibited behavior;
• documented the before state, change, and after state.

That is an access-control exercise.

SQL is simply where you enforced it.

Save the evidence for your portfolio

A strong Project 3 entry can be compact.

Use:

1. A short business summary: Explain that a contractor assigned to one project had direct MySQL permissions broader than the approved QA requirement.

2. Before/after SHOW GRANTS evidence: This demonstrates the actual privilege change.

3. The completed access-review matrix: Show how the business need maps to the technical permissions.

4. Validation evidence: Include the successful query against vw_aster_project_qa and at least one permission-denied result for an unapproved object or action.

5. A short explanation of the view: Explain why granting read-only access to a restricted view is different from granting read access to the entire atlas_ops database.

Before publishing screenshots, remove real workstation usernames, paths, connection details, or credentials from view.

The Avenloch account is synthetic.

Your local environment may not be.

Before Project 4

Project 3 began with an access problem and ended with a controlled permission change.

Project 4 starts differently.

Something has already happened.

Atlas contains suspicious changes across operational, analytical, and financial records.

You will not be given a single SQL command that explains the incident.

You will have to preserve the current state, examine the available evidence, reconstruct what changed, distinguish account evidence from human attribution, recover known-good values, and document what you can actually support.

That is where the projects stop feeling like guided SQL exercises and start feeling like an investigation.

Project 4: Investigate Suspicious Database Changes

The previous projects started with a known task.

Project 4 starts with a problem.

Avenloch has conflicting information inside Atlas. An invoice appears paid when Finance has no matching payment. A field-station location no longer matches Operations' records. IT & Security needs to know whether those discrepancies are isolated mistakes or part of a larger database-integrity incident.

You are not being given the affected-record list.

You need to build it.

This project is intentionally the least guided in the series. By now you have already practiced:

• navigating a relational schema;
• tracing records through joins;
• understanding the business meaning of data;
• inspecting MySQL privileges;
• distinguishing application identities from direct database accounts.

Now those skills become investigative tools.

Your job is to preserve the incident state, reconstruct what changed, determine what the evidence actually supports, contain inappropriate access, recover known-good data, and document the result.

Incident response is more than finding the bad query

NIST finalized SP 800-61 Rev. 3 in April 2025, replacing the older Rev. 2 incident-handling guide. The current guidance treats incident response as part of broader cybersecurity risk management and emphasizes preparation, detection, response, recovery, and improvement across the Cybersecurity Framework.

NIST SP 800-61 Rev. 3 - Incident Response Recommendations and Considerations

For this project, you can turn that into a practical database workflow:

preserve
   ↓
scope
   ↓
correlate
   ↓
assess
   ↓
contain
   ↓
recover
   ↓
validate
   ↓
report

That is not meant to replace an organization's incident-response plan.

It is a useful sequence for this lab.

NIST's forensic guidance also makes an important point: evidence collection and examination should support incident response without unnecessarily changing the evidence you are trying to understand.

NIST SP 800-86 - Guide to Integrating Forensic Techniques into Incident Response

So the first rule of this project is simple:

Do not start by fixing the database.

Preserve the state you received first.

Load the Project 4 incident state

Project 4 is independent from Project 3.

Download the Project 4 files:

• avenloch-atlas-p4-01-setup.sql
• avenloch-atlas-p4-02-admin-setup.sql
• avenloch-atlas-p4-03-investigation-starter.sql (optional): orientation queries for the start of the investigation. They are a starting point, not an answer key.

SHA-256 checksums (how to check a download):

87073560e60572134853224b321908f7e42a65534a0c91af77440cdc17aba945  avenloch-atlas-p4-01-setup.sql
fd9a3aaeea598deab6dcbf8f79c936c68ed76b7f9f57a0069f1b24cd428b5348  avenloch-atlas-p4-02-admin-setup.sql
20d32d284d2b06dcf461447f6d76ad55caf6110c56507f7dba0025980a3e9dfd  avenloch-atlas-p4-03-investigation-starter.sql

If you have not created the lab accounts yet, run avenloch-atlas-00-lab-accounts.sql from the Environment Setup files first.

Training environment: run these files only on the dedicated lab server.

• avenloch-atlas-p4-01-setup.sql drops and recreates two databases: atlas_ops and atlas_preincident, a second database that only Project 4 uses.
• avenloch-atlas-p4-02-admin-setup.sql deletes and recreates the tibarra_ext MySQL account.

Do not run them against a server that contains data or accounts you care about. The cleanup step at the end of this guide removes both databases.

Connect with your MySQL administrative account and run:

avenloch-atlas-p4-01-setup.sql
avenloch-atlas-p4-02-admin-setup.sql

in that order.

The setup creates two schemas:

atlas_ops
atlas_preincident

They have different purposes.

atlas_ops is the incident state you have been asked to investigate.

atlas_preincident is a known-good logical reference representing Avenloch's backup from:

September 10, 2026 at 18:00 Coordinated Universal Time (UTC)

The reference schema is not something you should "clean up."

Your av_it_admin account has read access to it so you can compare records without using it as a working database.

The incident-state setup also recreates the direct contractor access that existed when the suspicious changes occurred.

Read the incident Casebook section before querying

Open the Project 4 section of the Atlas Project Casebook.

It contains:

• the initial incident notification;
• the evidence inventory;
• the backup/reference timestamp;
• Finance and Operations notes;
• the scope and limitations of the supplied database audit;
• the incident-report worksheets.

Treat those materials and the two supplied schemas as the complete evidence set for the exercise.

Do not research the fictional customer, contractor, locations, or project online.

Preserve the current database before you investigate

A database investigation is awkward because simply "looking around" can eventually turn into remediation, and remediation changes the state you received.

For this lab, create a logical dump of atlas_ops before making any corrective changes.

Open your operating-system terminal, not a Workbench SQL tab.

mysqldump is installed with the server, but the installers do not always put it on your PATH. On Windows, the MSI installs it in C:\Program Files\MySQL\MySQL Server 8.4\bin; on macOS, the DMG installs it in /usr/local/mysql/bin. If your terminal reports that mysqldump is not found, use its full path, as in the PowerShell example below. On Ubuntu or Debian, the APT packages put it on your PATH.

Run:

mysqldump \
  --single-transaction \
  --no-tablespaces \
  --triggers \
  -u av_it_admin \
  -p \
  --result-file=atlas_ops_incident_preserved.sql \
  atlas_ops

On Windows PowerShell, the same command can be written on one line. The & operator runs the program from its full path:

& "C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqldump.exe" --single-transaction --no-tablespaces --triggers -u av_it_admin -p --result-file=atlas_ops_incident_preserved.sql atlas_ops

--result-file makes mysqldump write the file itself instead of passing the output through your shell. MySQL recommends it on Windows, and it matters for the hash you record next: Windows PowerShell 5.1 redirection (>) would save the dump as UTF-16 text, so the file you hash would not be the bytes mysqldump produced.

MySQL documents mysqldump as a logical-backup utility. A dump can also be used as a copy for analysis or experimentation without changing the original database.

• MySQL 8.4 - Using mysqldump for Backups
• MySQL 8.4 - Database Backup Methods

--single-transaction is appropriate for the InnoDB tables used in this lab and avoids holding table locks throughout the dump.

This is a logical database preservation step, not a full forensic acquisition of the host.

In a real incident, the evidence you preserve would depend on what happened and could include server logs, binary logs, host artifacts, application logs, memory, storage snapshots, or other sources.

Hash the preserved file

After the dump is created, calculate a SHA-256 hash and record it in your notes.

Linux:

sha256sum atlas_ops_incident_preserved.sql

macOS:

shasum -a 256 atlas_ops_incident_preserved.sql

Windows PowerShell:

Get-FileHash .\atlas_ops_incident_preserved.sql -Algorithm SHA256

The hash does not prove that the database was trustworthy when you acquired it.

It gives you a way to demonstrate that the dump file you preserved has not changed since you hashed it.

Record:

file name
acquisition date/time
SHA-256
purpose

in the incident report.

Start with the audit trail

Connect as av_it_admin and select the incident-state database:

USE atlas_ops;

The training environment contains:

db_change_audit

This is a supplied audit table populated by database triggers.

You did not build those triggers, and you do not need to reverse-engineer them to complete the investigation.

Start by reviewing the available events:

SELECT audit_id,
       event_timestamp,
       mysql_account,
       action_type,
       object_table,
       record_id,
       event_summary
FROM db_change_audit
ORDER BY event_timestamp;

Do not assume every row represents an incident.

The table contains ordinary administrative/application activity as well as the activity you are investigating.

Look for patterns:

• unusual account;
• unusual time;
• several changes close together;
• multiple business areas affected;
• records associated with the same project;
• a write pattern inconsistent with the account's business purpose.

Build your timeline from evidence rather than from intuition.

Understand what the audit account field proves

The mysql_account value matters, but its meaning needs to be precise.

The supplied triggers record:

USER()

when selected records are updated.

MySQL documents an important difference inside triggers: CURRENT_USER() identifies the trigger's definer, while USER() or SESSION_USER() returns the invoking client account.

MySQL 8.4 - Information Functions

That means an audit value such as:

some_account@localhost

supports the conclusion:

The authenticated MySQL client account some_account@localhost performed the change.

It does not by itself prove:

The human whose name is associated with that account personally performed the change.

Credentials can be shared, stolen, misused, scripted, or used from a session someone else controls.

Keep that distinction in your final report.

The audit is useful, but it is not omniscient

This training audit records selected updates to:

sites
findings
invoices

It is not a complete record of every SQL statement ever executed.

MySQL has several server logs with different purposes. For example, the general query log can record connections and statements received from clients, while the binary log records data-changing events and is also used for replication/recovery. Those logs are separate from this lab's trigger-generated evidence.

MySQL 8.4 - MySQL Server Logs

Do not claim evidence you were not given.

Scope the records that need investigation

Once you identify events that appear related, write down:

timestamp
MySQL account
table
record ID
what the audit says changed

Do not begin recovery yet.

Use the record IDs to retrieve the current incident state.

Then decide which records need comparison with the known-good reference.

A useful pattern is:

SELECT 'incident' AS source,
       <fields_you_need>
FROM atlas_ops.<table>
WHERE <id_column> = '<record_id>'

UNION ALL

SELECT 'preincident' AS source,
       <same_fields>
FROM atlas_preincident.<table>
WHERE <id_column> = '<record_id>';

The point is not the UNION ALL itself.

The point is to put:

what Atlas contains now

next to:

what the known-good reference contained

for the same record.

Your report should be able to identify the exact fields that differ.

Correlate the database change with the business context

A changed value becomes meaningful when you understand what the record does.

Use Atlas relationships to answer questions such as:

• Which project owns this site?
• Which dataset does this finding belong to?
• Which customer would receive the associated deliverable?
• Does an invoice have a corresponding payment?
• Would changing this value affect operations, analysis, or finance?

This is where Projects 1 and 2 come back.

You are no longer tracing relationships just to prove you can join tables.

You are using those relationships to assess impact.

Financial reconciliation is a good example

The Project 4 Casebook section tells you Finance found a discrepancy.

Do not stop at the invoice's status field.

Check the payment relationship:

project
  ↓
invoice
  ↓
payment

An invoice marked PAID and a matching cleared payment record support each other.

An invoice marked PAID with no corresponding payment is a different situation.

Document both the database state and the Finance note.

Check whether the account had the ability to make the changes

An audit event tells you what account the trigger observed.

It does not tell you whether that account should have had that permission.

You already learned how to inspect database authorization in Project 3.

Use the MySQL administrative account to inspect the direct account associated with the suspicious activity:

SHOW GRANTS FOR '<user>'@'<host>';

Compare the observed privileges with the person's assigned business role in Atlas.

This is not the same question as:

Did that person do it?

It is:

Did the account have technical authorization broad enough to perform the observed changes?

That is an access-control finding.

Record it separately from human attribution.

Contain the account after you preserve the evidence

Once you have:

• preserved the incident state;
• captured the relevant audit events;
• recorded the account's current grants;

you have enough evidence to perform temporary containment in this lab.

Use the MySQL administrative account.

MySQL supports locking an account so new connections are denied.

A general form is:

ALTER USER '<user>'@'<host>' ACCOUNT LOCK;

MySQL's connection-verification process checks whether the matched account is locked; a locked account is denied access.

MySQL 8.4 - Access Control, Connection Verification

Record the containment action and time.

Do not delete the account.

The investigation still needs an auditable identity and privilege history, and the business may later decide whether the contractor's legitimate access should be re-provisioned.

If access is eventually restored, Project 3 already showed the appropriate model: only the approved QA capability should be re-granted.

Recover only what the evidence supports

Do not restore the entire atlas_preincident schema over atlas_ops.

A full rollback can destroy legitimate changes that occurred after the backup.

Instead:

1. identify an affected record from the incident evidence;
2. compare it with the known-good reference;
3. determine which fields changed;
4. restore only the supported fields;
5. verify the result.

One generic way to recover a field from the reference schema is:

UPDATE atlas_ops.<table> AS live
JOIN atlas_preincident.<table> AS ref
  ON ref.<id_column> = live.<id_column>
SET live.<field> = ref.<field>
WHERE live.<id_column> = '<record_id>';

Adapt the statement to the actual record and fields you determined were affected.

Do not copy this blindly across an entire table.

Expect recovery activity to create new audit rows

The incident-state schema still has the supplied audit triggers.

If your recovery changes one of the monitored fields, the trigger should create a new audit event using:

your account
current UTC time

That is useful.

It creates a record of the remediation.

Because you preserved the original incident state before recovery, you can distinguish:

incident evidence

from:

your response activity

Validate the recovered state

After the corrective changes, prove the result.

At minimum:

• compare each recovered record against atlas_preincident;
• confirm the financial relationship is internally consistent;
• confirm the suspicious direct account is contained;
• review the new audit entries created by your remediation;
• make sure unrelated records were not changed.

Do not write:

Everything is fixed.

Show the queries or records that support the conclusion.

Write the incident report

The Project 4 Casebook section contains the Atlas Database Incident Report.

Your report should separate several concepts that are easy to blur together.

What happened

Describe the suspicious database changes and the affected business areas.

Scope

Identify the records and fields for which you found evidence of unauthorized or unsupported modification.

Do not expand the scope beyond the evidence.

Timeline

Build the sequence from the database audit and Casebook notes.

Use UTC consistently.

Account evidence

State which MySQL account is associated with the suspicious updates.

Attribution limitation

State explicitly that account evidence does not prove the named human personally performed the actions.

Access-control finding

Document whether the account had privileges broader than its legitimate business requirement.

Impact

Explain what the changes could affect.

Think across the business:

operational integrity
analytical/customer-deliverable integrity
financial integrity

Containment

Document what you did to stop further use of the suspicious direct account.

Recovery

Document which records/fields were restored and the known-good source used.

Validation

Show how you proved the recovered state.

Next steps

Recommendations can include:

• keep the account locked pending review;
• re-provision only least-privilege QA access if the business still requires it;
• review credential ownership and handling;
• expand database auditing where justified;
• review other systems for evidence not present in this lab;
• document lessons learned and improve controls.

Do not invent evidence about malware, credential theft, intent, or a human actor.

If the evidence does not answer a question, say that.

That is part of incident analysis.

What you actually practiced

Project 4 pulls the earlier projects together.

You:

• preserved a database incident state before remediation;
• hashed the preserved artifact;
• distinguished live data from a known-good reference;
• interpreted a database audit trail;
• built an event timeline;
• correlated operational, analytical, and financial records;
• inspected the authorization of an account associated with suspicious changes;
• separated account evidence from human attribution;
• contained direct database access;
• performed targeted recovery rather than a blind rollback;
• validated the repaired state;
• documented evidentiary limitations.

That is much closer to incident-response work than simply finding a deliberately bad row.

Save the evidence for your portfolio

The strongest portfolio artifact is the completed Atlas Database Incident Report in the Casebook.

Supporting screenshots can include:

• the suspicious audit cluster;
• one incident/pre-incident record comparison;
• the relevant SHOW GRANTS result;
• a post-recovery comparison;
• the containment/validation result.

Avoid publishing the synthetic training passwords even though they are not real credentials.

Also remove real local usernames, filesystem paths, hostnames, or connection details from your screenshots.

The case is fictional.

Your workstation is not.

You now have four different kinds of database evidence

Across these projects, the database was never the job title.

It was a system you had to understand and support.

You:

1. supported business operations by adding and validating related records;
2. classified information by connecting database fields to business context;
3. enforced least privilege by aligning a direct account with an approved access requirement;
4. investigated an integrity incident by preserving, correlating, containing, recovering, and reporting.

That range is what makes the set useful for an IT or cybersecurity portfolio rather than another collection of disconnected SQL exercises.