How to Create a New User and Grant Permissions in MySQL

Introduction: Managing user accounts and permissions in MySQL is essential for securing your database and controlling access to its resources. By creating new users and granting appropriate permissions, you can ensure that users have the necessary privileges to perform their tasks while maintaining data integrity and security. In this tutorial, we will guide you through the process of creating a new user and granting permissions in MySQL. By following these steps, you will have the knowledge to manage user accounts effectively and enforce access controls in your MySQL environment.

Table of Contents:

1. Understanding MySQL User Accounts and Permissions
2. Connecting to the MySQL Server
3. Creating a New User
4. Granting Permissions to the User
5. Verifying User Permissions
6. Modifying User Permissions
7. Revoking User Permissions
8. Removing a User
9. Conclusion

1. Understanding MySQL User Accounts and Permissions

MySQL uses a user-based access control system, where each user account is associated with specific permissions that determine their privileges within the database. User accounts are separate from operating system user accounts and are managed within MySQL itself. By creating and configuring user accounts, you can control who can access the database and what actions they can perform.

2. Connecting to the MySQL Server

Before creating a new user or granting permissions, you need to connect to the MySQL server. Follow these steps:

1. Open a terminal or command prompt.
2. Enter the following command to connect to the MySQL server as the root user:

mysql -u root -p

You will be prompted to enter the root user’s password. After successful authentication, you will be logged in to the MySQL server.

3. Creating a New User

To create a new user in MySQL, follow these steps:

1. Once you are logged in to the MySQL server, enter the following command to create a new user:

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

Replace ‘username’ with the desired username for the new user, and ‘password’ with a strong password.

2. If you want to allow the user to connect from any host, use the following command instead:

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

3. To create a user with administrative privileges, use the following command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

Replace ‘username’ and ‘password’ with your desired values.

4. Granting Permissions to the User

To grant specific permissions to a user, follow these steps:

1. Grant all privileges on a specific database to the user:

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

Replace ‘database_name’ with the name of the database and ‘username’ with the appropriate username.

2. Grant specific privileges to the user. For example, to grant SELECT, INSERT, UPDATE, and DELETE privileges on a specific table, use the following command:

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

Replace ‘database_name’ and ‘table_name’ with the appropriate values.

3. If you want to grant all privileges to the user on all databases and tables, use the following command:

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

4. After granting the permissions, apply the changes by running the following command:

FLUSH PRIVILEGES;

5. Verifying User Permissions

To verify the permissions granted to a user, follow these steps:

1. Log out from the MySQL server by entering the following command:

exit

2. Log back in to the MySQL server as the user you created:

mysql -u username -p

Replace ‘username’ with the appropriate username.

3. Enter the user’s password when prompted.
4. To view the user’s granted privileges, run the following command:

SHOW GRANTS;

6. Modifying User Permissions

To modify the permissions of an existing user, follow these steps:

1. Log in to the MySQL server as the root user.
2. Run the following command to modify the user’s permissions:

GRANT permission_list ON database_name.table_name TO 'username'@'localhost';

Replace ‘permission_list’, ‘database_name’, ‘table_name’, and ‘username’ with the appropriate values.

Apply the changes by running

FLUSH PRIVILEGES;

7. Revoking User Permissions

To revoke specific permissions from a user, follow these steps:

1. Log in to the MySQL server as the root user.
2. Run the following command to revoke permissions from the user:

REVOKE permission_list ON database_name.table_name FROM 'username'@'localhost';

Replace ‘permission_list’, ‘database_name’, ‘table_name’, and ‘username’ with the appropriate values.

3. Apply the changes by running:

FLUSH PRIVILEGES;

8. Removing a User

To remove a user from the MySQL server, follow these steps:

1. Log in to the MySQL server as the root user.
2. Run the following command to remove the user:

DROP USER 'username'@'localhost';

Check My Social Profile Links

Instagram

Youtube

Website

About The Author