IT Log

Record various IT issues and difficulties.

“The Comprehensive Guide to User and Permission Management in MySQL” “One Article Is All You Need to Master User and Permission Management in MySQL!”

        In MySQL database management, user management is a critical aspect for ensuring data security and system stability. By implementing proper permission controls, administrators can effectively restrict user access and prevent unauthorized operations and data breaches. This article will introduce common user management operations in MySQL, including user creation, password modification, user deletion, and permission management.

✨✨✨This is the BLOG of Acolight’s No Dream

✨✨✨For more information, please visit my homepageAcolight’s No Dream – CSDN Blog

Before diving into the main content, let’s take a look at an overview of what will be covered:

Contents

1. View Users

2. Create Users

3. Change Password

        （1）Use ALTER USER to change password:

        （2）Use SET PASSWORD to change the current user’s password:

        （3）Use SET PASSWORD to change another user’s password:

4. Delete Users

5. Permission Management

1. View Users

        First, let’s learn about user-related knowledge. When we talk about users, we need to know how to view user information. In MySQL, all user information is stored in the `user` table within the mysql system database. To view all users in the current database, you can query data from this table using a SELECT statement.

Example:

SELECT user, host FROM mysql.user;

Code Explanation:

        This query statement will return the names and host information of all users in the current MySQL system. The field user represents the username, while the field host indicates which hosts the user is allowed to connect from.

        That’s how you view users in MySQL!!!

2. Create Users

        After learning how to view users in MySQL, let’s move on to learn how to create a user in MySQL. When creating a new user in MySQL, the CREATE USER statement is typically used. Using this statement, we can assign a name to the new user, set their password, and specify which hosts they are allowed to connect from.

Syntax for Creating a User:

Code Explanation:

  • user_name: Specifies the username for the new user.
  • host_name: Specifies the range of hosts from which the user can connect, which can be a specific IP address or a wildcard %, indicating that connections are allowed from any host.
  • auth_string: The password set for the user.
  • IF NOT EXISTS: Optional, used to avoid duplicate entry errors when creating a user. If the specified user already exists, an error will not be thrown.

          ——After understanding the syntax for creating a user, let’s use two examples to further enhance our understanding:

Example 1: Creating a User Allowed to Connect from Any Host

Code Explanation:

        This statement creates a user named new_user with the password password123, and allows this user to connect from any host to the MySQL server. The % indicates a wildcard, allowing connections from any IP address.

Example 2: Create a user who can only connect from the local host

Code explanation:

        This statement creates a user named local_user with the password local_password, and allows this user to connect only from the local host (i.e., the machine where the database server is located). The localhost specifies that only connections originating from the same machine will be permitted.

        ——Through the above two cases, we have gained a further understanding of creating users in MySQL. Then, after creating a user, how do we check the user creation status?

After creating a user, we can use the following query statement to check whether the new user has been successfully added:

Code explanation:

        This query will list all users and their corresponding host information, helping us confirm whether new_user and local_user have been correctly created.

        That’s all about creating users!

3. Modify Password

        A user cannot allow anyone to operate with their identity, so we need passwords. How do we change a password? Below are three ways to modify passwords.

        (1) Using ALTER USER to change password:

Code explanation:

        This is the recommended method, ensuring immediate effect after password update. ALTER USER is a command supported in MySQL 5.7 and later versions, suitable for modifying user authentication methods and passwords.

        (2) Using SET PASSWORD to change the current user’s password:

Code explanation:

        This statement is used to modify the password of the currently logged-in user. If the current administrator is logged in as the root user, this command will modify the password of the root user.

        （3）Using SET PASSWORD FOR to modify the password of a specific user:

Code explanation:

        This method is similar to ALTER USER, used to modify the password of a specific user. However, it is important to note that SET PASSWORD FOR and ALTER USER have the same effect but differ in syntax and implementation.

        —Now we have understood the three ways to modify passwords. Let’s use two examples to deepen our understanding:

Example 1: Modify the password of new_user

user to new_password123, and it takes effect immediately. The user can still connect from any host.

Example 2: Change the password of the root user

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_root_password';

[/crayon]


Code Explanation:




This statement changes the password of the 
			root user to 
			new_root_password.




We have a general idea of how to change passwords in MySQL now!!!




4.Delete Users


As the database is used, some unnecessary users may appear. In such cases, we can use the 
			DROP USER statement to delete unwanted users and their related privileges.


Delete User Syntax:




		




If EXISTS is optional, meaning that if the user does not exist, the system will not throw an error.


        ——Similarly, we also use two cases to further deepen your understanding of user deletion:




Example 1: Delete the 
			new_user user




		




Code Explanation:




This statement will delete the 
			new_user user and no longer allow that user to connect to the MySQL server from any host.






Example 2: Delete the 
			local_user user




		




Code Explanation:




This statement will delete the 
			local_user user and no longer allow that user to connect to the MySQL server from the local host.




       


        ——Through the above two cases, I believe readers have their own understanding of deleting users. After deleting user information, how do we check the deletion status?


After deleting a user, we can verify whether the user has been deleted by querying the 
			mysql.user table (as follows):




		




        Thus, we have completed learning how to delete users in MySQL！！！


5. Privilege Management


        Finally, let's learn about privilege management. In MySQL, the permission system is very powerful and can precisely control which operations each user can perform and limit their access scope. First, let's familiarize ourselves with the built-in permission list in MySQL:




    

  • 
			SELECT: Read data.
    • 

  • 
			INSERT: Insert data.
    • 

  • 
			UPDATE: Update data.
    • 

  • 
			DELETE: Delete data.
    • 

  • 
			ALL PRIVILEGES: All permissions.
    • 





        Permissions can not only be set at the database level but also for specific tables, columns, or even certain stored procedures.




        After learning about the built-in permission list in MySQL, let's move on to how to grant permissions. In MySQL, we can use the 
			GRANT statement to allow administrators to grant specific permissions to users. Its basic syntax is as follows:




		




Code Explanation:






    

  • 
			priv_type: Specifies the type of privilege, such as 
			SELECT, 
			INSERT, etc.
    • 

  • 
			priv_level: Specifies the scope of the permission, typically a database, table, or column.
    • 

  • 
			WITH GRANT OPTION: If this option is included, it means the user can grant the permissions they have been granted to other users.
    • 





        Of course, we provide two examples to help readers understand.




To grant the SELECT privilege to the 
			new_user for the 
			test_db database:




		




Code explanation:




        This statement grants the 
			new_user access to all tables in the 
			test_db database with SELECT privileges only, allowing them to query all tables but preventing modification or deletion.






To grant all privileges to the 
			new_user for the 
			test_db database:




		




Code explanation:




        This statement grants the 
			new_user full access to all tables in the 
			test_db database, including SELECT, INSERT, UPDATE, and even structural modifications and deletion capabilities.




        After explaining the two cases above, readers should now have their own understanding of granting privileges. Since there is granting, there must also be privilege revocation. So, how do we revoke privileges?


        When a user's permissions are no longer needed, you can use the 
			REVOKE statement to revoke those permissions:




		




        Of course, we also use two cases to help readers further understand:




Example 1: Revoke 
			new_user's 
			SELECT permission on the 
			test_db database




		




Code explanation:




        This statement revokes 
			new_user's 
			SELECT permission on the 
			test_db database, meaning the user will no longer be able to execute query operations.






Example 2: Revoke all permissions from 
			new_user on the 
			test_db database




		




Code explanation:




With this command, the 
			new_user will lose all permissions of 
			test_db.




Thus, we have completed all operations of permission management!!!




That's all the content of this article~~
, , , , , , , , ,

10 responses to ““The Comprehensive Guide to User and Permission Management in MySQL” “One Article Is All You Need to Master User and Permission Management in MySQL!””

  1. A well-structured article that covers all aspects of MySQL user management. Very useful!

  2. This guide is essential for anyone looking to secure their MySQL database with proper user controls.

  3. Clear and concise instructions. Perfect for understanding how to handle user accounts in MySQL.

  4. Thank you for sharing this valuable information. It clarified many doubts I had about MySQL permissions.

  5. The article provides a comprehensive overview of user and permission management in MySQL. Highly recommended!

  6. Great article! It explains complex concepts in an accessible way, making it perfect for beginners.

  7. This guide makes user management in MySQL seem simple. The examples are easy to follow and implement.

  8. Loved the detailed breakdown of permission management. A must-read for anyone working with MySQL!

  9. The step-by-step explanations are very helpful! Learned how to create, modify, and delete users effectively.

  10. This article is a fantastic guide for managing MySQL users and permissions. It covers everything from creating to deleting users, with clear examples.

Leave a Reply