Introduction
Create a new user and grant permissions in MySQL to securely manage database access for applications, websites, and other services. MySQL is a popular open-source relational database management system used to store and manage application data. Creating a new MySQL user and assigning the required permissions is an important task for database administrators and developers.
In this guide, we will explain how to create a new user and grant permissions in MySQL, including granting access to all databases, granting access to a specific database, and revoking permissions when required.
Prerequisites
Before creating a new MySQL user and granting permissions, make sure you have the following:
- Root or administrative access to the MySQL server.
- Access to the MySQL command-line shell.
- MySQL installed and running on the server.
- The required username and password for the new MySQL user.
- The name of the database if you want to grant access to a specific database.
Implementation
MySQL, the most popular open-source SQL database management system, is developed, distributed, and supported by Oracle Corporation.
Step 1: A new user can be created within the MySQL shell using the following statement:
CREATE USER 'pheonix'@'localhost' IDENTIFIED BY 'password';
This will create a new user named ‘pheonix’ without any permissions.
Step 2: To give full access to all databases for the pheonix user, run the following statement:
GRANT ALL PRIVILEGES ON * . * TO 'pheonix'@'localhost';
Step 3:Then run the following statement to reload all the privileges:
FLUSH PRIVILEGES;
Step 4: To give access to a specific database for the new user, run the following statement:
GRANT ALL PRIVILEGES ON database . * TO 'pheonix'@'localhost';
Step 5: To revoke access:REVOKE ALL PRIVILEGES ON database.* FROM 'pheonix'@'localhost';
Conclusion
Creating a new MySQL user and granting the appropriate permissions helps manage database access securely and efficiently. Using the CREATE USER, GRANT, and REVOKE statements, you can control which databases a user can access and what actions they can perform. It is recommended to grant users only the permissions they require to follow the principle of least privilege and improve MySQL database security.
For detailed information about MySQL account management and user privileges, refer to the official MySQL documentation Link
FAQs
1. How do I create a new user in MySQL?
You can create a new MySQL user using the CREATE USER statement:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
2. How do I grant all privileges to a MySQL user?
Use the GRANT ALL PRIVILEGES statement:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
This grants the user full privileges on all databases.
3. How do I grant access to a specific database in MySQL?
You can grant privileges to a specific database using:
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';
Replace database with the name of the required database.
Related Articles
Configuring MySQL to Listen on a Private IP Address
How to Disable/Lock a MySQL User Account from CLI?
How to Copy a Table from One Database to Another in MySQL?
Need Help with MySQL User Management?
Having trouble creating MySQL users or managing database permissions? Our experts can help you configure MySQL users, privileges, and database access securely and efficiently. Get in touch with our team here