-- MySQL AI Access Security Kit
-- Updated: 2026-07-19
-- Source: https://datamcp.app/resources/mysql-ai-access-security-kit
--
-- Target: MySQL 8.0 account and privilege syntax.
-- Review every account, host, identifier, and statement before use.
-- Run account administration as an authorized database administrator.
-- Do not store a real production password in this file.
--
-- Official references:
-- https://dev.mysql.com/doc/refman/8.0/en/create-user.html
-- https://dev.mysql.com/doc/refman/8.0/en/grant.html
-- https://dev.mysql.com/doc/refman/8.0/en/show-grants.html
-- https://dev.mysql.com/doc/refman/8.0/en/revoke.html
-- https://dev.mysql.com/doc/refman/8.0/en/account-locking.html

-- Replace every example value before running:
--   account:  'ai_reader'@'203.0.113.10'
--   database: app_production
--   tables:   customer_summary, ticket_summary, subscription_status
--
-- 203.0.113.10 is a documentation-only address. Replace it with the exact
-- client host, provider-required host value, or reviewed network boundary.
-- Avoid omitting the host: MySQL treats an omitted host as '%'.

-- 1. Create a dedicated account with no administrative privileges.
-- Replace the placeholder password through an approved secret workflow.
CREATE USER 'ai_reader'@'203.0.113.10'
  IDENTIFIED BY 'REPLACE_WITH_A_GENERATED_SECRET'
  REQUIRE SSL
  WITH MAX_USER_CONNECTIONS 5;

-- 2. Grant SELECT only on an explicit list of reviewed tables.
-- Do not change this to ON *.*. Avoid ON database.* unless every current and
-- future object in that database has been approved for this account.
GRANT SELECT ON `app_production`.`customer_summary`
  TO 'ai_reader'@'203.0.113.10';

GRANT SELECT ON `app_production`.`ticket_summary`
  TO 'ai_reader'@'203.0.113.10';

GRANT SELECT ON `app_production`.`subscription_status`
  TO 'ai_reader'@'203.0.113.10';

-- Optional: expose only reviewed columns from one table.
-- Column-level SELECT can reduce accidental exposure, but review application
-- behavior and views separately before relying on it as the only boundary.
-- GRANT SELECT (`id`, `display_name`, `plan_status`)
--   ON `app_production`.`customers`
--   TO 'ai_reader'@'203.0.113.10';

-- Optional write example. Keep commented out until one documented workflow
-- requires it and denial tests have been updated.
-- GRANT INSERT, UPDATE
--   ON `app_production`.`agent_drafts`
--   TO 'ai_reader'@'203.0.113.10';

-- 3. Inspect account properties and every effective grant.
-- SHOW CREATE USER includes TLS, resource-limit, and lock configuration.
SHOW CREATE USER 'ai_reader'@'203.0.113.10';
SHOW GRANTS FOR 'ai_reader'@'203.0.113.10';

-- If SHOW GRANTS lists roles, inspect the privileges provided by each role.
-- A fresh account from this kit receives no role and no GRANT OPTION.
-- SHOW GRANTS FOR 'ai_reader'@'203.0.113.10' USING 'role_name';

-- 4. Run these validation statements one at a time while connected through
-- the same account and network path the AI or MCP integration will use.
-- Replace identifiers and use disposable fixtures for denial tests.
--
-- Confirm which account row MySQL actually matched.
-- SELECT USER() AS supplied_identity, CURRENT_USER() AS effective_account;
-- SHOW GRANTS FOR CURRENT_USER();
--
-- Expected: allowed.
-- SELECT * FROM `app_production`.`ticket_summary` LIMIT 5;
--
-- Expected: access denied.
-- SELECT * FROM `app_production`.`users_private` LIMIT 1;
-- Ask an administrator to create a disposable agent_permission_fixture first,
-- then leave it ungranted to this account for the following checks.
-- INSERT INTO `app_production`.`agent_permission_fixture` (`id`) VALUES (1);
-- UPDATE `app_production`.`agent_permission_fixture`
--   SET `id` = `id` WHERE 1 = 0;
-- DELETE FROM `app_production`.`agent_permission_fixture` WHERE 1 = 0;
-- CREATE TABLE `app_production`.`agent_permission_created` (`id` BIGINT);
-- DROP TABLE `app_production`.`agent_permission_fixture`;
-- CALL `app_production`.`admin_maintenance`();
--
-- Expected: denied until a new table receives an explicit reviewed grant.
-- SELECT * FROM `app_production`.`new_table_after_review` LIMIT 1;

-- 5. Emergency disablement and revocation examples.
-- Coordinate these actions because they affect every client using the account.
-- ACCOUNT LOCK prevents new connections. Review and terminate existing
-- sessions separately when incident containment requires it.
--
-- Stop new direct logins:
-- ALTER USER 'ai_reader'@'203.0.113.10' ACCOUNT LOCK;
--
-- Remove one table grant:
-- REVOKE SELECT ON `app_production`.`ticket_summary`
--   FROM 'ai_reader'@'203.0.113.10';
--
-- Remove all direct privileges and GRANT OPTION. This statement does not
-- revoke roles, so inspect SHOW GRANTS and revoke any listed role separately.
-- REVOKE ALL PRIVILEGES, GRANT OPTION
--   FROM 'ai_reader'@'203.0.113.10';
--
-- Re-enable only after grant review and credential rotation:
-- ALTER USER 'ai_reader'@'203.0.113.10'
--   IDENTIFIED BY 'REPLACE_WITH_A_NEW_GENERATED_SECRET'
--   ACCOUNT UNLOCK;
