1. Connect to MySQL database
First, connect to the MySQL database as an administrator, usually the root user:
mysql -u root -p
The system will prompt to enter the password of the administrator user, and then enter the command line of MySQL.
2. Create a new user
Use the CREATE USER command to create a new MySQL user. The basic syntax of this command is as follows:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
- ‘username’: The name of the new user.
- ‘host’: Specifies which host the user is allowed to connect from. localhost means only local connections are allowed, % means connections from any IP address are allowed.
- ‘password’: Set the password for the new user.
Example: Create a user named gktask, only allow connections from the localhost (localhost) with password set to 123456.
CREATE USER 'gktask'@'localhost' IDENTIFIED BY '123456';
3. Grant permissions
In MySQL, permissions are divided into multiple levels, including database level, table level, and column level permissions. The corresponding permissions can be granted according to the user's needs. Common permissions include SELECT, INSERT, UPDATE, DELETE, etc. Typically, the GRANT command is used to assign permissions.
3.1 Grant all permissions
If you want the user to have full access to all databases and tables, you can use the following command:
GRANT ALL PRIVILEGES ON *.* TO 'gktask'@'localhost';
in:
- ALL PRIVILEGES means granting the user all permissions.
- *. Indicates granting permissions to all databases and tables.
If you only want the user to access a specific database, you can replace . with the specified database name, such as mydatabase.:
GRANT ALL PRIVILEGES ON mydatabase.* TO 'gktask'@'localhost';
3.2 Refresh permissions
After changing the permissions, execute the following command to refresh the permissions so that they take effect immediately:
FLUSH PRIVILEGES;
4. View all users
As root, you can query all users and view the current list of users in MySQL. You can achieve this by querying the table:
SELECT User, Host FROM ;
5. Revoke permission (optional)
If you need to cancel the user's permission, you can use the REVOKE command. For example, to cancel all permissions of gktask users to mydatabase database, you can execute the following command:
REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'gktask'@'localhost';
Execute FLUSH PRIVILEGES to make the changes take effect:
FLUSH PRIVILEGES;
6. Delete the user (optional)
If a user is no longer needed, you can delete it using the DROP USER command:
DROP USER 'gktask'@'localhost';
In MySQL, managing users and permissions is an important step in database security. Through the above steps, you can create new users, grant permissions, and manage and delete users. Properly configure permissions to ensure that each user can only access the data resources they need, which helps improve the security and management efficiency of the database.
Attachment: How to solve the failure of mysql user authorization
MySQL user authorization failure can be caused by a variety of reasons, here are some solutions:
Make sure the username and password are correct: First check whether the username and password are used are correct, it may be that the input is incorrect or the password has been changed.
Check the authorization table: Use the administrator account to log in to MySQL and check whether the user permissions are authorized correctly. You can use the following command to view user permissions:
SHOW GRANTS FOR 'username'@'hostname';
- Reauthorize the user: If the user permissions are incorrect, you can reauthorize the user, for example:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Check IP address restrictions: If the user authorization fails, it may also be related to the IP address, you can check whether the IP address restrictions are set.
Check the MySQL service status: Ensure that the MySQL service is running normally and you can restart the MySQL service.
Check firewall settings: Sometimes firewall settings may affect MySQL connections. You can try to turn off the firewall or modify the firewall settings.
Summarize
This is the article about creating new users and granting permissions in MySQL database. For more related content on creating new users and granting permissions in MySQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!