MySQL User and Privilege Management

This article summarizes the content from Professor Song Hongkang's course.

1. User Administration

In MySQL, users can be categorized into regular users and root users. The root user has full privileges including creating, deleting, and modifying user passwords, whereas regular users only possess permissions that have been explicit granted to them.
MySQL offers various commands for managing user accounts, covering login/logout, user creation/deletion, password management, and privilege control.
Database security relies heavily on proper account management.

1.1 Connecting to MySQL Server

After starting the MySQL service, you can connect using the mysql command:

mysql -h hostname|hostIP -P port -u username -p DatabaseName -e "SQL statement"

Parameter descriptions:

• -h parameter specifies the host name or IP address of the server.
• -P parameter indicates the port number used to connect to the MySQL server. The default port is 3306, which is used if this option is omitted.
• -u parameter sets the username.
• -p parameter prompts for a password.
• DatabaseName parameter specifies the database to connect to. If not specified, the connection defaults to the MySQL system database, where you can later use the USE command to switch databases.
• -e parameter allows executing an SQL statement directly upon connecsion, then exiting the MySQL server.

1.2 Creating Users

The basic syntax for creating users is:

CREATE USER username [IDENTIFIED BY 'password'][, username [IDENTIFIED BY 'password']];

1.3 Modifying Users

To change a user's name:

1.4 Removing Users

Users can be deleted using the DROP USER statement or by directly removing entries from the mysql.user table.
Method 1: Using DROP USER (Recommended)
Method 2: Using DELETE (Not Recommended)

1.5 Setting Passwords for Current User

This applies to both root and regular users updating their own passwords. Root user access is highly privileged, so password security is critical. The official method recommended by MySQL for changing passwords is using ALTER USER. Additionally, SET PASSWORD can also be used. Since the PASSWORD() function was removed in MySQL 8, direct updates to the user table are no longer supported.
Legacy approach:

# Changing current user's password: (Tested on MySQL 5.7)
SET PASSWORD = PASSWORD('123456');

Recommended approaches:

1. Using ALTER USER to update current user's password
   Regular users can change their password using the ALTER command:

ALTER USER USER() IDENTIFIED BY 'new_password';

2. Using SET statement to modify current password
   As root user, execute the following SQL command:

SET PASSWORD='new_password';

This automatically encrypts and assigns the new password.

1.6 Changing Passwords for Other Users

Root users can also update passwords for other regular users. After logging in as root, you can utilize ALTER USER or SET PASSWORD statements. Due to the removal of PASSWORD(), direct table manipulation is deprecated.

1. Using ALTER USER to modify another user’s password
   Basic syntax:

ALTER USER user [IDENTIFIED BY 'new_password'][, user[IDENTIFIED BY 'new_password']]...;

Where user refers to the user account consisting of username and hostname; IDENTIFIED BY sets the password.

2. Using SET command to change another user’s password
   Login as root and run:

SET PASSWORD FOR 'username'@'hostname'='new_password';

Here, username is the target user's name; hostname is the host allowed to connect; new_password is the new password.

3. Using UPDATE to change another user’s password (Not Recommended)
   With root access, update the password field in the mysql.user table:

UPDATE mysql.user SET authentication_string=PASSWORD("123456")
WHERE User = "username" AND Host = "hostname";

1.7 MySQL 8 Password Management (Knowledge)

MySQL maintains history of previously used passwords, offering features like: (1) Password expiration policies requiring periodic changes. (2) Password reuce restrictions preventing old passwords. (3) Password strength checks ensuring robust passwords.

1. Password Expiration Policy

2. Password Reuse Policy

2. Privilege Management

Permissions in MySQL define what actions a user is allowed to perform within the system. For example, a user with only SELECT permission cannot perform UPDATE operations. Similarly, a user restricted to connecting from a certain machine cannot connect from elsewhere.

2.1 Privilege List

Available MySQL privileges can be viewed using:

SHOW PRIVILEGES;

2.2 Principles of Granting Permissions

Security considerations require adherence to these best practices:

1. Grant only the minimum necessary permissions required for functionality.
2. When creating users, restrict login hosts, preferably to specific IPs or internal networks.
3. Enforce strong password policies for all users.
4. Regularly review and remove unnecessary users, revoking or deleting their accounts.

2.3 Granting Privileges

There are two methods for granting permissions:

1. Assigning roles to users.
2. Directly assigning privileges to users.

2.4 Viewing Privileges

2.5 Revoking Privileges

3. Privilege Tables

4. Access Control (Understanding)

5. Role Management

6. Configuration File Usage

6.1 Configuration File Format

Unlike command-line options, configuration files contain startup parameters.

6.2 Startup Commands and Option Groups

6.3 Version-Specific Options

6.4 Priority Among Multiple Groups in One File

6.5 Differences Between Command Line and Config Files

7. System Variables