Shulou(Shulou.com)05/31 Report--

This article shows you how to change passwords and access restrictions in MySQL. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

MySQL is a true multi-user, multi-thread 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. Because of its openness and stability, and its perfect combination with popular americium PHP, many sites use it as a back-end database, which makes it widely used. For security reasons, each user needs to be given access restrictions to different databases to meet the requirements of different users. The following will be discussed separately for your reference.

1. Summary of MySQL password modification methods

First of all, I would like to make it clear that: in general, to change the MySQL password requires the permission of root in the mysql, so the average user cannot change the password unless you ask the administrator for help.

Method one

Use MyAdmin

(graphical MySql management tool), this is the simplest, directly use SQL statements to modify the database user table, but do not forget to use the PASSWORD function, insert user Insert command, modify user Update command, delete with Delete command. The user field of the data table is described in detail later in this section.

Method two

Use mysqladmin. Input

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 the root in the command to your user name, and you can change your own password.

Of course, if your mysqladmin can't connect to mysql,

Server, or you can't execute mysqladmin, then this method is invalid, and mysqladmin can't clear the password.

The following methods are used at the mysql prompt and must have root permission for mysql:

Method three

Mysql > INSERT INTO mysql.user (Host,User,Password) VALUES

(%, system, PASSWORD (manager))

Mysql > FLUSH PRIVILEGES

To be exact, this is adding a user with the user name system and the password manager. Note that you use the PASSWORD function, and then use FLUSH

PRIVILEGES to perform the 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 five

Use the SET PASSWORD statement

Mysql > SET PASSWORD FOR system@ "%" = PASSWORD (manager);

You must also use the PASSWORD () function, but you don't need to use FLUSH PRIVILEGES to perform the confirmation.

Method 6

Use GRANT... IDENTIFIED BY statement to authorize.

Mysql > GRANT USAGE ON *. * TO system@ "%" IDENTIFIED BY manager;

The PASSWORD () function is unnecessary here, and there is no need to use FLUSH PRIVILEGES to perform the confirmation.

Note: the function of PASSWORD () is to encrypt passwords, which is automatically interpreted by MySql in the program.

2. The setting method of access restriction in MySql

We use two ways to set up users.

Go to the Mysql execution directory (usually c:mysqlin). Enter mysqld-shareware.exe, enter mysql

-- user=root mysql, otherwise you can't add new users. Go to the mysql > prompt to do the operation.

Suppose we are going to create a superuser with the user name system and the user password manager.

Method one

Authorize with the Grant command, and enter the following code:

Mysql > GRANT ALL PRIVILEGES ON *. * TO system@localhost IDENTIFIED BY

Manager WITH GRANT OPTION;

Should display: Query OK, 0 rows affected (0.38 sec)

Method two

Set each permission for the user:

Mysql > INSERT INTO user

VALUES (localhost,system,PASSWORD (manager))

Yrecinct Y, Y.)

For version 3.22.34 of MySQL, there are 14 "Ys" here, and the corresponding permissions are as follows (in order of fields):

The permissions table column name explains the scope of use accordingly.

Select Select_priv requires the select permissions table only if it is actually retrieved from a table.

Insert Insert_priv allows you to insert new rows into an existing table

Update Update_priv allows you to update the list of rows in an existing table with new values

Delete Delete_priv allows you to delete row tables that meet the criteria

Create Create_priv allows you to create new databases and tables databases, tables, or indexes

Drop Drop_priv discards (deletes) existing databases and tables

Reload Reload_priv allows you to tell the server to re-read the authorization table server management

Shutdown Shutdown_priv may be abused (by terminating the server denying service to other users) server management

Process Process_priv allows you to view the plain text of the currently executed query, including setting or changing passwords to query server management

File File_priv permissions can be abused on the server to read any readable file to file access on the database table server.

Grant Grant_priv allows you to grant those permissions you own to other user databases or tables

References References_priv allows you to open and close record file databases or tables

Index Index_priv allows you to create or drop (delete) indexed tables

Alter Alter_priv allows you to change the table and can be used to override the permission system table by renaming the table

If you create a user with only select, insert, update, and delete permissions, the user is allowed to operate only on an existing table in a database.

Now we can create the database we want to use, and 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;

Should display: Query OK, 1 row affected (0.00 sec)

The above is how to change passwords and access restrictions in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.