User Management and Security

Securing a MySQL installation means controlling who can connect, what they can do, and how they authenticate. Proper user management is a critical part of running MySQL in production.

Creating Users

CREATE USER 'alice'@'localhost' IDENTIFIED BY 'StrongP@ssword1!';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'AnotherStr0ngPass!';

The host part (localhost, %, 192.168.1.%) controls which hosts the user can connect from. % is a wildcard matching any host.

Granting Privileges

Privileges are granted at the global, database, table, or column level:

-- All privileges on a specific database
GRANT ALL PRIVILEGES ON myapp.* TO 'alice'@'localhost';

-- Read-only access on a specific table
GRANT SELECT ON myapp.products TO 'appuser'@'%';

-- Execute a specific stored procedure
GRANT EXECUTE ON PROCEDURE myapp.GetUserPosts TO 'appuser'@'%';

FLUSH PRIVILEGES;

Revoking Privileges

REVOKE INSERT, UPDATE ON myapp.* FROM 'appuser'@'%';

Viewing Privileges

SHOW GRANTS FOR 'alice'@'localhost';

The Principle of Least Privilege

Application service accounts should have only the privileges they need. A typical web application needs SELECT, INSERT, UPDATE, and DELETE on its own database — not DROP, CREATE, or SUPER. Reserve highly privileged accounts for administrative tasks performed by humans.

Altering and Dropping Users

-- Change a user's password
ALTER USER 'alice'@'localhost' IDENTIFIED BY 'NewP@ssword2!';

-- Rename a user
RENAME USER 'alice'@'localhost' TO 'alice_admin'@'localhost';

-- Remove a user entirely
DROP USER 'alice_admin'@'localhost';

Authentication Plugins

MySQL 8 defaults to the caching_sha2_password authentication plugin, which is more secure than the older mysql_native_password. Some older clients may require:

ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password1!';

Additional Security Practices

• Always set a strong root password and never use root for application connections.
• Restrict remote root login — root should only connect from localhost.
• Enable the MySQL audit log plugin in production to record who did what and when.
• Use TLS/SSL for connections from application servers to the MySQL host.
• Regularly review user privileges with SELECT * FROM mysql.user and remove unused accounts.

A well-secured MySQL installation significantly reduces the blast radius of any application-level vulnerability or credential leak.