Introduction

MySQL is one of the most popular relational database management systems used for storing and managing data. When working with MySQL, it’s essential to create users with specific permissions to ensure security and proper access control. In this guide, we’ll walk you through the steps to create a new user and grant permissions in MySQL.

Step 1: Access MySQL as the Root User

To create a new user and grant permissions, you need to access MySQL as the root user or another user with administrative privileges. Use the following command to log in:

mysql -u root -p

You’ll be prompted to enter the root password. Once logged in, you can proceed to create a new user.

Step 2: Create a New User

To create a new user, use the CREATE USER statement. Replace username and password with your desired credentials:

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

If you want to allow the user to connect from any host, replace localhost with %:

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

Step 3: Grant Permissions to the User

After creating the user, you need to grant them specific permissions. Use the GRANT statement to assign privileges. For example, to grant all privileges on a specific database, use the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

If you want to grant specific privileges, such as SELECT, INSERT, and UPDATE, you can specify them like this:

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

After granting permissions, run the following command to apply the changes:

FLUSH PRIVILEGES;

Step 4: Verify User Permissions

To verify the permissions granted to the user, use the SHOW GRANTS statement:

SHOW GRANTS FOR 'username'@'localhost';

This will display a list of all permissions assigned to the user.

Step 5: Remove a User (Optional)

If you need to remove a user, use the DROP USER statement:

DROP USER 'username'@'localhost';

This will delete the user and revoke all their permissions.

Conclusion

Creating a new user and granting permissions in MySQL is a fundamental task for database administrators. By following the steps outlined in this guide, you can ensure that your database is secure and that users have the appropriate level of access.

If you’re looking for a reliable hosting solution to manage your MySQL databases, consider  free webhosting options to help you get started quickly.