MySQL Grant Generator

MySQL GRANT generator with least privilege by default

The generator prepares CREATE USER and GRANT statements for one account, host, and MySQL database. Only SELECT is enabled by default because every additional permission should follow from a concrete application requirement. The database name is quoted as an identifier, while the username, host, and password are handled as SQL literals.

A dedicated application account limits the impact of leaked credentials. Instead of connecting a service as root, you can give it access to one database and only the required operations. Prepare web server rules with the .htaccess generator, describe container services with the Docker Compose generator, and schedule administrative work with the crontab generator.

Identifiers and literals are different SQL elements

The database name in the ON clause is an identifier and receives backticks: `app_db`.*. A MySQL account name consists of two literals: 'app_user'@'localhost'. The generator does not accept a real password: it always emits a fixed placeholder quoted as an SQL literal. User, host, and database values pass strict validation before quoting, while control characters and syntax fragments are rejected.

CREATE USER 'app_user'@'localhost'
  IDENTIFIED BY 'CHANGE_ME_STRONG_PASSWORD';
GRANT SELECT ON `app_db`.* TO 'app_user'@'localhost';

User and database names deliberately use a conservative format: ASCII letters, digits, and underscores. This restriction reduces confusion between a name and SQL syntax. Quoting should not be removed merely because the current value contains no special character.

Least privilege instead of ALL PRIVILEGES

SELECT is sufficient for a reporting process and is a safe starting point. A CRUD application may additionally require INSERT, UPDATE, and DELETE. Permissions such as CREATE, DROP, ALTER, and INDEX normally belong to a separate migration account. REFERENCES covers foreign-key constraints, while EXECUTE covers stored routines. The generator does not offer ALL PRIVILEGES, keeping each choice explicit.

The MySQL account host

In MySQL, 'app_user'@'localhost' and 'app_user'@'%' are different accounts. localhost limits access to the database server, a specific IP address or DNS name narrows the network source, while % accepts every host. The generator permits a standalone % as an explicit decision but rejects mixed wildcard patterns, whitespace, and SQL fragments. In production, choose the most precise host and add firewall restrictions.

Why WITH GRANT OPTION and FLUSH PRIVILEGES are absent

WITH GRANT OPTION would let the new account delegate its own permissions to other users. A normal application account should not have that ability, so the generator never emits the clause. Statements managed by the MySQL server do not require FLUSH PRIVILEGES after CREATE USER or GRANT either; adding it would be redundant and may require another global administrative privilege.

The generator uses CREATE USER without IF NOT EXISTS. If the account already exists, the server reports an error rather than suggesting that the entered password was applied. Use an intentional ALTER USER for an existing account and inspect current permissions with SHOW GRANTS.

A safe usage procedure

1. Create a separate account for one application or process and select the narrowest possible host.
2. Choose one database and only the operations required by the application's actual queries.
3. Generate the statements, then replace the placeholder with a strong secret only in a protected local file.
4. Review the result as an administrator, run it in a controlled session, and remove secrets from shell history.
5. Confirm the result with SHOW GRANTS FOR 'user'@'host' and a connection test using the application account.

Frequently asked questions