MySQL User Accounts and Authentication

MySQL security starts with user account management. Each user account is identified by a username and the host from which they can connect. Understanding how to create, modify, and remove user accounts is the foundation of database security.

Creating Users

The CREATE USER statement creates a new account. The account identifier is in the form 'user'@'host'. Using 'localhost' restricts the account to local connections only, while '%' allows connections from any host.

Authentication Plugins

MySQL 8.0 uses caching_sha2_password as the default authentication plugin. Older clients may need mysql_native_password. Always use strong, randomly generated passwords for database accounts.

Use 'localhost' instead of '%' whenever possible
Create separate accounts for each application
Never use the root account in application code
Rotate passwords regularly

Example

-- Create a new user restricted to localhost
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';

-- Create a user that can connect from any host (less secure)
CREATE USER 'report_user'@'%' IDENTIFIED BY 'AnotherStr0ng!';

-- Change a user's password
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewP@ssword2025';

-- Remove a user
DROP USER IF EXISTS 'old_user'@'localhost';

SQL

Full Editor

Pro Tip

Always restrict database users to the minimum required host — prefer localhost over wildcard %.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned