Users and Permissions

Introduction

MySQL users authenticate clients; privileges control what they can do—globally, per database, table, or column. This chapter creates users, grants least privilege, introduces roles (MySQL 8), and covers password hygiene so dev laptops and servers stay secure.

Prerequisites

MySQL Configuration and Client Tools
Login as root or admin for GRANT exercises

Create User

MySQL 8 create with password:

sql

CREATE USER 'app_reader'@'localhost'
  IDENTIFIED BY 'StrongPass123!';

Remote host pattern (restrict in production):

sql

CREATE USER 'app_reader'@'192.168.1.%'
  IDENTIFIED BY 'StrongPass123!';

Code explanation:

'user'@'host' — account is pair; 'app'@'localhost' ≠ 'app'@'%'
localhost uses socket on Linux often; TCP use 127.0.0.1

List users:

sql

SELECT user, host FROM mysql.user;

GRANT Privileges

Read-only on one database:

sql

GRANT SELECT ON blog_dev.* TO 'app_reader'@'localhost';

Read/write app user:

sql

CREATE USER 'app_writer'@'localhost' IDENTIFIED BY 'WriterPass456!';
 
GRANT SELECT, INSERT, UPDATE, DELETE ON blog_dev.* TO 'app_writer'@'localhost';

No schema changes for app user—omit CREATE, DROP, ALTER.

Apply:

sql

FLUSH PRIVILEGES;

Test login:

bash

mysql -u app_reader -p blog_dev

sql

SELECT COUNT(*) FROM users;
-- INSERT fails for app_reader

Privilege Levels

Level	Example
Global	`GRANT SELECT ON .`
Database	`GRANT SELECT ON blog_dev.*`
Table	`GRANT SELECT ON blog_dev.posts`
Column	`GRANT SELECT (title) ON blog_dev.posts`

Common privileges:

Privilege	Allows
SELECT	Read rows
INSERT, UPDATE, DELETE	DML
CREATE, ALTER, DROP	DDL
INDEX	Index management
EXECUTE	Run procedures/functions
ALL PRIVILEGES	Everything on scope—avoid for apps

Show grants:

sql

SHOW GRANTS FOR 'app_reader'@'localhost';

REVOKE

sql

REVOKE INSERT ON blog_dev.* FROM 'app_writer'@'localhost';

Drop user:

sql

DROP USER IF EXISTS 'app_reader'@'localhost';

Roles (MySQL 8.0)

Group privileges:

sql

CREATE ROLE 'role_blog_read', 'role_blog_write';
 
GRANT SELECT ON blog_dev.* TO 'role_blog_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON blog_dev.* TO 'role_blog_write';
 
GRANT 'role_blog_read' TO 'app_reader'@'localhost';
SET DEFAULT ROLE 'role_blog_read' TO 'app_reader'@'localhost';

Activate role in session:

sql

SET ROLE 'role_blog_read';

Password and Security

Use long random passwords; store in secrets manager
mysql_secure_installation removes anonymous users
Disable remote root login
caching_sha2_password default on 8.0—match client support

Password expire (policy example):

sql

ALTER USER 'app_writer'@'localhost'
  PASSWORD EXPIRE INTERVAL 180 DAY;

Warning

Never Commit Passwords to Git

Use env vars in apps—see Git .gitignore.

Production Patterns

Account	Scope
App runtime	DML on app DB only
Migration user	DDL on deploy
Human admin	Bastion + strong auth, not shared

FAQ

Access denied after GRANT?

Wrong host in connection—'user'@'localhost' vs 'user'@'127.0.0.1' differ.

GRANT without CREATE USER?

MySQL 8 removed implicit user create—CREATE USER first.

Root vs sudo mysql on Ubuntu?

Socket auth for root—set password auth if tools need TCP.

Privilege escalation?

Only grant minimum; audit SHOW GRANTS.

Docker MYSQL_USER?

Entrypoint creates user with grants on MYSQL_DATABASE only.

SSL required connection?

REQUIRE SSL on user—advanced hardening.