In MySQL modify password and access restrictions set the details

MySQL is a real multi-user, multi-threaded SQL database server. MySQL is implemented as a client/server architecture, consisting of a server daemon mysqld and many different client programs and libraries. Due to the openness and stability of its source code and the perfect combination of Wylie language and PHP, many sites use it as a back-end database, which makes it widely used. Security considerations require that each user be assigned access restrictions to different databases to meet the requirements of different users.

A, MySQL modified password method summary

The first thing to say is: Under normal circumstances, to modify the MySQL password is required to have the root of MySQL, so the average user can not change the password, unless the administrator to help modify.

Method One

Using phpMyAdmin (the tool for graphical management of MySQL databases), this is the simplest, directly using SQL statements to modify the MySQL Database Library user table, but do not forget to use the password function, insert the user with the Insert command, modify the user with the update command, Deletes with the delete command. A detailed description of the data table User field is available 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 root password will be changed to NEWPASSWD. Again, change the command root to your username, and you can change your password. Of course, if your mysqladmin is not connected to MySQL server, or you have no way to execute mysqladmin, then this method is invalid, and mysqladmin cannot empty the password.

The following methods are used at the MySQL prompt and must have the root permissions of MySQL:

Method Three

MySQL "GT; INSERT into Mysql.user (Host,user,password) ValueS ('% ', ' system ', Password (' manager '));

MySQL "GT; FLUSH Privileges

Specifically, this is adding a user named system with a password of manager. Note that you want to use the password function, and then use flush privileges to perform the validation.

Method Four

And method Three, just use the Replace statement

MySQL "GT; REPLACE into Mysql.user (Host,user,password)

ValueS ('% ', ' system ', PASSWORD (' manager '));

MySQL "GT; FLUSH Privileges

Method Five

Using the Set Password statement

MySQL "GT; SET PASSWORD for system@ "quot;%" quot; = PASSWORD (' manager ');

You must also use the password () function, but you do not need to use flush privileges to perform the validation.

Method Six

Use Grant ... Identified by statement to authorize.

MySQL "GT; GRANT USAGE on *.* to system@ "quot;%" quot; Identified by ' manager ';

This password () function is unnecessary and does not require the use of flush privileges to perform validation.

Note: PASSWORD () function is to encrypt password, in the program MySQL automatic interpretation.

The method of setting access restrictions in MySQL

We use two methods to set up the user.

Enter the MySQL execution directory (usually C:/mysql/bin). Enter Mysqld-shareware.exe, enter MySQL--user=root MySQL, otherwise cannot add new user. Go to the MySQL "GT" Prompt for action.

Let's say we're going to build a superuser, the username is system, and the user password is manager.

Method One

With the grant command authorization, enter the following code:

MySQL "GT; Grant all privileges on *.* to system@localhost identified by ' manager ' with Grant OPTION;

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

Method Two

To set each permission for a user:

MySQL "Gt;insert into user ValueS (' localhost ', ' system ', PASSWORD (' manager '), ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ', ', ', '", ' y ', ' y '), ' Y ', ' y ', ' y ', ' y ', ' y ');

For 3.22.34 version of MySQL, here a total of 14 "quot;" Y "quot, the corresponding permissions are as follows (in the Order of fields):

The Permission table column name explains the use scope appropriately

Select Select_priv requires a SELECT permission table only when it is actually retrieved from a table

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

The update Update_priv allows you to update the list of rows in an existing table with a new value

Delete Delete_priv allows you to delete a row table that satisfies a condition

Create Create_priv allows you to build new database and table databases, tables, or indexes

Drop Drop_priv Discard (delete) existing database and table databases or tables

Reload Reload_priv allows you to tell the server to read back into the Authorization table server administration

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

Process Process_priv allows you to view the normal text of the currently executing query, including setting or changing password Query server Management

File File_priv permissions can be misused to read any readable files on the server to access files on the database table server