In MySQL modify password and access restrictions set the details

mysql| Access | Detailed 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. The following is discussed separately for your reference.

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> INSERT into Mysql.user (Host,user,password) VALUES ('% ', ' system ', Password (' manager '));
Mysql> 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> REPLACE into Mysql.user (Host,user,password)
VALUES ('% ', ' system ', PASSWORD (' manager '));
Mysql> FLUSH Privileges

Method Five

Using 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 the validation.

Method Six

Use Grant ... Identified by statement to authorize.

Mysql> GRANT USAGE on *.* to system@ "%" 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> prompt to do the operation.

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>grant all privileges in *.* 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>insert into user VALUES (' localhost ', ' system ', PASSWORD (' manager '), ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y-axis ', ' y-axis ', ' Y ' , ' y ', ' y ', ' y ', ' y ');

For 3.22.34 version of MySQL, here are 14 "Y" with the appropriate permissions (in order of field):
　

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
Grant Grant_priv allows you to delegate those permissions you own to other user databases or tables
References References_priv allows you to open and close a log file database or table
Index INDEX_PRIV allows you to create or discard (delete) an indexed table
Alter ALTER_PRIV allows you to change the table, which 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 perform operations only on existing tables in one database.

Here we can create the database we want to use, we directly enter. For example: To create a database named Xinxiku, you can use the following code:

Mysql>create database Xinxiku;

Should be shown: Query OK, 1 row Affected (0.00 sec)