MySQL中修改密码及访问限制设置详解 MySQL change password and access restrictions in the Detailed Settings

MySQL是一个真正的多用户、多线程SQL数据库服务器。 MySQL is a true multi-user, multi-threaded SQL database server. MySQL是以一个客户机/服务器结构的实现，它由一个服务器守护程序mysqld和很多不同的客户程序和库组成。 MySQL is a client / server implementation that consists of a server daemon mysqld and many different client programs and libraries. 由于其源码的开放性及稳定性，且与网站流行编徎语言PHP的完美结合，现在很多站点都利用其当作后端数据库，使其获得了广泛应用。 Because of its open source and stability, and the site of the popular language PHP compiled Cheng perfect combination, now many sites are using it as back-end database, it has been applied widely. 处于安全方面的考虑，需要为每一用户赋于对不同数据库的访问限制，以满足不同用户的要求。 In security considerations, the need for each user assigns access restrictions for different databases, to meet different user requirements. 下面就分别讨论，供大家参考。 Here are discussed, for your reference.

一、MySQL修改密码方法总结 One, MySQL Change Password Methods
首先要说明一点的是：一般情况下，修改MySQL密码是需要有mysql里的root权限的，这样一般用户是无法更改密码的，除非请求管理员帮助修改。 The first point to note is this: Under normal circumstances, there is the need to modify the MySQL password in the mysql root privileges, so the average user can not change the password, unless the request to help modify the administrator.

方法一 Method one

使用phpMyAdmin Use phpMyAdmin
(图形化管理MySql数据库的工具)，这是最简单的，直接用SQL语句修改mysql数据库库的user表，不过别忘了使用PASSWORD函数，插入用户用Insert命令，修改用户用Update命令，删除用Delete命令。 (Graphical management tool for MySql database), this is the most simple, direct use of SQL statements to modify the user table mysql database library, but do not forget to use the PASSWORD function, insert the user with the Insert command, modify the user with the Update command to delete with Delete command. 在本节后面有数据表user字段的详细介绍。 There are data in the table later in this section a detailed description of user fields.

方法二 Method Two

使用mysqladmin。 Use mysqladmin. 输入 Enter

mysqladmin -u root -p oldpassword newpasswd mysqladmin-u root-p oldpassword newpasswd

执行这个命令后，需要输入root的原密码，这样root的密码将改为newpasswd。 After executing this command, you need to enter the root of the original password, so that the root password will be changed to newpasswd. 同样，把命令里的root改为你的用户名，你就可以改你自己的密码了。 Similarly, the command in the root to your user name, you can change your own password.
当然如果你的mysqladmin连接不上mysql Of course, if your connection is not on the mysql mysqladmin
server，或者你没有办法执行mysqladmin，那么这种方法就是无效的，而且mysqladmin无法把密码清空。 server, or you do not implement mysqladmin, then this method is ineffective, and not the mysqladmin password empty.

下面的方法都在mysql提示符下使用，且必须有mysql的root权限： The following methods are in the mysql prompt, and must have the mysql root privileges:

方法三 Method three

mysql> INSERT INTO mysql.user (Host,User,Password) VALUES mysql> INSERT INTO mysql.user (Host, User, Password) VALUES
('%','system', PASSWORD('manager')); ('%',' System ', PASSWORD (' manager '));
mysql> FLUSH PRIVILEGES mysql> FLUSH PRIVILEGES

确切地说这是在增加一个用户，用户名为system，密码为manager。 Rather it is to add a user, the user name system, password is manager. 注意要使用PASSWORD函数，然后还要使用FLUSH Note To use the PASSWORD function, and also the use of FLUSH
PRIVILEGES来执行确认。 PRIVILEGES to perform confirmation.

方法四 Method Four

和方法三一样，只是使用了REPLACE语句 And methods of three the same, but use the REPLACE statement

mysql> REPLACE INTO mysql.user (Host,User,Password) mysql> REPLACE INTO mysql.user (Host, User, Password)
VALUES('%','system',PASSWORD('manager')); VALUES ('%',' system ', PASSWORD (' manager '));
mysql> FLUSH PRIVILEGES mysql> FLUSH PRIVILEGES

方法五 Method Five

使用SET PASSWORD语句 Using the SET PASSWORD statement

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

你也必须使用PASSWORD()函数，但是不需要使用FLUSH PRIVILEGES来执行确认。 You must use the PASSWORD () function, but do not need to use FLUSH PRIVILEGES to perform validation.

方法六 Method six

使用GRANT ... IDENTIFIED BY语句，来进行授权。 Using the GRANT ... IDENTIFIED BY statement, to be licensed.

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

这里PASSWORD()函数是不必要的，也不需要使用FLUSH PRIVILEGES来执行确认。 Here PASSWORD () function is unnecessary and does not need to use FLUSH PRIVILEGES to perform validation.

注：PASSWORD()函数作用是为口令字加密，在程序中MySql自动解释。 Note: PASSWORD () function role is password encryption, automatic interpretation of MySql in the program.

二、MySql中访问限制的设置方法 Two, MySql access restrictions set method
我们采用两种方法来设置用户。 We use two methods to set the user.

进入到Mysql执行目录下（通常是c:\mysql\bin）。 Into Mysql execution directory (usually c: \ mysql \ bin). 输入mysqld-shareware.exe，输入mysql Enter the mysqld-shareware.exe, enter mysql
--user=root mysql ,不然不能添加新用户。 - User = root mysql, otherwise you can not add new users. 进入到mysql>提示符下进行操作。 Access to the mysql> prompt to operate.

假设我们要建立一个超级用户，用户名为system，用户口令为manager。 Suppose we want to create a super user, the user name system, the user password is manager.

方法一 Method one

用Grant 命令授权，输入的代码如下： Grant authorization to use the command, enter the code below:

mysql>GRANT ALL PRIVILEGES ON *.* TO system@localhost IDENTIFIED BY mysql> GRANT ALL PRIVILEGES ON *.* TO system @ localhost IDENTIFIED BY
'manager' WITH GRANT OPTION; 'Manager' WITH GRANT OPTION;

应显示:Query OK, 0 rows affected (0.38 sec) Should show: Query OK, 0 rows affected (0.38 sec)

方法二 Method Two

对用户的每一项权限进行设置： Each of the user to set permissions:

mysql>INSERT INTO user mysql> INSERT INTO user
VALUES('localhost','system',PASSWORD('manager'), VALUES ('localhost', 'system', PASSWORD ('manager'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y ',' Y ');

对于3.22.34版本的MySQL,这里共14个"Y",其相应的权限如下(按字段顺序排列): For the 3.22.34 version of MySQL, where a total of 14 "Y", the appropriate permissions as follows (field order):


权限表列名称相应解释使用范围 Permission to use the table column names corresponding to explain the scope of
select Select_priv 只有在真正从一个表中检索时才需要select权限表 select Select_priv only really retrieve from a table select permission on the table when needed
insert Insert_priv 允许您把新行插入到一个存在的表中表 insert Insert_priv allows you to insert a new row to an existing table table
update Update_priv 允许你用新值更新现存表中行的列表 update Update_priv allows you to update the existing value with the new list of rows in the table
delete Delete_priv 允许你删除满足条件的行表 delete Delete_priv allows you to delete the table rows satisfy the condition
create Create_priv 允许你创建新的数据库和表数据库、表或索引 create Create_priv allows you to create a new database and database tables, table or index
drop Drop_priv 抛弃(删除)现存的数据库和表数据库或表 drop Drop_priv abandoned (remove) existing databases and database tables or tables
reload Reload_priv 允许您告诉服务器再读入授权表服务器管理 reload Reload_priv allows you to tell the server to read the grant tables into the server management
shutdown Shutdown_priv 可能被滥用(通过终止服务器拒绝为其他用户服务) 服务器管理 shutdown Shutdown_priv may be abused (by terminating the server refused to service to other users) server management
process Process_priv 允许您察看当前执行的查询的普通文本,包括设定或改变口令查询服务器管理 process Process_priv allows you to look at the current execution of the query plain text, including setting or changing the password query server management
file File_priv 权限可以被滥用在服务器上读取任何可读的文件到数据库表服务器上的文件存取 file File_priv permissions on the server can be abused to read any file readable on the server to the database table file access
grant Grant_priv 允许你把你自己拥有的那些权限授给其他的用户数据库或表 grant Grant_priv allows you to put your own rights as those granted to other users of the database or table
references References_priv 允许你打开和关闭记录文件数据库或表 references References_priv allows you to open and close the log file or database table
index Index_priv 允许你创建或抛弃(删除)索引表 index Index_priv allows you to create or drop (remove) index table
alter Alter_priv 允许您改变表格,可以用于通过重新命名表来推翻权限系统表 alter Alter_priv allows you to change the form, can be used to overthrow the authority to rename the table to system table

如果创建用户时只有select、insert、update和delete权限,则允许用户只能在一个数据库现有的表上实施操作. If you create a user with only select, insert, update and delete permissions, allows the user to only one database table to implement the existing operation.

下面就可以创建我们要用到的数据库了,我们直接输入. 例如：我们要创建数据库名为XinXiKu，可用如下代码： Here you can use to create our database, we directly, for example: we want to create a database called XinXiKu, used the following code:

mysql>create database XinXiKu; mysql> create database XinXiKu;

应显示:Query OK, 1 row affected (0.00 sec) Should show: Query OK, 1 row affected (0.00 sec)