A database root account should not be an application’s everyday credential. Compromise of one application can otherwise expose unrelated schemas or administrative operations. A better design separates runtime queries from schema migrations and grants only the operations actually required. Least privilege must be tested; randomly removing permissions during live traffic is not a reliable security plan.
1. Inventory requirements and version
The example targets MySQL 8.0/8.4; check MariaDB and older clients independently. Determine whether the workload needs only CRUD or also stored procedures, temporary tables or other operations. WordPress plugins may require CREATE or ALTER during installation and upgrades, so the four sample privileges are not a universal WordPress configuration. Use a separate, controlled migration account for reviewed schema changes.
2. Create an account with a precise host
A MySQL account includes both user and host. Replace the example IP with the actual application source; NAT, proxies and socket connections can change matching. REQUIRE SSL assumes working TLS configuration. Do not execute the sample password: provision a unique value securely and keep it out of Git, chat and shell arguments. Review SQL history and administrative logging for credential exposure as well.
-- Administrative session; replace all placeholders securely.
CREATE USER 'app_runtime'@'10.20.30.40'
IDENTIFIED BY 'REPLACE_WITH_UNIQUE_SECRET' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE
ON appdb.* TO 'app_runtime'@'10.20.30.40';
SHOW GRANTS FOR 'app_runtime'@'10.20.30.40';
3. Keep the grant scope narrow
A grant on appdb.* covers that schema, including future tables. Consider table-level grants when the requirement is narrower. Avoid global ALL, GRANT OPTION and FILE unless explicitly justified. Existing global privileges or roles can defeat an apparently narrow grant; a new account is often easier to audit. Normal CREATE USER and GRANT statements do not require FLUSH PRIVILEGES.
4. Test from the actual application host
Use a password prompt or secret store, never -pPASSWORD. The connection example requires a valid server certificate and a real CA file. USER shows the connection identity, while CURRENT_USER identifies the account MySQL matched for authorization. Test the application’s actual reads and writes using approved test data. SHOW GRANTS alone does not prove every application feature works.
mysql --host=db.example.net --user=app_runtime --password --ssl-mode=VERIFY_IDENTITY --ssl-ca=/path/to/trusted-ca.pem
-- Inside the resulting session:
SELECT USER(), CURRENT_USER();
SHOW GRANTS;
5. Cut over with a rollback route
Test in staging before changing the runtime credential. Old pooled connections may continue using the previous account, so establish new connections. On failure, use the protected previous credential and diagnose the specific missing privilege rather than granting ALL. Retire broad credentials only after all required paths pass. Acceptance requires permitted operations to work, out-of-scope access to be denied, and no new application permission errors.
