Introduction
MySQL is a free software for managing databases that store data in tables. It's often used with Linux, Apache, and PHP, making up the LAMP stack. As of now, it's the most popular free database software.
This guide will show you how to make a new MySQL user and give them different permissions.
Prerequisites To use this guide, you need access to a MySQL database. We assume you have MySQL on an Ubuntu 20.04 server, but the instructions work for other setups too.
If you don't have MySQL, you can set it up by following a guide on installing MySQL. The steps to create a user and give permissions are similar across different systems.
You can also use a MySQL database from a cloud service. For example, MyDomains.tech offers Managed Databases, and you can learn how to set one up in their documentation.
Throughout this guide, parts of commands that you need to customize are highlighted.
Creating a New User MySQL automatically makes a 'root' user when installed. This user can do anything in the database, so it's better for admin tasks only. This part explains how to use the root user to create a new user and assign privileges.
In Ubuntu with MySQL 5.7 or newer, the root user uses the auth_socket plugin for login, not a password. This means you should use 'sudo mysql' to log in as the root user. If your root user has a password, use 'mysql -u root -p' instead.
To make a new user, use the CREATE USER command:
CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
Replace 'username' and 'host' with your chosen username and where they'll connect from (like 'localhost'). For authentication, auth_socket doesn't need a password but limits remote access. The default is caching_sha2_password, which is secure and uses a password.
Here's how to make a user with caching_sha2_password:
CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
If using PHP, you might prefer mysql_native_password:
CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
If unsure, start with caching_sha2_plugin and change later with ALTER USER.
After making your user, you need to give them permissions.
Granting User Permissions To give a user permissions, use the GRANT command:
GRANT PRIVILEGE ON database.table TO 'username'@'host';
Replace PRIVILEGE with the actions they can do, like CREATE or SELECT, and specify the database and table. You can give multiple permissions at once.
For example, to give a user many permissions globally, use:
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on . TO 'sammy'@'localhost' WITH GRANT OPTION;
WITH GRANT OPTION lets them give their permissions to others.
Be cautious with ALL PRIVILEGES, as it gives full control like the root user:
GRANT ALL PRIVILEGES ON . TO 'sammy'@'localhost' WITH GRANT OPTION;
After GRANT or CREATE USER, you don't usually need FLUSH PRIVILEGES, but it won't hurt.
To remove a permission, use REVOKE:
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';
To check a user's permissions, use SHOW GRANTS:
SHOW GRANTS FOR 'username'@'host';
To delete a user, use DROP USER:
DROP USER 'username'@'localhost';
To exit MySQL, type 'exit'. To log in as your new user later, use:
mysql -u sammy -p
Conclusion You've learned how to add and set permissions for MySQL users. You can now explore more advanced MySQL settings and configurations.
