Build an AWS IAM Compliance Engine

Use SQL to turn AWS IAM evidence into prioritized compliance findings.

Introduction

30 Second Summary

A wide spreadsheet can hide one risky detail among dozens of similar columns. Reviewers need a clear way to find the issues that deserve attention first.

In this project, you will build a local SQL engine in DuckDB that turns synthetic AWS IAM credential evidence into a prioritized remediation queue. The finished workflow shows why complete evidence modeling matters from the first finding through the final retest.

What You'll Build

Run the finished workflow to watch four prioritized IAM findings fall to zero after remediation.

By the end of this project, you'll have:

  • A repeatable SQL workflow that turns six rows of AWS-shaped credential evidence into findings you can rerun.
  • A complete control queue that catches a Critical root key, a user without MFA, and old access keys stored in either key slot.
  • A sanitized GitHub repository you can share as evidence of your workflow. Its exports show four findings before remediation and zero afterward.
  • Secret Mission: Detect active credentials that have not been used for 90 days.

Are there any prerequisites?

Intermediate SQL familiarity is the only knowledge prerequisite. Synthetic evidence keeps your AWS account untouched.

Before We Start

Before any hands-on work begins, commit to the purpose of your local AWS IAM evidence project. Your SQL-based compliance triage engine will transform synthetic credential-report evidence into proof that prioritized findings can be remediated without live AWS changes.

Set Up the Local Audit Workspace

Your compliance engine needs a dependable place to run SQL against synthetic AWS IAM evidence. Keeping that work local removes cloud access from the workflow.

A lightweight DuckDB database keeps your attention on evidence analysis. This step prepares the folders that hold your data, queries, and exports.

In this step, get ready to:
  • Install DuckDB CLI 1.5.5 through Windows PowerShell.
  • Create the local project workspace in Visual Studio Code.
  • Create a persistent database that returns a ready status.
Install DuckDB CLI

The DuckDB CLI gives you a local SQL engine without an application server. First, you will check whether the required release is already available.

Why DuckDB for this project?

DuckDB can query CSV evidence directly with analytical SQL. This keeps the control-testing workflow close to the source data.

The standard SQLite CSV workflow requires more manual import setup. DuckDB leaves more project time for evidence modeling.

  • Press the Windows key to open Windows search.
  • Type Windows PowerShell and press Enter.

You will see a Windows PowerShell window where the DuckDB command can run.

  • Check whether DuckDB is already available by running this command:
duckdb -version

What does this command do?

The -version argument asks the DuckDB CLI to print its installed release. The result determines which setup path you need.

✔️ I see the required version

You are set. The output contains v1.5.5, so the required DuckDB CLI is ready.

ⓧ I see an older version

Your installed DuckDB release needs an upgrade before the project begins. Windows Package Manager can target the existing DuckDB package.

  • Approve any confirmation prompt that Windows Package Manager displays.
  • Upgrade DuckDB by running this command:
winget upgrade --id DuckDB.cli

What does this command do?

The command targets the DuckDB.cli package by its identifier. Windows Package Manager installs the latest available upgrade.

  • Confirm the upgraded version by running this command:
duckdb -version

What should I see?

The output should now contain v1.5.5.

ⓧ The command is unavailable

DuckDB is not installed yet. Windows Package Manager can add the current CLI to your computer.

  • Approve any confirmation prompt that Windows Package Manager displays.
  • Install DuckDB by running this command:
winget install DuckDB.cli

What does this command do?

The command downloads the official DuckDB.cli package. Windows Package Manager then makes the CLI available on your computer.

  • Confirm the installed version by running this command:
duckdb -version

What should I see?

The output should contain v1.5.5. That confirms the installation succeeded.

Installation still failing?

  • Install the Microsoft Visual C++ Redistributable from Microsoft if Windows reports a missing runtime dependency.
  • Restart Windows PowerShell after installation so the refreshed session can find DuckDB.

Still stuck? Help me diagnose my DuckDB installation. You can also share the non-sensitive error text in the NextWork community.

Create the audit workspace

The project root keeps raw evidence separate from SQL source files. A dedicated output folder gives every generated finding a predictable destination.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code and press Enter.

You will see the Visual Studio Code window.

  • Select File from the top menu.
  • Select Open Folder....

You will see the Windows folder picker.

  • Select your Desktop.
  • Select New Folder.

The folder picker is ready for your project name.

  • Enter aws-iam-compliance-triage as the folder name.
  • Select Select Folder.
  • Select Yes, I trust the authors if the Workspace Trust dialog appears.

The Explorer now shows aws-iam-compliance-triage as the open project root.

  • Create data inside the project root with the New Folder button in the Explorer toolbar.
  • Create sql inside the project root with the New Folder button in the Explorer toolbar.

You should now see data and sql beneath the project root.

  • Create output inside the project root with the New Folder button in the Explorer toolbar.

The Explorer should list data, sql, and output inside aws-iam-compliance-triage.

Create the database checkpoint

A persistent database file carries tables and views across later scripts. The first query creates that file while proving the CLI can execute SQL from the project root.

  • Right-click aws-iam-compliance-triage in the Explorer.
  • Select Open in Integrated Terminal.
  • Select PowerShell from the terminal profile menu if another shell opens.

The terminal starts inside the open project root. Its prompt should end with aws-iam-compliance-triage.

  • Create the persistent database and print its ready status by running this command:
duckdb compliance.duckdb -c "SELECT 'ready' AS status;"

What does this command do?

  • The compliance.duckdb argument names the persistent local database file.
  • The -c argument runs one SQL command before the CLI exits.
  • The query returns ready under the status column to prove SQL execution works.

You will see one row containing ready. The Explorer will also show compliance.duckdb beside your three folders.

Do you not see the ready row?

  • Confirm the terminal prompt ends with aws-iam-compliance-triage before rerunning the command.
  • Check that the command uses double quotes around the SQL query.

Still stuck? Help me fix my DuckDB ready-status command.

Before the final check, which DuckDB version do you expect the terminal to report?

  • Verify the installed DuckDB version by running this command:
duckdb -version

What should I see?

The output contains v1.5.5. This confirms the required DuckDB CLI is available from PowerShell.

That foundation is ready. Your project now has a working SQL engine, an organized workspace, and a persistent local database.

Your local audit workspace can now run repeatable SQL checks. Next, you will load synthetic IAM evidence and surface the first compliance finding.

Turn Raw IAM Evidence into a First Finding

Your local database is ready to accept evidence. The fastest way to prove this workflow has value is to turn one raw record into a visible finding.

In this step, you'll load a synthetic AWS IAM credential report with DuckDB. You'll use SQL to identify the console user whose MFA setting fails IAM.5.

In this step, get ready to:
  • Create a synthetic IAM credential report with six evidence records.
  • Load the evidence as text on a fixed analysis date.
  • Run the missing-MFA control to inspect the finding for alice.
Create the synthetic credential report

An AWS IAM credential report is a CSV record of identity credential settings. This synthetic report preserves the AWS-shaped fields needed for local control testing.

Why use synthetic evidence?

Real credential reports contain account-specific security information. Synthetic identities let you publish the finished project without exposing company evidence.

The local workflow also avoids AWS permissions. It creates no AWS resources.

Values such as N/A preserve unavailable evidence. The not_supported value records fields that do not apply to the root account.

  • Create data/iam_credential_report_before.csv by using the new-file control in Visual Studio Code's file tree.
  • Fill data/iam_credential_report_before.csv by pasting this dataset:
user,arn,user_creation_time,password_enabled,password_last_used,password_last_changed,password_next_rotation,mfa_active,access_key_1_active,access_key_1_last_rotated,access_key_1_last_used_date,access_key_1_last_used_region,access_key_1_last_used_service,access_key_2_active,access_key_2_last_rotated,access_key_2_last_used_date,access_key_2_last_used_region,access_key_2_last_used_service,cert_1_active,cert_1_last_rotated,cert_2_active,cert_2_last_rotated,additional_credentials_info
root_account,arn:aws:iam::123456789012:root,2022-01-01T00:00:00+00:00,true,2026-09-01T08:00:00+00:00,not_supported,not_supported,true,true,2024-01-10T00:00:00+00:00,2026-09-01T08:05:00+00:00,us-east-1,iam,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
alice,arn:aws:iam::123456789012:user/alice,2025-05-10T00:00:00+00:00,true,2026-09-10T14:00:00+00:00,2026-06-01T00:00:00+00:00,N/A,false,false,N/A,N/A,N/A,N/A,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
bob,arn:aws:iam::123456789012:user/bob,2024-03-12T00:00:00+00:00,false,N/A,N/A,N/A,false,false,N/A,N/A,N/A,N/A,true,2026-01-15T00:00:00+00:00,2026-08-20T16:30:00+00:00,us-east-1,s3,false,N/A,false,N/A,N/A
carol,arn:aws:iam::123456789012:user/carol,2024-07-20T00:00:00+00:00,true,2026-09-18T09:00:00+00:00,2026-08-01T00:00:00+00:00,N/A,true,true,2026-08-15T00:00:00+00:00,2026-09-18T09:05:00+00:00,us-west-2,ec2,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
 dave,arn:aws:iam::123456789012:user/dave,2023-11-01T00:00:00+00:00,false,N/A,N/A,N/A,false,true,2025-12-01T00:00:00+00:00,2026-09-10T11:00:00+00:00,eu-west-1,sts,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
erin,arn:aws:iam::123456789012:user/erin,2025-02-14T00:00:00+00:00,false,N/A,N/A,N/A,false,true,2026-08-01T00:00:00+00:00,2026-01-10T10:00:00+00:00,us-east-1,iam,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A

What does this dataset contain?

  • The header preserves the AWS credential-report field names used by the SQL engine.
  • The six records cover root_account through erin.
  • The identities use a documentation-style account number for a safe local exercise.
  • Save data/iam_credential_report_before.csv.
  • Scroll to the bottom of the CSV file.

You should see one header row followed by six evidence records. The final record starts with erin.

Does the CSV structure look uneven?

  • Confirm that the header begins with user,arn,user_creation_time.
  • Confirm that every evidence record occupies one line.
  • Remove any blank line above the header.

Still stuck? Ask for help checking the CSV structure.

✔️ Awesome, I've got everything!

Great. Confirm that data/iam_credential_report_before.csv is saved with one header row and six evidence records.

ⓧ I'd like to double check the full code

user,arn,user_creation_time,password_enabled,password_last_used,password_last_changed,password_next_rotation,mfa_active,access_key_1_active,access_key_1_last_rotated,access_key_1_last_used_date,access_key_1_last_used_region,access_key_1_last_used_service,access_key_2_active,access_key_2_last_rotated,access_key_2_last_used_date,access_key_2_last_used_region,access_key_2_last_used_service,cert_1_active,cert_1_last_rotated,cert_2_active,cert_2_last_rotated,additional_credentials_info
root_account,arn:aws:iam::123456789012:root,2022-01-01T00:00:00+00:00,true,2026-09-01T08:00:00+00:00,not_supported,not_supported,true,true,2024-01-10T00:00:00+00:00,2026-09-01T08:05:00+00:00,us-east-1,iam,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
alice,arn:aws:iam::123456789012:user/alice,2025-05-10T00:00:00+00:00,true,2026-09-10T14:00:00+00:00,2026-06-01T00:00:00+00:00,N/A,false,false,N/A,N/A,N/A,N/A,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
bob,arn:aws:iam::123456789012:user/bob,2024-03-12T00:00:00+00:00,false,N/A,N/A,N/A,false,false,N/A,N/A,N/A,N/A,true,2026-01-15T00:00:00+00:00,2026-08-20T16:30:00+00:00,us-east-1,s3,false,N/A,false,N/A,N/A
carol,arn:aws:iam::123456789012:user/carol,2024-07-20T00:00:00+00:00,true,2026-09-18T09:00:00+00:00,2026-08-01T00:00:00+00:00,N/A,true,true,2026-08-15T00:00:00+00:00,2026-09-18T09:05:00+00:00,us-west-2,ec2,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
dave,arn:aws:iam::123456789012:user/dave,2023-11-01T00:00:00+00:00,false,N/A,N/A,N/A,false,true,2025-12-01T00:00:00+00:00,2026-09-10T11:00:00+00:00,eu-west-1,sts,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
erin,arn:aws:iam::123456789012:user/erin,2025-02-14T00:00:00+00:00,false,N/A,N/A,N/A,false,true,2026-08-01T00:00:00+00:00,2026-01-10T10:00:00+00:00,us-east-1,iam,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
Load the evidence as text

Credential evidence mixes dates with special values such as N/A. Loading every field as text preserves the source record before control logic interprets it.

A fixed analysis_date makes future age calculations reproducible. The same evidence produces the same result whenever you rerun the project.

  • Create sql/01_load_and_mfa.sql by using the new-file control in Visual Studio Code's file tree.
  • Add the import setup and row-count check by pasting this SQL:
CREATE OR REPLACE TABLE run_context AS
SELECT DATE '2026-09-19' AS analysis_date;

CREATE OR REPLACE TABLE credential_report_before AS
SELECT *
FROM read_csv(
    'data/iam_credential_report_before.csv',
    all_varchar = true
);

SELECT COUNT(*) AS evidence_rows
FROM credential_report_before;

What does this SQL do?

  • The run_context table stores 2026-09-19 as the shared analysis date.
  • The read_csv call loads the synthetic report into credential_report_before.
  • The all_varchar = true option keeps every imported field as text.
  • The final query counts the imported records under evidence_rows.
  • Save sql/01_load_and_mfa.sql.
  • Switch back to Windows PowerShell from the setup step.

Before you run this first slice, how many evidence records do you expect the table to contain?

  • Load the report into compliance.duckdb by running this command:
duckdb compliance.duckdb -f sql/01_load_and_mfa.sql

What does this command do?

DuckDB opens the persistent compliance.duckdb database. The -f argument runs every statement in sql/01_load_and_mfa.sql before the command exits.

You'll see 6 under evidence_rows. That result proves all six source records reached the database.

Good progress. Your raw credential evidence is now queryable inside a persistent local database.

Did the import fail?

  • Confirm that PowerShell is still using the aws-iam-compliance-triage project root.
  • Confirm that the CSV path is exactly data/iam_credential_report_before.csv.
  • Save both files before rerunning the command.

Need another pair of eyes? Ask for help diagnosing the DuckDB CSV import.

Detect the missing-MFA finding

AWS Security Hub defines IAM.5 as MFA should be enabled for all IAM users that have a console password. The first control query tests that rule against the imported evidence.

The root account has separate governance requirements. This query focuses on non-root users with console passwords.

  • Append the first control query below the row-count query in sql/01_load_and_mfa.sql by pasting this SQL:
SELECT
    'IAM.5' AS control_id,
    user AS principal,
    arn AS resource_arn,
    'Console password is enabled but MFA is not active' AS evidence,
    'Enable MFA for the IAM user' AS recommended_action
FROM credential_report_before
WHERE NOT contains(user, 'root_account')
  AND password_enabled = 'true'
  AND mfa_active = 'false';

How does the control find a failure?

  • The output labels every matching row with control ID IAM.5.
  • The contains condition excludes the root_account row.
  • The remaining conditions require an enabled console password.
  • The final condition requires an inactive MFA setting.
  • Save sql/01_load_and_mfa.sql.

✔️ Awesome, I've got everything!

Great. Confirm that the import setup appears above the IAM.5 control query.

ⓧ I'd like to double check the full code

CREATE OR REPLACE TABLE run_context AS
SELECT DATE '2026-09-19' AS analysis_date;

CREATE OR REPLACE TABLE credential_report_before AS
SELECT *
FROM read_csv(
    'data/iam_credential_report_before.csv',
    all_varchar = true
);

SELECT COUNT(*) AS evidence_rows
FROM credential_report_before;

SELECT
    'IAM.5' AS control_id,
    user AS principal,
    arn AS resource_arn,
    'Console password is enabled but MFA is not active' AS evidence,
    'Enable MFA for the IAM user' AS recommended_action
FROM credential_report_before
WHERE NOT contains(user, 'root_account')
  AND password_enabled = 'true'
  AND mfa_active = 'false';

Before you run the completed script, which user do you expect the three conditions to retain?

  • Switch back to Windows PowerShell.
  • Run the completed control script against compliance.duckdb by using this command:
duckdb compliance.duckdb -f sql/01_load_and_mfa.sql

What happens on this run?

The script refreshes both database tables before counting the evidence. It then evaluates the missing-MFA conditions against every imported row.

You'll first see 6 under evidence_rows. The next result identifies alice under control IAM.5.

The finding includes the principal ARN. It also includes the evidence statement and the recommended action.

You have your first working compliance finding. Raw identity evidence now produces a reviewable remediation record.

Missing the finding for alice?

  • Confirm that the alice row contains true for password_enabled.
  • Confirm that the same row contains false for mfa_active.
  • Confirm that the control query appears after the row-count query.

Still not seeing the row? Ask for help tracing the IAM.5 filter.

Your evidence pipeline now turns a synthetic credential report into a visible control failure. Next, you'll test how complete a first access-key check really is.

Expose the Naive Key Check Gap

Your first control already turns raw AWS IAM evidence into a visible missing-MFA finding. Now you need to test whether a direct key-age check covers every credential in the report.

This step deliberately begins with a SQL query scoped to the first access-key slot. You'll compare its result with the source row before deciding whether the finding set is complete.

In this step, get ready to:
  • Create a first-slot query for active access keys older than 90 days.
  • Run the query against the existing compliance.duckdb database.
  • Compare the query result with the source evidence to assess its completeness.
Write a first-slot key check

The first test uses the most direct field pair: access_key_1_active and access_key_1_last_rotated. It calculates credential age against the pinned analysis date.

  • In the Visual Studio Code workspace sidebar, select the existing sql folder.
  • Use the file control beside that folder to create sql/02_naive_key_check.sql.
  • Add the first-slot age query by pasting the code below:
SELECT
    user AS principal,
    1 AS key_slot,
    date_diff(
        'day',
        TRY_CAST(left(access_key_1_last_rotated, 10) AS DATE),
        (SELECT analysis_date FROM run_context)
    ) AS credential_age_days
FROM credential_report_before
WHERE NOT contains(user, 'root_account')
  AND access_key_1_active = 'true'
  AND date_diff(
        'day',
        TRY_CAST(left(access_key_1_last_rotated, 10) AS DATE),
        (SELECT analysis_date FROM run_context)
      ) > 90
ORDER BY principal;

What does this query do?

  • left(access_key_1_last_rotated, 10) extracts the date portion from the timestamp text.
  • TRY_CAST converts that date text safely. Values such as N/A become NULL.
  • date_diff compares the rotation date with analysis_date to calculate credential_age_days.
  • The WHERE clause keeps active non-root keys whose calculated age exceeds 90 days.
  • Save sql/02_naive_key_check.sql.

You should see 02_naive_key_check.sql listed inside the sql folder with no unsaved indicator.

✔️ Awesome, I've got everything!

Your saved file now contains the complete first-slot age query.

ⓧ I'd like to double check the full code

The complete file should match this reference.

SELECT
    user AS principal,
    1 AS key_slot,
    date_diff(
        'day',
        TRY_CAST(left(access_key_1_last_rotated, 10) AS DATE),
        (SELECT analysis_date FROM run_context)
    ) AS credential_age_days
FROM credential_report_before
WHERE NOT contains(user, 'root_account')
  AND access_key_1_active = 'true'
  AND date_diff(
        'day',
        TRY_CAST(left(access_key_1_last_rotated, 10) AS DATE),
        (SELECT analysis_date FROM run_context)
      ) > 90
ORDER BY principal;
Run the naive check

A query can run successfully while returning an incomplete result. This run tests whether the narrow field selection matches the underlying evidence.

  • Return to the Windows PowerShell session from earlier.

Before you run this, consider how many old active user keys the query will find.

  • Execute the naive key check with DuckDB by running this command:
duckdb compliance.duckdb -f sql/02_naive_key_check.sql

What should you see?

You'll see a single result row. It names dave as the principal with key_slot set to 1.

Bob does not appear. This is the intended shortfall in the naive check.

Did the query fail to run?

  • Confirm that PowerShell is still inside the aws-iam-compliance-triage folder.
  • Rerun the loading script from the previous step if the database reports that a required table is missing.
  • Compare your saved query with the full-code reference if PowerShell reports a SQL syntax problem.

Still stuck? Help me diagnose the naive key check.

Confirm the missed evidence

One returned row does not prove that only one risky key exists. The source CSV lets you test the result against the evidence that produced it.

  • Switch back to Visual Studio Code.
  • Select data/iam_credential_report_before.csv in the workspace sidebar.
  • Locate the CSV row that begins with bob.
  • Check Bob's value under access_key_1_active.
  • Check Bob's value under access_key_2_active.
  • Check Bob's date under access_key_2_last_rotated.

Bob's first key slot is false. His second key slot is true.

The second key was last rotated on 2026-01-15T00:00:00+00:00. That date is more than 90 days before the analysis date of 2026-09-19.

What does this comparison prove?

The query misses Bob because every filter and age expression reads slot 1 fields. Bob's old active credential lives in slot 2.

That mismatch is an evidence completeness failure. A control can produce valid rows while still overlooking risk.

You've proved that the first-slot query overlooks a risky credential stored in the repeated second slot. Next, you'll reshape both slots into one reusable evidence set and build the complete triage queue.

Normalize Credentials and Build the Triage Queue

Your first key-age check found dave in slot 1. Bob's old slot-2 key stayed hidden because the query never examined the second column group.

This step turns both key slots into rows through normalization. One reusable SQL shape can then evaluate every credential and produce a complete remediation queue.

In this step, get ready to:
  • Normalize both access-key slots into one reusable view.
  • Combine three identity controls into a findings view.
  • Run the severity-ranked queue and confirm all four findings.
Normalize both access-key slots

The synthetic AWS IAM credential report stores two possible access keys in repeated column groups. Converting those groups into rows gives each credential the same fields for slot number, active status, and rotation date.

  • In Visual Studio Code, create sql/03_build_triage_queue.sql inside the sql folder using the file control in the left sidebar.
  • Add the normalized access-key view by pasting this code into the new file:
CREATE OR REPLACE VIEW access_keys_before AS
SELECT
    user,
    arn,
    1 AS key_slot,
    access_key_1_active AS active,
    TRY_CAST(left(access_key_1_last_rotated, 10) AS DATE) AS last_rotated
FROM credential_report_before
UNION ALL
SELECT
    user,
    arn,
    2 AS key_slot,
    access_key_2_active AS active,
    TRY_CAST(left(access_key_2_last_rotated, 10) AS DATE) AS last_rotated
FROM credential_report_before;

How does normalization close the gap?

  • The first query converts each slot-1 column group into a row with key_slot set to 1.
  • The second query applies the same shape to slot 2 with key_slot set to 2.
  • The UNION ALL operator keeps rows from both slots, including Bob's previously missed credential.
  • The TRY_CAST expression safely converts valid date text while turning values such as N/A into null dates.
  • Save sql/03_build_triage_queue.sql.
  • Confirm that 03_build_triage_queue.sql appears inside the sql folder in the left sidebar.

Is the file in the wrong folder?

Check that the file appears beside 01_load_and_mfa.sql and 02_naive_key_check.sql. Move it into the sql folder if it appears at the top level of the workspace.

Ask for help if needed. Help me place my SQL file in the correct Visual Studio Code folder.

Combine three controls

The findings_before view gives three AWS Security Hub controls one consistent finding shape. A numeric severity rank places the Critical root-key finding before the Medium user findings.

The next three code blocks form one CREATE OR REPLACE VIEW statement. Keep the blocks together in the displayed order before saving or running the script.

  • Place your cursor after the semicolon that ends the access_keys_before view.
  • Build findings_before by pasting the next three blocks in sequence:
CREATE OR REPLACE VIEW findings_before AS
SELECT
    'IAM.4' AS control_id,
    'IAM root user access key should not exist' AS control_name,
    'CRITICAL' AS severity,
    1 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    CAST(NULL AS INTEGER) AS key_slot,
    CAST(NULL AS BIGINT) AS credential_age_days,
    'Cloud Platform' AS owner_team,
    'Root user has an active access key' AS evidence,
    'Delete every access key associated with the root user' AS recommended_action
FROM credential_report_before
WHERE contains(user, 'root_account')
  AND (access_key_1_active = 'true' OR access_key_2_active = 'true')

UNION ALL

What does the root-key branch add?

  • Control IAM.4 finds an active access key on root_account.
  • Its severity_rank value of 1 moves the Critical finding to the top of the queue.
  • The null key slot shows that the control applies to the root account as a whole.
SELECT
    'IAM.5' AS control_id,
    'MFA should be enabled for all IAM users that have a console password' AS control_name,
    'MEDIUM' AS severity,
    2 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    CAST(NULL AS INTEGER) AS key_slot,
    CAST(NULL AS BIGINT) AS credential_age_days,
    'Identity Operations' AS owner_team,
    'Console password is enabled but MFA is not active' AS evidence,
    'Enable MFA for the IAM user' AS recommended_action
FROM credential_report_before
WHERE NOT contains(user, 'root_account')
  AND password_enabled = 'true'
  AND mfa_active = 'false'

UNION ALL

What does the MFA branch add?

  • Control IAM.5 checks non-root users whose console password is enabled.
  • The final filter keeps users whose MFA status is false.
  • The owner and recommended action make the finding ready for remediation triage.
SELECT
    'IAM.3' AS control_id,
    'IAM users'' access keys should be rotated every 90 days or less' AS control_name,
    'MEDIUM' AS severity,
    2 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    key_slot,
    date_diff('day', last_rotated, analysis_date) AS credential_age_days,
    'Identity Operations' AS owner_team,
    'Active access key is older than 90 days' AS evidence,
    'Rotate the access key and update every dependent workload' AS recommended_action
FROM access_keys_before
CROSS JOIN run_context
WHERE NOT contains(user, 'root_account')
  AND active = 'true'
  AND last_rotated IS NOT NULL
  AND date_diff('day', last_rotated, analysis_date) > 90;

What does the rotation branch add?

  • Control IAM.3 reads from the normalized access_keys_before view, so the same rule covers both slots.
  • The cross join makes the fixed analysis_date available to every key row.
  • The age filter keeps active non-root credentials older than 90 days.
  • Save sql/03_build_triage_queue.sql.
  • Switch back to Windows PowerShell from earlier.
  • Confirm that both views compile by running this command:
duckdb compliance.duckdb -f sql/03_build_triage_queue.sql

PowerShell returns to the prompt without reporting a SQL error. This confirms that access_keys_before and findings_before compile inside DuckDB.

Does the script stop with an error?

Check that each UNION ALL sits between complete SELECT branches. Confirm that the only semicolon in the findings_before statement appears after the final age filter.

Ask for help with the failing section. Help me diagnose why my findings_before view does not compile.

Run the prioritized queue

The findings now share the same columns. The final query selects the review fields and sorts by severity rank, principal, and key slot.

  • Return to sql/03_build_triage_queue.sql in Visual Studio Code.
  • Add the ordered queue query below the semicolon that ends findings_before by pasting this code:
SELECT
    control_id,
    severity,
    principal,
    key_slot,
    credential_age_days,
    owner_team,
    evidence,
    recommended_action
FROM findings_before
ORDER BY severity_rank, principal, key_slot;

How is the queue ordered?

  • The selected fields show who owns each finding, what evidence triggered it, and what action closes it.
  • The severity_rank sort places the Critical root finding before every Medium finding.
  • The principal and key-slot sorts keep findings deterministic within the same severity.
  • Save sql/03_build_triage_queue.sql.

✔️ Awesome, I've got everything!

Great. Confirm that sql/03_build_triage_queue.sql is saved before you run the completed queue.

ⓧ I'd like to double check the full code

Compare your file with this complete version. Every branch and final sort should appear in the same order.

CREATE OR REPLACE VIEW access_keys_before AS
SELECT
    user,
    arn,
    1 AS key_slot,
    access_key_1_active AS active,
    TRY_CAST(left(access_key_1_last_rotated, 10) AS DATE) AS last_rotated
FROM credential_report_before
UNION ALL
SELECT
    user,
    arn,
    2 AS key_slot,
    access_key_2_active AS active,
    TRY_CAST(left(access_key_2_last_rotated, 10) AS DATE) AS last_rotated
FROM credential_report_before;

CREATE OR REPLACE VIEW findings_before AS
SELECT
    'IAM.4' AS control_id,
    'IAM root user access key should not exist' AS control_name,
    'CRITICAL' AS severity,
    1 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    CAST(NULL AS INTEGER) AS key_slot,
    CAST(NULL AS BIGINT) AS credential_age_days,
    'Cloud Platform' AS owner_team,
    'Root user has an active access key' AS evidence,
    'Delete every access key associated with the root user' AS recommended_action
FROM credential_report_before
WHERE contains(user, 'root_account')
  AND (access_key_1_active = 'true' OR access_key_2_active = 'true')

UNION ALL

SELECT
    'IAM.5' AS control_id,
    'MFA should be enabled for all IAM users that have a console password' AS control_name,
    'MEDIUM' AS severity,
    2 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    CAST(NULL AS INTEGER) AS key_slot,
    CAST(NULL AS BIGINT) AS credential_age_days,
    'Identity Operations' AS owner_team,
    'Console password is enabled but MFA is not active' AS evidence,
    'Enable MFA for the IAM user' AS recommended_action
FROM credential_report_before
WHERE NOT contains(user, 'root_account')
  AND password_enabled = 'true'
  AND mfa_active = 'false'

UNION ALL

SELECT
    'IAM.3' AS control_id,
    'IAM users'' access keys should be rotated every 90 days or less' AS control_name,
    'MEDIUM' AS severity,
    2 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    key_slot,
    date_diff('day', last_rotated, analysis_date) AS credential_age_days,
    'Identity Operations' AS owner_team,
    'Active access key is older than 90 days' AS evidence,
    'Rotate the access key and update every dependent workload' AS recommended_action
FROM access_keys_before
CROSS JOIN run_context
WHERE NOT contains(user, 'root_account')
  AND active = 'true'
  AND last_rotated IS NOT NULL
  AND date_diff('day', last_rotated, analysis_date) > 90;

SELECT
    control_id,
    severity,
    principal,
    key_slot,
    credential_age_days,
    owner_team,
    evidence,
    recommended_action
FROM findings_before
ORDER BY severity_rank, principal, key_slot;

Before you run the final check, which credential do you expect the normalized slot-2 rows to recover?

  • Run the completed triage queue from PowerShell by using this command:
duckdb compliance.duckdb -f sql/03_build_triage_queue.sql

What should I see?

You should see four ordered findings.

  • The first row is the Critical IAM.4 finding for root_account.
  • The second row is the Medium IAM.5 finding for alice.
  • The third row is the Medium IAM.3 finding for bob in key slot 2.
  • The fourth row is the Medium IAM.3 finding for dave in key slot 1.

Are any findings missing?

If Bob is missing, confirm that the second half of access_keys_before reads access_key_2_active and access_key_2_last_rotated.

If the root finding is not first, confirm that IAM.4 uses severity_rank value 1. Confirm that the final query starts its ordering with severity_rank.

Ask for help with your result. Help me compare my triage queue with the expected four findings.

You closed the completeness gap. Your queue now retains both old user keys while placing the Critical root finding first.

Next, you'll retest the same controls against remediated evidence and export the before-and-after results.

Retest, Export, and Publish the Evidence

Your normalized queue already catches four AWS IAM control failures in the before-remediation evidence.

A complete governance workflow must prove remediation. It must also preserve reviewable evidence.

You will use DuckDB to retest the same SQL controls against remediated data. GitHub will hold the sanitized exports and documentation.

In this step, get ready to:
  • Retest the three controls against remediated credential evidence.
  • Export the before-remediation findings and after-remediation findings.
  • Publish the sanitized project in a public GitHub repository.
Retest controls against remediated evidence

The remediated report represents the state after each original finding has been addressed. Running the same controls against it proves whether the queue is clear.

  • In the Visual Studio Code Explorer sidebar, select the data folder.
  • Click the New File icon in the Explorer sidebar.
  • Name the file iam_credential_report_after.csv.
  • Add the remediated evidence by pasting this dataset into the new file:
user,arn,user_creation_time,password_enabled,password_last_used,password_last_changed,password_next_rotation,mfa_active,access_key_1_active,access_key_1_last_rotated,access_key_1_last_used_date,access_key_1_last_used_region,access_key_1_last_used_service,access_key_2_active,access_key_2_last_rotated,access_key_2_last_used_date,access_key_2_last_used_region,access_key_2_last_used_service,cert_1_active,cert_1_last_rotated,cert_2_active,cert_2_last_rotated,additional_credentials_info
root_account,arn:aws:iam::123456789012:root,2022-01-01T00:00:00+00:00,true,2026-09-01T08:00:00+00:00,not_supported,not_supported,true,false,N/A,N/A,N/A,N/A,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
alice,arn:aws:iam::123456789012:user/alice,2025-05-10T00:00:00+00:00,true,2026-09-18T14:00:00+00:00,2026-06-01T00:00:00+00:00,N/A,true,false,N/A,N/A,N/A,N/A,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
bob,arn:aws:iam::123456789012:user/bob,2024-03-12T00:00:00+00:00,false,N/A,N/A,N/A,false,false,N/A,N/A,N/A,N/A,true,2026-09-01T00:00:00+00:00,2026-09-15T16:30:00+00:00,us-east-1,s3,false,N/A,false,N/A,N/A
carol,arn:aws:iam::123456789012:user/carol,2024-07-20T00:00:00+00:00,true,2026-09-18T09:00:00+00:00,2026-08-01T00:00:00+00:00,N/A,true,true,2026-08-15T00:00:00+00:00,2026-09-18T09:05:00+00:00,us-west-2,ec2,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
 dave,arn:aws:iam::123456789012:user/dave,2023-11-01T00:00:00+00:00,false,N/A,N/A,N/A,false,true,2026-09-05T00:00:00+00:00,2026-09-10T11:00:00+00:00,eu-west-1,sts,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
 erin,arn:aws:iam::123456789012:user/erin,2025-02-14T00:00:00+00:00,false,N/A,N/A,N/A,false,false,N/A,N/A,N/A,N/A,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A

What changed in the evidence?

  • The root_account row now marks its access key as inactive.
  • The alice row now records active MFA.
  • The bob row now records a recent rotation date for key slot 2.
  • The dave row now records a recent rotation date for key slot 1.
  • The erin row now marks the active key as disabled.
  • Save data/iam_credential_report_after.csv.
  • Confirm that iam_credential_report_after.csv appears beside the before-remediation report in the Explorer sidebar.

Does the CSV look misaligned?

  • Check that the header remains on one line.
  • Check that each principal occupies one complete line.
  • Remove any spaces before dave or erin if your editor inserted them.

Still stuck? Help me check the column alignment in my remediated IAM credential report.

✔️ Awesome, I've got everything!

Great. Confirm that the remediated CSV is saved inside the data folder.

ⓧ I'd like to double check the full code

user,arn,user_creation_time,password_enabled,password_last_used,password_last_changed,password_next_rotation,mfa_active,access_key_1_active,access_key_1_last_rotated,access_key_1_last_used_date,access_key_1_last_used_region,access_key_1_last_used_service,access_key_2_active,access_key_2_last_rotated,access_key_2_last_used_date,access_key_2_last_used_region,access_key_2_last_used_service,cert_1_active,cert_1_last_rotated,cert_2_active,cert_2_last_rotated,additional_credentials_info
root_account,arn:aws:iam::123456789012:root,2022-01-01T00:00:00+00:00,true,2026-09-01T08:00:00+00:00,not_supported,not_supported,true,false,N/A,N/A,N/A,N/A,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
alice,arn:aws:iam::123456789012:user/alice,2025-05-10T00:00:00+00:00,true,2026-09-18T14:00:00+00:00,2026-06-01T00:00:00+00:00,N/A,true,false,N/A,N/A,N/A,N/A,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
bob,arn:aws:iam::123456789012:user/bob,2024-03-12T00:00:00+00:00,false,N/A,N/A,N/A,false,false,N/A,N/A,N/A,N/A,true,2026-09-01T00:00:00+00:00,2026-09-15T16:30:00+00:00,us-east-1,s3,false,N/A,false,N/A,N/A
carol,arn:aws:iam::123456789012:user/carol,2024-07-20T00:00:00+00:00,true,2026-09-18T09:00:00+00:00,2026-08-01T00:00:00+00:00,N/A,true,true,2026-08-15T00:00:00+00:00,2026-09-18T09:05:00+00:00,us-west-2,ec2,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
 dave,arn:aws:iam::123456789012:user/dave,2023-11-01T00:00:00+00:00,false,N/A,N/A,N/A,false,true,2026-09-05T00:00:00+00:00,2026-09-10T11:00:00+00:00,eu-west-1,sts,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
 erin,arn:aws:iam::123456789012:user/erin,2025-02-14T00:00:00+00:00,false,N/A,N/A,N/A,false,false,N/A,N/A,N/A,N/A,false,N/A,N/A,N/A,N/A,false,N/A,false,N/A,N/A
  • In the Explorer sidebar, select the sql folder.
  • Click the New File icon.
  • Name the file 04_retest_and_export.sql.
  • Load the remediated report by pasting this first SQL construct:
CREATE OR REPLACE TABLE credential_report_after AS
SELECT *
FROM read_csv(
    'data/iam_credential_report_after.csv',
    all_varchar = true
);

What does this SQL do?

  • The table preserves every source field as text through all_varchar = true.
  • Replacing the table makes each retest repeatable against the current CSV.
  • Place the cursor after the table statement.
  • Normalize both remediated access-key slots by pasting this view below it:
CREATE OR REPLACE VIEW access_keys_after AS
SELECT
    user,
    arn,
    1 AS key_slot,
    access_key_1_active AS active,
    TRY_CAST(left(access_key_1_last_rotated, 10) AS DATE) AS last_rotated
FROM credential_report_after
UNION ALL
SELECT
    user,
    arn,
    2 AS key_slot,
    access_key_2_active AS active,
    TRY_CAST(left(access_key_2_last_rotated, 10) AS DATE) AS last_rotated
FROM credential_report_after;

Why normalize the after state too?

The before and after tests need the same evidence shape. Normalizing both slots prevents a remediated key in slot 2 from escaping the retest.

  • Save sql/04_retest_and_export.sql.
  • Switch back to Windows PowerShell.
  • Check that the remediated table and normalized view load successfully by running:
duckdb compliance.duckdb -f sql/04_retest_and_export.sql

What should this first run prove?

PowerShell returns to its prompt without a CSV parsing error. That confirms DuckDB can read the remediated report and create the normalized view.

Did the remediated report fail to load?

  • Confirm that PowerShell is still in the aws-iam-compliance-triage folder.
  • Confirm that the CSV path is exactly data/iam_credential_report_after.csv.

Still stuck? Help me diagnose why DuckDB cannot load my remediated credential report.

The control logic should stay identical across both evidence states. Only the view name and source relations change.

  • Switch back to sql/03_build_triage_queue.sql.
  • Copy the entire findings_before view definition.
  • Return to sql/04_retest_and_export.sql.
  • Paste the copied view below access_keys_after.
  • Rename findings_before to findings_after in the copied view.
  • Replace every credential_report_before reference with credential_report_after in the copied view.
  • Replace access_keys_before with access_keys_after in the copied view.
  • Add the before-and-after count query below the completed view:
SELECT 'before' AS test_stage, COUNT(*) AS open_findings
FROM findings_before
UNION ALL
SELECT 'after' AS test_stage, COUNT(*) AS open_findings
FROM findings_after
ORDER BY test_stage;

What does the comparison measure?

  • The before row counts the original triage queue.
  • The after row counts open findings after remediation.
  • Using the same controls makes the two counts directly comparable.
  • Save sql/04_retest_and_export.sql.

Before you run the retest, do you expect every original finding to disappear?

  • Run the retest from Windows PowerShell with this command:
duckdb compliance.duckdb -f sql/04_retest_and_export.sql

What should the retest show?

You should see 4 open findings for before. You should see 0 open findings for after.

That result proves the remediated evidence closes IAM.3, IAM.4, and IAM.5.

  • In sql/04_retest_and_export.sql, place the cursor immediately above the count query.
  • Export the original queue by pasting this statement:
COPY (
    SELECT
        control_id,
        control_name,
        severity,
        principal,
        resource_arn,
        key_slot,
        credential_age_days,
        owner_team,
        evidence,
        recommended_action
    FROM findings_before
    ORDER BY severity_rank, principal, key_slot
) TO 'output/findings_before.csv' (HEADER, DELIMITER ',');

What does the first export preserve?

The export keeps each control result with its severity and owner. It also retains the evidence and recommended action needed for remediation tracking.

  • Save sql/04_retest_and_export.sql.
  • Generate the before-remediation export by running:
duckdb compliance.duckdb -f sql/04_retest_and_export.sql

What should the first export create?

The Explorer sidebar should show findings_before.csv inside output.

The file contains four ordered findings. The Critical root finding comes first.

  • Place the cursor below the first export statement.
  • Add the after-remediation export by pasting this statement:
COPY (
    SELECT
        control_id,
        control_name,
        severity,
        principal,
        resource_arn,
        key_slot,
        credential_age_days,
        owner_team,
        evidence,
        recommended_action
    FROM findings_after
    ORDER BY severity_rank, principal, key_slot
) TO 'output/findings_after.csv' (HEADER, DELIMITER ',');

Why export an empty result?

An export with headers and zero data rows is durable evidence of a clean retest. Reviewers can inspect the tested schema without relying on a screenshot alone.

  • Save sql/04_retest_and_export.sql.

Before the final local run, which result would prove that remediation closed every queued finding?

  • Run the completed retest and export script with this command:
duckdb compliance.duckdb -f sql/04_retest_and_export.sql

What should the completed run show?

The terminal shows before with 4 open findings. It shows after with 0 open findings.

You should also see output/findings_before.csv and output/findings_after.csv in the Explorer sidebar.

Are the exports missing?

  • Confirm that the existing output folder is inside aws-iam-compliance-triage.
  • Confirm that both COPY statements appear above the count query.
  • Confirm that findings_after reads from the after-remediation relations.

Still stuck? Help me find why my DuckDB retest does not create both CSV exports.

✔️ Awesome, I've got everything!

Excellent. Confirm that the terminal shows four findings before remediation and zero afterward.

ⓧ I'd like to double check the full code

CREATE OR REPLACE TABLE credential_report_after AS
SELECT *
FROM read_csv(
    'data/iam_credential_report_after.csv',
    all_varchar = true
);

CREATE OR REPLACE VIEW access_keys_after AS
SELECT
    user,
    arn,
    1 AS key_slot,
    access_key_1_active AS active,
    TRY_CAST(left(access_key_1_last_rotated, 10) AS DATE) AS last_rotated
FROM credential_report_after
UNION ALL
SELECT
    user,
    arn,
    2 AS key_slot,
    access_key_2_active AS active,
    TRY_CAST(left(access_key_2_last_rotated, 10) AS DATE) AS last_rotated
FROM credential_report_after;

CREATE OR REPLACE VIEW findings_after AS
SELECT
    'IAM.4' AS control_id,
    'IAM root user access key should not exist' AS control_name,
    'CRITICAL' AS severity,
    1 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    CAST(NULL AS INTEGER) AS key_slot,
    CAST(NULL AS BIGINT) AS credential_age_days,
    'Cloud Platform' AS owner_team,
    'Root user has an active access key' AS evidence,
    'Delete every access key associated with the root user' AS recommended_action
FROM credential_report_after
WHERE contains(user, 'root_account')
  AND (access_key_1_active = 'true' OR access_key_2_active = 'true')

UNION ALL

SELECT
    'IAM.5' AS control_id,
    'MFA should be enabled for all IAM users that have a console password' AS control_name,
    'MEDIUM' AS severity,
    2 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    CAST(NULL AS INTEGER) AS key_slot,
    CAST(NULL AS BIGINT) AS credential_age_days,
    'Identity Operations' AS owner_team,
    'Console password is enabled but MFA is not active' AS evidence,
    'Enable MFA for the IAM user' AS recommended_action
FROM credential_report_after
WHERE NOT contains(user, 'root_account')
  AND password_enabled = 'true'
  AND mfa_active = 'false'

UNION ALL

SELECT
    'IAM.3' AS control_id,
    'IAM users'' access keys should be rotated every 90 days or less' AS control_name,
    'MEDIUM' AS severity,
    2 AS severity_rank,
    user AS principal,
    arn AS resource_arn,
    key_slot,
    date_diff('day', last_rotated, analysis_date) AS credential_age_days,
    'Identity Operations' AS owner_team,
    'Active access key is older than 90 days' AS evidence,
    'Rotate the access key and update every dependent workload' AS recommended_action
FROM access_keys_after
CROSS JOIN run_context
WHERE NOT contains(user, 'root_account')
  AND active = 'true'
  AND last_rotated IS NOT NULL
  AND date_diff('day', last_rotated, analysis_date) > 90;

COPY (
    SELECT
        control_id,
        control_name,
        severity,
        principal,
        resource_arn,
        key_slot,
        credential_age_days,
        owner_team,
        evidence,
        recommended_action
    FROM findings_before
    ORDER BY severity_rank, principal, key_slot
) TO 'output/findings_before.csv' (HEADER, DELIMITER ',');

COPY (
    SELECT
        control_id,
        control_name,
        severity,
        principal,
        resource_arn,
        key_slot,
        credential_age_days,
        owner_team,
        evidence,
        recommended_action
    FROM findings_after
    ORDER BY severity_rank, principal, key_slot
) TO 'output/findings_after.csv' (HEADER, DELIMITER ',');

SELECT 'before' AS test_stage, COUNT(*) AS open_findings
FROM findings_before
UNION ALL
SELECT 'after' AS test_stage, COUNT(*) AS open_findings
FROM findings_after
ORDER BY test_stage;
Document the reproducible workflow

The exports prove the result. A README makes the method understandable to someone who did not build the project.

  • In the Explorer sidebar, select the aws-iam-compliance-triage workspace folder.
  • Click the New File icon.
  • Name the file README.md.
  • Start the project documentation by pasting this first section:
# AWS IAM Compliance Triage Engine

A local SQL portfolio project that converts synthetic AWS IAM credential evidence into prioritized compliance findings and proves remediation with a repeatable retest.

## What it detects

- IAM.3: IAM users' access keys should be rotated every 90 days or less
- IAM.4: IAM root user access key should not exist
- IAM.5: MFA should be enabled for all IAM users that have a console password

## Tools

- DuckDB CLI 1.5.5
- SQL
- Visual Studio Code
- Windows PowerShell

What does this section establish?

The opening names the project outcome and the exact controls. The tools list makes the local workflow reproducible.

  • Save README.md.
  • Open the Markdown preview from the editor toolbar.

You should see the project title followed by three control descriptions and four tools.

  • Return to the README.md editor.
  • Append the run instructions and normalization explanation below the tools list:


## Run the project

From the repository root:

```powershell
duckdb compliance.duckdb -f sql/01_load_and_mfa.sql
duckdb compliance.duckdb -f sql/02_naive_key_check.sql
duckdb compliance.duckdb -f sql/03_build_triage_queue.sql
duckdb compliance.duckdb -f sql/04_retest_and_export.sql
```

Expected result:

- Before remediation: 4 findings
- After remediation: 0 findings
- Exports: `output/findings_before.csv` and `output/findings_after.csv`

## Why the normalization matters

An IAM credential report stores two possible access keys in repeated column groups. A query that checks only `access_key_1_*` misses risks stored in `access_key_2_*`. The project normalizes both slots with `UNION ALL`, then applies one reusable rotation rule.

Why include the failed approach?

The normalization section records the completeness failure you found earlier. It shows why checking both repeated access-key groups is part of defensible control testing.

  • Save README.md.
  • Refresh the Markdown preview.

You should see four runnable commands followed by the expected before count and after count.

  • Return to the README.md editor.
  • Append the safety guidance and project references below the normalization section:


## Data safety

The repository contains synthetic identities and a documentation-style account number. Do not commit a real IAM credential report, credentials, account secrets, or private company evidence to a public repository.

## Portfolio talking points

- Converted raw cloud evidence into reusable SQL control logic.
- Demonstrated a completeness failure, then fixed it through normalization.
- Preserved control ID, severity, owner, evidence, and remediation guidance in the finding model.
- Used before-and-after evidence to prove closure rather than only reporting an issue.

## References

- AWS IAM credential reports: https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_getting-report.html
- AWS Security Hub IAM controls: https://docs.aws.amazon.com/securityhub/latest/userguide/iam-controls.html
- DuckDB CSV import: https://duckdb.org/docs/current/data/csv/overview
- DuckDB CSV export: https://duckdb.org/docs/current/guides/file_formats/csv_export

Why lead with data safety?

A public security project needs a clear boundary around publishable evidence. This README states that the repository uses synthetic identities and excludes real account evidence.

  • Save README.md.
  • Refresh the Markdown preview.

You should see the data-safety warning followed by four portfolio talking points and four references.

Does the README preview look broken?

  • Confirm that each section heading starts on its own line.
  • Confirm that the PowerShell commands remain between the opening and closing code fences.

Still stuck? Help me fix the Markdown formatting in my project README.

✔️ Awesome, I've got everything!

Good work. Confirm that the README preview shows every section from the title through the references.

ⓧ I'd like to double check the full code

# AWS IAM Compliance Triage Engine

A local SQL portfolio project that converts synthetic AWS IAM credential evidence into prioritized compliance findings and proves remediation with a repeatable retest.

## What it detects

- IAM.3: IAM users' access keys should be rotated every 90 days or less
- IAM.4: IAM root user access key should not exist
- IAM.5: MFA should be enabled for all IAM users that have a console password

## Tools

- DuckDB CLI 1.5.5
- SQL
- Visual Studio Code
- Windows PowerShell

## Run the project

From the repository root:

```powershell
duckdb compliance.duckdb -f sql/01_load_and_mfa.sql
duckdb compliance.duckdb -f sql/02_naive_key_check.sql
duckdb compliance.duckdb -f sql/03_build_triage_queue.sql
duckdb compliance.duckdb -f sql/04_retest_and_export.sql
```

Expected result:

- Before remediation: 4 findings
- After remediation: 0 findings
- Exports: `output/findings_before.csv` and `output/findings_after.csv`

## Why the normalization matters

An IAM credential report stores two possible access keys in repeated column groups. A query that checks only `access_key_1_*` misses risks stored in `access_key_2_*`. The project normalizes both slots with `UNION ALL`, then applies one reusable rotation rule.

## Data safety

The repository contains synthetic identities and a documentation-style account number. Do not commit a real IAM credential report, credentials, account secrets, or private company evidence to a public repository.

## Portfolio talking points

- Converted raw cloud evidence into reusable SQL control logic.
- Demonstrated a completeness failure, then fixed it through normalization.
- Preserved control ID, severity, owner, evidence, and remediation guidance in the finding model.
- Used before-and-after evidence to prove closure rather than only reporting an issue.

## References

- AWS IAM credential reports: https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_getting-report.html
- AWS Security Hub IAM controls: https://docs.aws.amazon.com/securityhub/latest/userguide/iam-controls.html
- DuckDB CSV import: https://duckdb.org/docs/current/data/csv/overview
- DuckDB CSV export: https://duckdb.org/docs/current/guides/file_formats/csv_export
  • In the Explorer sidebar, select the aws-iam-compliance-triage workspace folder.
  • Click the New File icon.
  • Name the file .gitignore.
  • Exclude local database files by pasting these patterns:
*.duckdb
*.wal

What does this file protect?

The patterns exclude DuckDB database files and write-ahead log files from version control. The repository keeps reproducible source evidence without publishing the local database.

  • Save .gitignore.
  • Confirm that .gitignore appears in the workspace root.
  • Confirm that compliance.duckdb is absent from the files you plan to upload.

✔️ Awesome, I've got everything!

Great. Your workspace now contains the documentation and database exclusion rules.

ⓧ I'd like to double check the full code

*.duckdb
*.wal
Publish the sanitized repository

Publishing a public security repository can feel risky. This upload contains synthetic evidence and excludes the local database.

The repository gives reviewers one place to inspect the control logic and the before-and-after proof.

  • Return to GitHub in your browser.
  • Select New repository.
  • Select your account under Owner.
  • Enter aws-iam-compliance-triage as the repository name.
  • Select Public as the visibility.
  • Leave the repository without pre-populated files.
  • Click Create repository.

You should see the empty repository page for aws-iam-compliance-triage.

Why start with an empty repository?

Your local workspace already contains the final README and ignore rules. Starting empty avoids conflicts with files generated by GitHub.

  • Click Add file on the repository page.
  • Click Upload files.
  • Drag README.md, .gitignore, data, sql, and output into the upload area.
  • Confirm that compliance.duckdb is absent from the upload list.
  • Enter Publish compliance triage evidence as the commit message.
  • Click Propose changes.

GitHub processes the upload and returns you to the repository. The repository now holds the sanitized source files and evidence exports.

Are files missing from the upload?

  • Return to Add file.
  • Select Upload files.
  • Upload only the missing sanitized files from the existing workspace.

Still stuck? Help me verify which project files belong in my public GitHub repository.

Before you refresh the repository, which files should prove that another reviewer can reproduce the controls and inspect both evidence states?

  • Refresh the GitHub repository page.
  • Confirm that README.md appears in the repository.
  • Confirm that .gitignore appears in the repository.
  • Confirm that the data folder contains both credential reports.
  • Confirm that the sql folder contains all four SQL scripts.
  • Confirm that the output folder contains both findings exports.
  • Confirm that compliance.duckdb does not appear in the repository.

You should see the rendered README with the project files listed above it. The published repository proves a complete workflow from detection through verified closure.

That closes the governance loop. Your controls now detect risk, preserve evidence, and prove that remediation reduced four findings to zero.

Secret mission

Detect Unused Credentials

A recently rotated access key can still sit unused for months. Extend your normalized evidence model with usage dates, then detect the abandoned credential hidden in the before-remediation data.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your evidence workflow, pause public sharing, or delete both copies. Your local DuckDB workspace and GitHub Free repository have no ongoing project charges.

Resources you used:

  • Local workspace: aws-iam-compliance-triage, including compliance.duckdb, the synthetic evidence in data, the SQL source in sql, and the generated evidence in output.
  • Published copy: The public aws-iam-compliance-triage repository containing the sanitized project files.

Keep everything running

No action is needed. Choose this option if you want the evidence workflow to remain ready for future control testing.

  • Keep compliance.duckdb so the tables and views remain available.
  • Keep output/findings_before.csv and output/findings_after.csv as your before-and-after evidence.
  • Leave the sanitized aws-iam-compliance-triage repository public for review.
  • Keep DuckDB CLI installed for future local analysis.

No local process remains active after the DuckDB CLI scripts exit. No cloud resource is waiting in the background.

Pause - I'll come back to this later

Keep the local workspace while removing public access to the repository. No local process needs to be stopped.

  • Keep the aws-iam-compliance-triage workspace on your computer.
  • Change the visibility of the aws-iam-compliance-triage repository to private through its repository visibility settings.
  • Confirm the private repository remains available when you are signed in.

Your database, scripts, synthetic evidence, and exports remain ready for your return.

Delete - I don't want to use this again

Deleting both copies is permanent, so choose this option only when you no longer need the workflow. No AWS cleanup is required because the project created no live AWS resources.

Delete the published copy:

  • Return to the public aws-iam-compliance-triage repository from earlier.
  • Delete the repository through its repository deletion settings.
  • Complete the confirmation prompt for aws-iam-compliance-triage.

The former repository URL should no longer display the project.

Delete the local copy:

  • Close the Visual Studio Code workspace from earlier.
  • Delete the aws-iam-compliance-triage folder through File Explorer.
  • Refresh File Explorer to verify the folder has been removed.

The project folder should no longer appear in File Explorer. DuckDB CLI remains installed separately for future local SQL work.

Nice Work!

Nice Work!

You did it! Your local compliance triage engine now turns synthetic AWS IAM evidence into an ordered remediation queue.

Your reproducible SQL tests prove control closure with four findings before remediation. The final retest shows zero findings afterward.

Your sanitized GitHub repository makes the complete workflow easy to review.

You've learned how to:

  • Built a local compliance triage engine with DuckDB that converts synthetic credential evidence into prioritized findings with owners plus recommended actions.
  • Exposed a realistic completeness failure with the deliberately incomplete slot-1 query. Fixed it by normalizing both access-key slots with UNION ALL.
  • Published reviewable governance evidence that demonstrates the complete detection-to-retest workflow.
  • Completed the optional Secret Mission by extending the normalized model with last-used evidence. The new IAM.8 check identifies erin because her active key had been unused for at least 90 days.

Ready to quiz yourself?