MySQL is a powerful relational database management system that is commonly used to store and manage data for web applications. To ensure the security and proper functioning of your MySQL database, it’s essential to create and manage user accounts with the appropriate permissions. In this tutorial, we will walk you through the process of creating a new user and granting permissions in MySQL 8, using both the command-line interface and a graphical tool like phpMyAdmin.

Prerequisites:

• A server with MySQL 8 installed.
• Access to the command line or phpMyAdmin.

Method 1: Using the MySQL Command Line

Step 1: Access the MySQL Command Line

Open your terminal or command prompt and log in to MySQL as a superuser or a user with sufficient privileges. Use the following command:

mysql -u root -p

You will be prompted to enter the MySQL root password.

Step 2: Create a New User

To create a new user, you can use the CREATE USER command. Replace ‘newuser’ and ‘password’ with your desired username and password:

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;

Step 3: Grant Permissions

To grant permissions to the new user, use the GRANT statement. Replace ‘database_name’ with the name of the database to which you want to grant access, and ‘newuser’ with the username:

GRANT ALL PRIVILEGES ON database_name.* TO ‘newuser’@’localhost’;

After granting permissions, don’t forget to flush the privileges:

FLUSH PRIVILEGES;

Step 4: Exit MySQL

You can exit MySQL by typing:

FLUSH PRIVILEGES;

Method 2: Using phpMyAdmin

Step 1: Access phpMyAdmin

1. Open your web browser and enter the URL for your phpMyAdmin interface. It is usually located at http://localhost/phpmyadmin.
2. Log in using your MySQL username and password.

Step 2: Create a New User

1. Click on the “User accounts” tab in phpMyAdmin.
2. Click “Add user account” or “Add User” depending on your phpMyAdmin version.
3. Enter the username and password.
4. Choose “Local” for the Host name.
5. Set the “Authentication Plugin” to “Native MySQL authentication.”

Step 3: Grant Permissions

1. In phpMyAdmin, navigate to the “Databases” tab.
2. Click on the database for which you want to grant permissions.
3. Go to the “Privileges” tab.
4. Click “Add user account privileges” or a similar option.
5. Select the user you just created.
6. Specify the privileges you want to grant and save changes.

Creating a new user and granting permissions in MySQL 8 is crucial for maintaining a secure and well-organized database. Whether you prefer the command line or a graphical interface like phpMyAdmin, following these steps will help you manage your MySQL users and database access effectively. Always ensure that you grant the minimum necessary permissions to maintain the security of your MySQL server.