Introduction
MySQL is the world’s leading open-source relational database management system (RDBMS). Widely used in the LAMP stack (Linux, Apache, MySQL, PHP), MySQL powers numerous web applications. This guide will walk you through the steps to create a new MySQL user and grant the necessary permissions.
Table of Contents
Section | Description |
---|---|
1. Prerequisites | Ensure you have access to a MySQL database and set up your environment. |
2. Creating a New MySQL User | Learn how to create a new MySQL user with password authentication. |
3. Accessing MySQL as Root | Instructions for accessing the MySQL shell with root privileges. |
4. Creating a User with Password Authentication | Steps for creating a user using MySQL’s default authentication method. |
5. Granting Permissions to a MySQL User | How to grant specific privileges to a user for performing tasks. |
6. Granting Privileges | Syntax and examples for granting permissions to MySQL users. |
7. Applying the Changes | Learn about applying permissions and reloading the grant tables. |
8. Revoking Privileges | Instructions for revoking user permissions in MySQL. |
9. Viewing User Permissions | How to view a user’s current privileges using SHOW GRANTS . |
10. Deleting a MySQL User | Steps for deleting a MySQL user using the DROP USER command. |
11. Logging in as the New User | How to log in as the new MySQL user with password authentication. |
12. Conclusion | Summary of the process and further steps for optimizing MySQL. |
SEO-Optimized Guide: How to Create a MySQL User and Grant Permissions
Introduction
MySQL is the world’s leading open-source relational database management system (RDBMS). Widely used in the LAMP stack (Linux, Apache, MySQL, PHP), MySQL powers numerous web applications. This guide will walk you through the steps to create a new MySQL user and grant the necessary permissions.
Prerequisites
Before you begin, ensure you have access to a MySQL database. This tutorial assumes your database is running on an Ubuntu 20.04 VPS, though the instructions should apply regardless of your database environment.
If you don’t have a MySQL database installed yet, refer to our guide on How to Install MySQL for the steps to set up MySQL on your server. Alternatively, you can deploy a MySQL managed database via your preferred cloud provider.
Note: Any customizable example commands in this guide will be highlighted like this.
Creating a New MySQL User
By default, MySQL creates a root user with complete administrative privileges over all databases, tables, and users. For security reasons, it’s best to create a separate user for day-to-day database operations. Here’s how to do it.
Accessing MySQL as Root
On systems running MySQL 5.7 and higher, the root MySQL user uses the auth_socket
plugin by default. To access MySQL as the root user, you need to run the following command with sudo
:
sudo mysql
Note: If your root MySQL user is set to authenticate with a password instead, use this command to access the MySQL shell:
mysql -u root -p
Creating a New User
Once you’re inside the MySQL prompt, you can create a new user using the CREATE USER
statement. The general syntax is as follows:
CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
- Replace
username
with your desired MySQL username. - Set
host
tolocalhost
if the user will access the database locally. - Choose an authentication plugin or leave it to default (
caching_sha2_password
), which is highly secure.
Example: Create a User with Password Authentication
The following command creates a MySQL user named sammy
with password-based authentication:
CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
Note: If you’re using PHP-based applications like phpMyAdmin, you may need to create the user with the older
mysql_native_password
plugin for compatibility:
CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
If necessary, you can modify the user’s authentication plugin later:
ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Granting Permissions to a MySQL User
After creating a new user, you’ll want to grant them appropriate permissions to perform tasks on your MySQL databases.
Granting Privileges
The syntax for granting privileges is as follows:
GRANT PRIVILEGE ON database.table TO 'username'@'host';
- PRIVILEGE: The specific actions the user can perform, like
SELECT
,INSERT
,UPDATE
, orDELETE
. - database.table: Specify the database and table (or use
*.*
to grant privileges globally).
Example: Grant Multiple Privileges
The following command grants the user sammy
global permissions to create, alter, and drop databases and tables, insert and update data, and more:
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
Warning: Be cautious with the
ALL PRIVILEGES
command, which grants a user full administrative access, similar to the root user:
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
Applying the Changes
MySQL automatically updates the grant tables, so running FLUSH PRIVILEGES
after GRANT
is generally unnecessary. However, it doesn’t hurt to run this command to manually reload the privileges:
FLUSH PRIVILEGES;
Revoking Privileges
If you need to revoke permissions, the syntax is almost identical to granting them:
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';
Viewing User Permissions
To see the current privileges for a user, run:
SHOW GRANTS FOR 'username'@'host';
Deleting a MySQL User
To remove a user from the MySQL system, use the following DROP USER
command:
DROP USER 'username'@'localhost';
Logging in as the New User
After creating a new MySQL user and granting them permissions, you can log in as the new user with the following command:
mysql -u sammy -p
The -p
flag prompts you for the user’s password.
Conclusion
In this guide, you’ve learned how to create new MySQL users and assign the appropriate privileges. By granting only the necessary permissions, you can ensure both security and efficient management of your MySQL database. From here, explore different permission configurations and higher-level MySQL settings to further optimize your database setup.
What do you think?
Show comments / Leave a comment