MySQL User Management: A Comprehensive Guide

Learn how to create and configure MySQL user permissions on any system, ensuring robust database management and security.

Prerequisites

Access to a MySQL database on a server is required, ideally with MySQL installed on Ubuntu 22.04. This guide is universally applicable for MySQL database management.

Creating a New User

Utilize the root MySQL account for administrative tasks only. Create additional users for database management with restricted privileges for enhanced security.

sudo mysql

Or, for password authentication:

mysql -u root -p

Create a user with:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Granting a User Permissions

Define specific actions the new user can perform on the database and tables:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

For global privileges:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Conclusion

This guide provides the foundational steps for creating MySQL users and setting their permissions, crucial for secure and efficient database management.