Users, privileges & security

CREATE USER, GRANT, REVOKE, roles, least privilege, authentication plugins and SQL injection.

A database usually holds a company's most valuable data, so it's a prime target for attackers. MySQL security comes down to three ideas: authenticate every connection, give each account the least privilege it needs, and keep untrusted input out of SQL code. This lesson covers accounts, privileges, roles and the habits that keep a server safe.

Accounts are user + host#

SQL

CREATE USER 'app'@'localhost' IDENTIFIED BY 'S3cure-App-Pass!';
CREATE USER 'report'@'10.0.0.%' IDENTIFIED BY 'An0ther-Str0ng-1!';
CREATE USER 'alice'@'%' IDENTIFIED BY 'Temp-Pass-123!' PASSWORD EXPIRE;

The host part says where the user may connect from: localhost (this machine), an IP, a pattern like 10.0.0.%, a hostname, or % (anywhere).
'app'@'localhost' and 'app'@'%' are different accounts, each with its own password and privileges. When several match, MySQL uses the most specific host.
PASSWORD EXPIRE forces Alice to choose a new password at first login.

List accounts:

SQL

SELECT user, host, plugin, account_locked FROM mysql.user
WHERE user IN ('app', 'report', 'alice') ORDER BY user;

Output

+--------+-----------+-----------------------+----------------+
| user   | host      | plugin                | account_locked |
+--------+-----------+-----------------------+----------------+
| alice  | %         | caching_sha2_password | N              |
| app    | localhost | caching_sha2_password | N              |
| report | 10.0.0.%  | caching_sha2_password | N              |
+--------+-----------+-----------------------+----------------+

MySQL 8's default authentication plugin is caching_sha2_password. It's secure, but very old client libraries may not support it. (In 8.4 the legacy mysql_native_password plugin is disabled by default.)

Managing accounts#

SQL

ALTER USER 'app'@'localhost' IDENTIFIED BY 'N3w-App-Pass!';      -- change a password
ALTER USER 'alice'@'%' ACCOUNT LOCK;                              -- block logins without deleting
ALTER USER 'alice'@'%' ACCOUNT UNLOCK;
RENAME USER 'report'@'10.0.0.%' TO 'reporting'@'10.0.0.%';
ALTER USER 'alice'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;  -- lock for 1 day after 5 failures

Remove an account with DROP USER 'alice'@'%';. Users change their own password with ALTER USER USER() IDENTIFIED BY '...';.

Privileges with GRANT and REVOKE#

Privileges can be granted at several levels:

Level	Syntax	Example
Global	`ON .`	administrators only
Database	`ON shop.*`	an application's database
Table	`ON shop.orders`	a reporting user
Column	`SELECT (id, name) ON shop.customers`	hide sensitive columns
Routine	`EXECUTE ON PROCEDURE shop.transfer`	call a procedure only

SQL

CREATE DATABASE IF NOT EXISTS shop;
CREATE TABLE IF NOT EXISTS shop.customers (id INT PRIMARY KEY, name VARCHAR(30), email VARCHAR(60), card_last4 CHAR(4));

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'localhost';
GRANT SELECT (id, name) ON shop.customers TO 'reporting'@'10.0.0.%';

SHOW GRANTS FOR 'app'@'localhost';
SHOW GRANTS FOR 'reporting'@'10.0.0.%';

Output

+-----------------------------------------------------------------------+
| Grants for app@localhost                                              |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app`@`localhost`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `app`@`localhost` |
+-----------------------------------------------------------------------+
+-----------------------------------------------------------------------------+
| Grants for reporting@10.0.0.%                                               |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `reporting`@`10.0.0.%`                                |
| GRANT SELECT (`id`, `name`) ON `shop`.`customers` TO `reporting`@`10.0.0.%` |
+-----------------------------------------------------------------------------+

GRANT USAGE ON *.* just means "can log in". Take privileges away with REVOKE:

SQL

REVOKE DELETE ON shop.* FROM 'app'@'localhost';
SHOW GRANTS FOR 'app'@'localhost';

Output

+---------------------------------------------------------------+
| Grants for app@localhost                                      |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app`@`localhost`                       |
| GRANT SELECT, INSERT, UPDATE ON `shop`.* TO `app`@`localhost` |
+---------------------------------------------------------------+

Common privileges: SELECT, INSERT, UPDATE, DELETE (data); CREATE, ALTER, DROP, INDEX, REFERENCES (schema); EXECUTE, CREATE ROUTINE, TRIGGER, EVENT (programs); PROCESS, RELOAD, REPLICATION SLAVE (admin). MySQL 8 also adds fine-grained dynamic privileges such as BACKUP_ADMIN and CONNECTION_ADMIN. ALL PRIVILEGES grants everything at that level except GRANT OPTION, which lets a user pass privileges on.

Roles (MySQL 8.0+)#

Instead of granting the same list of privileges to dozens of accounts, grant them to a role and grant the role to people:

SQL

CREATE ROLE 'shop_read', 'shop_write';
GRANT SELECT ON shop.* TO 'shop_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'shop_write';

CREATE USER 'dev1'@'%' IDENTIFIED BY 'Dev-One-Pass-1!';
GRANT 'shop_read', 'shop_write' TO 'dev1'@'%';
SET DEFAULT ROLE ALL TO 'dev1'@'%';          -- activate the roles automatically at login

SHOW GRANTS FOR 'dev1'@'%' USING 'shop_read', 'shop_write';

Output

+----------------------------------------------------------------+
| Grants for dev1@%                                              |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`%`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `dev1`@`%` |
| GRANT `shop_read`@`%`,`shop_write`@`%` TO `dev1`@`%`           |
+----------------------------------------------------------------+

Roles are inactive until activated. SET DEFAULT ROLE (or SET ROLE within a session) does that. Change a role's privileges once and every member is updated.

Least privilege in practice#

A typical production setup:

Account	Privileges
`app@'10.0.1.%'` (the web app)	`SELECT, INSERT, UPDATE, DELETE` on its own database only
`migrate@'10.0.2.5'` (deployments)	adds `CREATE, ALTER, DROP, INDEX, REFERENCES`
`report@'10.0.3.%'` (BI)	`SELECT` on a few tables or views, ideally on a replica
`backup@localhost`	`SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD, REPLICATION CLIENT`
`root@localhost`	everything, never used by applications

Rules of thumb:

Never let applications connect as root. If the app is compromised, so is every database on the server.
No % hosts for powerful accounts. Restrict them to specific IPs or localhost.
One account per application or service, so you can audit and revoke individually.
Review grants regularly (SHOW GRANTS, SELECT * FROM mysql.user).

SQL injection#

SQL injection happens when user input is concatenated into SQL text. Imagine this login code:

Python

# ❌ NEVER do this
sql = "SELECT * FROM users WHERE email = '" + email + "' AND password_hash = '" + pw_hash + "'"

If an attacker types the email ' OR 1=1 -- , the query becomes:

SQL

SELECT * FROM users WHERE email = '' OR 1=1 -- ' AND password_hash = '...'

OR 1=1 matches every row and -- comments out the password check, so the attacker logs in as the first user. With a different payload, they could read other tables or delete data.

The fix is parameterised queries (prepared statements). The SQL and the data travel separately, so input can never become code:

Python

# ✅ Python (mysql-connector / PyMySQL): %s placeholders, values passed separately
cur.execute("SELECT id, name FROM users WHERE email = %s", (email,))

Java

// ✅ Java (JDBC)
PreparedStatement ps = conn.prepareStatement("SELECT id, name FROM users WHERE email = ?");
ps.setString(1, email);

The same mechanism exists in SQL itself, which is useful inside stored procedures:

SQL

SET @email = ''' OR 1=1 -- ';
PREPARE stmt FROM 'SELECT id, name FROM shop.customers WHERE email = ?';
EXECUTE stmt USING @email;      -- safely finds nobody
DEALLOCATE PREPARE stmt;

Output

Empty set (0.00 sec)

Placeholders work for values only, not for table or column names. If those must be dynamic, check them against an allow-list in your code. Least privilege is your second line of defence: an injected DROP TABLE fails if the account has no DROP privilege.

Server hardening checklist#

Run mysql_secure_installation on new servers (removes anonymous users and the test database).
Network: bind to a private interface (bind-address = 10.0.0.5, or 127.0.0.1 if only local apps connect), and firewall port 3306 so only app servers can reach it. Never expose MySQL directly to the internet.
Encryption in transit: MySQL 8 sets up TLS automatically. Require it per account with ALTER USER 'app'@'%' REQUIRE SSL;, or for everyone with require_secure_transport = ON.
Encryption at rest: InnoDB supports tablespace encryption (ENCRYPTION='Y') with a keyring component.
Passwords: enable the validate_password component for strength rules, and never store application passwords in plain text. Store password hashes made with bcrypt or Argon2 in your application, not with MySQL's MD5()/SHA1().
Secrets: keep DB credentials in environment variables or a secrets manager, not in Git.
Files: secure_file_priv limits where LOAD DATA and SELECT ... INTO OUTFILE can read and write.
Patching and auditing: apply minor updates promptly, watch the error log for failed logins, and consider an audit-log plugin for regulated data.

Cleaning up#

SQL

DROP USER IF EXISTS 'app'@'localhost', 'reporting'@'10.0.0.%', 'alice'@'%', 'dev1'@'%';
DROP ROLE IF EXISTS 'shop_read', 'shop_write';

What's next#

Security also means being able to recover. Next: backing up and restoring MySQL with mysqldump and binary logs.

Accounts are user + host#

Managing accounts#

Privileges with GRANT and REVOKE#

Roles (MySQL 8.0+)#

Least privilege in practice#

SQL injection#

Server hardening checklist#

Cleaning up#

What's next#

Quick quiz