Detailed explanation of examples of changing passwords and access restrictions in MySQL

MySQL is a true multi-user, multi-threaded SQL database server. MySQL is implemented as a client/server structure, which consists of a server daemon mysqld and many different client programs and libraries.

Due to the openness and stability of its source code, and its perfect combination with PHP, the popular website editor, many websites now use it as a back-end database, making it widely used. For security reasons, each user needs to be assigned access restrictions to different databases to meet the requirements of different users. They are discussed separately below for your reference.
1. Summary of MySQL password modification methods
First of all, one thing to note is: Under normal circumstances, changing the MySQL password requires root permissions in mysql, so ordinary users cannot change the password. , unless the administrator is requested to help modify it.
Method 1 　
Use phpMyAdmin
(a tool for graphical management of MySql database). This is the simplest. Directly use SQL statements to modify the user table of the mysql database, but don’t forget To use the PASSWORD function, use the Insert command to insert a user, the Update command to modify a user, and the Delete command to delete a user. There is a detailed introduction to the user field of the data table later in this section.　　
　　Method 2　　　　
　 Use mysqladmin. Enter 　
　mysqladmin -u root -p oldpassword newpasswd 　　　
After executing this command, you need to enter the original password of root, so that the password of root will be changed to newpasswd. Similarly, change root in the command to your username, and you can change your own password.
Of course, if your mysqladmin cannot connect to the mysql
server, or you cannot execute mysqladmin, then this method is invalid, and mysqladmin cannot clear the password.
The following methods are used at the mysql prompt and must have root permissions for mysql:
Method 3
mysql> INSERT INTO mysql.user (Host,User,Password) VALUES
　('%','system', PASSWORD('manager'));
　mysql> FLUSH PRIVILEGES　　
　To be precise, this is adding a user, the username is system and the password is manager. Note that you need to use the PASSWORD function, and then use FLUSH
PRIVILEGES to perform confirmation.
Method 4 　
Same as method 3, except that the REPLACE statement is used
mysql> REPLACE INTO mysql.user (Host,User,Password)
VALUES('%' ,'system',PASSWORD('manager'));
mysql> FLUSH PRIVILEGES
Method 5
Use the SET PASSWORD statement
mysql> SET PASSWORD FOR system@"% " = PASSWORD('manager'); ​
You must also use the PASSWORD() function, but you do not need to use FLUSH PRIVILEGES to perform confirmation.​
​ Method 6​
Use the GRANT... IDENTIFIED BY statement to authorize.​
Mysql> GRANT USAGE ON *.* TO system@"%" IDENTIFIED BY 'manager';
Here the PASSWORD() function is unnecessary, and there is no need to use FLUSH PRIVILEGES to perform confirmation.​
Note: The PASSWORD() function is used to encrypt the password, which is automatically interpreted by MySql in the program.​
　 2. How to set access restrictions in MySql
We use two methods to set users.
Enter the Mysql execution directory (usually c:\mysql\bin). Enter mysqld-shareware.exe, enter mysql
　--user=root mysql, otherwise you cannot add a new user. Enter the mysql> prompt to perform operations.​
Suppose we want to create a super user with the user name system and the user password manager.
　 Method One 　
Use the Grant command to authorize, enter the code as follows:
　mysql>GRANT ALL PRIVILEGES ON *.* TO system@localhost IDENTIFIED BY
'manager' WITH GRANT OPTION;
VALUES('localhost','system',PASSWORD('manager'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y ','Y');
For the 3.22.34 version of MySQL, there are a total of 14 "Y"s, and their corresponding permissions are as follows (arranged in field order): ​
Permissions Table column name Corresponding explanation Scope of use
　select Select_priv only requires select permission when actually retrieving from a table table
insert Insert_priv allows you to insert new rows into an existing table table
update Update_priv allows you to update rows in an existing table with new values list
delete Delete_priv allows you to delete rows that meet the conditions table
create Create_priv allows you to create new databases and tables databases, tables or indexes
drop Drop_priv discards (delete) existing databases and tables database or Table
reload Reload_priv allows you to tell the server to re-read the authorization table Server Management
shutdown Shutdown_priv can be abused (by terminating the server to deny service to other users) Server Management
process Process_priv allows you to view the currently executing query Plain text, including setting or changing password queries Server Administration
File File_priv permission can be abused to read any readable file on the server into a database table File access on the server
Grant Grant_priv allows you to put your own Those permissions granted to other users database or table
References References_priv allows you to open and close the log file database or table
index Index_priv allows you to create or discard (delete) index table
alter Alter_priv allows you to change Tables can be used to override the permission system by renaming the table. Table
If the user is created with only select, insert, update and delete permissions, the user is allowed to perform operations only on the existing tables of a database.
Now we can create the database we want to use, we enter it directly. For example: if we want to create a database named XinXiKu, we can use the following code: 　
　mysql>create database XinXiKu; affected (0.00 sec)

【Related recommendations】

1.

Mysql free video tutorial

2.

Use mysqldump to back up and restore tables Detailed explanation of examples

3.

Detailed explanation of examples of php storing pictures in mysql

4.

##5. Detailed explanation of examples of replacing content in the database with regular expressions

The above is the detailed content of Detailed explanation of examples of changing passwords and access restrictions in MySQL. For more information, please follow other related articles on the PHP Chinese website!