Change password and access restriction settings in MySQL _ MySQL

MySQL is a real multi-user, multi-thread SQL database server. MySQL is implemented in a client server structure. it consists of a server daemon mysqld and many different client programs and libraries. Because of its open nature and stability of the source code, and its perfect combination with the popular compilation of PHP, many websites now use it as a back-end database, mySQL is a real multi-user, multi-thread SQL database server. MySQL is implemented in a client/server structure. it consists of a server daemon mysqld and many different client programs and libraries. Because of its openness and stability of the source code, and its perfect combination with the popular compilation of PHP, many websites now use it as a back-end database, making it widely used. In terms of security, each user must be given access restrictions on different databases to meet the requirements of different users. We will discuss it separately for your reference.
　　
　　 I. Summary of MySQL password change methods
The first thing to note is: Generally, you need to have the root permission in MySQL to change the mysql password. in this way, you cannot change the password unless you ask the administrator to help you modify the password.
　　
　　 Method 1
Use phpMyAdmin
　　
(Graphical MySql database management tool), this is the simplest, directly use SQL statements to modify the user table of the mysql database, but do not forget to use the PASSWORD function, Insert the user using the Insert command, modify the user's use of the Update command and Delete the use of the Delete command. This section describes the user field of a data table.
　　
　　 Method 2
Use mysqladmin. Input
　　
Mysqladmin-u root-p oldpassword newpasswd
　　
After executing this command, you need to enter the original root password, so that the root password will be changed to newpasswd. Similarly, change the root in the command to your username, and you can change your password.
　　
Of course, if your mysqladmin cannot connect to mysql
　　
Or you cannot execute mysqladmin. this method is invalid, and mysqladmin cannot clear the password.
　　
The following methods are used at the mysql prompt and must have the root permission of mysql:
　　
　　 Method 3
Mysql> insert into mysql. user (Host, User, Password) VALUES
　　
('%', 'System', PASSWORD ('manager '));
　　
Mysql> FLUSH PRIVILEGES
　　
Specifically, this is to add a user with the username system and password manager. Be sure to use the PASSWORD function, and then use FLUSH
　　
PRIVILEGES for confirmation.
　　
　　 Method 4
Similar to method Sany, but 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 do not need to use flush privileges for confirmation.
　　
　　 Method 6
Use the GRANT... identified by statement to GRANT permissions.
　　
Mysql> grant usage on *. * TO system @ "%" identified by 'manager ';
　　
Here, the PASSWORD () function is unnecessary and you do not need to use flush privileges for confirmation.
　　
Note: The PASSWORD () function is used to encrypt passwords and automatically interpret them in MySql.
　　
　　 II. how to set access restrictions in MySql
We use two methods to set users.
　　
Go to the Mysql execution Directory (usually c: \ mysql \ bin ). Input mysqld-mongoware.exe and mysql
　　
-- User = root mysql; otherwise, new users cannot be added. Go to the mysql> prompt to perform the operation.
　　
Suppose we want to create a super user with the username system and user password manager.
　　
　　 Method 1
Use the Grant command for authorization. the input code is as follows:
　　
Mysql> grant all privileges on *. * TO system @ localhost IDENTIFIED
　　
'Manager' with grant option;
　　
Display: Query OK, 0 rows affected (0.38 sec)
　　
　　 Method 2
Set each permission of a user:
　　
Mysql> insert into user
　　
VALUES ('localhost', 'system', PASSWORD ('manager '),
　　
'Y', 'y ', 'Y', 'y ');
　　
For MySQL 3.22.34, there are 14 "Y" in total. the corresponding permissions are as follows (sorted by field ):
　　
Permission table column name explanation scope
　　
Select Select_priv the select permission table is required only when a table is actually retrieved.
　　
Insert Insert_priv allows you to insert a new row into an existing table.
　　
Update Update_priv allows you to use the new value to update the columns in the row of an existing table.
　　
Delete Delete_priv allows you to delete row tables that meet the conditions.
　　
Create Create_priv allows you to create new databases and table databases, tables, or indexes.
　　
Drop Drop_priv discard (delete) existing database and table database or table
　　
Reload Reload_priv allows you to tell the server to read the authorization table again. server management
　　
Shutdown Shutdown_priv may be abused (by terminating the server and rejecting other user services) server management
　　
Process Process_priv allows you to view the common text of the currently executed query, including setting or changing the password query server management.
　　
File File_priv permission can be abused to read any readable files from the server to the database table server for file access
　　
Grant Grant_priv allows you to grant your own permissions to other user databases or tables.
　　
References References_priv allows you to open and close the record file database or table
　　
Index Index_priv allows you to create or discard (delete) index tables.
　　
Alter Alter_priv allows you to change the table, which can be used to overturn the permission system table by renaming the table.
　　
If you only have select, insert, update, and delete permissions when creating a user, you can only perform operations on the existing table of a database.
　　
Next we can create the database we want to use. we can directly enter it. for example, we want to create a database named XinXiKu. the following code is available:
　　
Mysql> create database XinXiKu;
　　
Display: Query OK, 1 row affected (0.00 sec)