mysql 用户管理

一、权限表

MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许的权限。MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。用户管理包 括管理用户账户、权限等。

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中，由 mysql_install_db 脚本初始化。存储账户权限信息表主要有：user、db、host、tables_priv、columns_priv 和procs_priv。

1.1 user表

user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是 全局级的。例如：一个用户在user表中被授予r DELETE权限，则该用户可以删除MySQL服务 器上所有数据库中的任何记录。MySQL5.5中user表有42个字段，这些字段可以 分为4类，分别是用户列、权限列、安全列和资源控制列。

1.用户列
user表的用户列包括Host、User、Password.分别表示主机名、用户名和密码。其中User和 Host为User表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主 机名和密码必须匹配User表中对应的字段，只有3个值都匹配的时候，才允许连接的建立。这3 个字段的值就是创建账户时保存的账户信息。修改用户密码时，实际就是修改user表的Password 字段的值。

2.权限列
权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包 括査询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等髙级权限。普通 权限用于操作数据库：高级权限用于数据库管理。
user表中对应的权限是针对所有用户数据库的。这些字段值的类型为ENUM,可以取的值只 能为Y和N, Y表示该用户有对应的权限；N表示用户没有对应的权限。査看user表的结构可以 看到，这些字段的值默认都是N。如果要修改权限，可以使用GRANT语句或UPDATE语句更改 user表的这些字段来修改用户对应的权限。

3.安全列
安全列只有6个字段，其中两个是ssl相关的，2个是x509相关的，另外2个是授权插件相关 的。ssl用于加密；X509标准可用于标识用户；Plugin字段标识可以用于验证用户身份的插件，如 果该字段为空，服务器使用内建授权验证机制验证用户身份。读者可以通过SHOW VARIABLES LIKE 'have_openssl’语句来査询服务器是否支持ssl功能。

4.资源控制列
资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为：
(1) max_questions―用户每小时允许执行的查询操作次数。
(2) max_updates―用户每小时允许执行的更新操作次数。
(3) max connections 用户每小时允许执行的连接操作次数。
(4) max user connections 用户允许同时建立的连接次数。
一个小时内用户査询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时，才 可以在此执行对应的操作。可以使用GRANT语句更新这些字段的值。

1.2 db 表和 host 表

db表和host表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作 权限，决定用户能从哪个主机存取哪个数据库。host表中存储了某个主机对数据库的操作权限，配 合db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANT和 REVOKE语句的影响。db表比较常用，host表一般很少使用。db表和host表结构相似，字段大致 可以分为两类：用户列和权限列。

1.用户列
db表用户列有3个字段，分别是Host、User、Db,标识从某个主机连接某个用户对某个数据 库的操作权限，这3个字段的组合构成了 db表的主键。host表不存储用户名称，用户列只有2个 字段，分别是Host和Db,表示从某个主机连接的用户对某个数据库的操作权限，其主键包括Host 和Db两个字段。host很少用到，一般情况下db表就可以满足权限控制需求了。

2.权限列
db表和host表的权限列大致相同，表中create_routine_priv和alter_routine_priv这两个字段表 明用户是否有创建和修改存储过程的权限。
user表中的权限是针对所有数据库的，如果希望用户只对某个数据库有操作权限，那么需要将 user表中对应的权限设置为N,然后在db表中设置对应数据库的操作权限。例如，有一个名称为 Zhangting的用户分别从名称为large.domain.com和small.domain.com的两个主机连接到数据库，并 需要操作books数据库。这时，可以将用户名称Zhangting添加到db表中，而db表中的host字段 值为空，然后将两个主机地址分别作为两条记录的host字段值添加到host表中，并将两个表的数

据库字段设置为相同的值bookso当有用户连接到MySQL服务器时，db表中没有用户登录的主机 名称，则MySQL会从host表中查找相匹配的值，并根据查询的结果决定用户的操作是否被允许。

1.3 tables_priv 表和 columns_priv 表

tables_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。

tables_priv 表有 8 个字段，分别是 Host^ Db、User、Table_name^ Grantor、Timestamp、Table_priv 和Column_priv,各个字段说明如下：
(1) Host、Db、User和Table_name4个字段分表示主机名、数据库名、用户名和表名。
(2) Grantor表示修改该记录的用户。
(3) Timestamp字段表示修改该记录的时间。
(4) Table_priv 表示对表的操作权限包括 Select、Insert、Update> DeleteCreateDrop> Grant、 Referencesx Index 和 Alter。
(5) Columnj)riv字段表示对表中的列的操作权限，包括Select. Insert. Update和References。

columns_priv 表只有 7 个字段，分别是 Host、Db、User、Table name、Column name、Timestamp、 Column_privo其中，Column name用来指定对哪些数据列具有操作权限。

1.4 procs_priv 表

procs_priv表可以对存储过程和存储函数设置操作权限。

procs_priv 表包含 8 个字段，分别是 Host^ Db、User> Routine_name> Routine type、Grantor、 Proc_priv和Timestamp,各个字段的说明如下：
(1) Host、Db和User字段分别表示主机名、数据库名和用户名。Routine_name表示存储过 程或函数的名称。
(2) Routinejype表示存储过程或函数的类型。
(3) Routinejype 字段有两个值，分别是 FUNCTION 和 PROCEDURE。FUNCTION 表示这 是-个函数：PROCEDURE表示这是一个存储过程。
(4) Grantor是插入或修改该记录的用户。
(5) Proc_priv 表示拥有的权限，包括 Execute> Aller Routinex Grant 3 种。
(6) Timestamp表示记录更新时间。

二、账户管理

MySQL提供许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服 务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性，需要通过账户管理来保证。

2.1 登录和退岀MySQL服务器

通过mysql -help命令可以查看mysql命令帮助信息。mysql命令的常用参数如下:
(1) -h主机名，可以使用该参数指定主机名或ip,如果不指定，默认是localhosto
(2) -u用户名，可以使用该参数指定用户名。
(3) -p密码，可以使用该参数指定登录密码。如果该参数后面有一段字段，则该段字符串将 作为用户的密码直接登录。如果后面没有内容，则登录的时候会提示输入密码。注意：该参数后面 的字符串和-P之前不能有空格。
(4) -P端口号，该参数后面接MySQL服务器的端口号，默认为3306。
(5) 数据库名，可以在命令的最后指定数据库名。
(6) -e执行sql语句。如果指定了该参数，将在登录后执行-e后面的命令或sql语句并退出。 可以使用mysql -user=monty -password=guess db name登录服务器。

如果想用较短的选项，命令应为：

mysql -u monty -pguess db name

示列: 使用root用户登录到本地MySQL服务器的test库中

mysql -uroot -p -hlocalhost test

2.2 新建普通用户

创建新用户，必须有相应的权限来执行创建操作。在MySQL数据库中，
有两种方式创建新用 户：
一种是使用CREATE USER或GRANT语句；
另一种是直接操作MySQL授权表。
最好的方法是使用GRANT语句，因为这样更精确，错误少。

2.2.1 使用CREATE USER语句创建新用户

CREATE USER user_specification
	[,user specification]...

user_specification:
	user@host
	[
	IDENTIFIED BY [PASSWORD] 'password'
	| IDENTIFIED WITH auth_plugin [AS 'auth_string'] ]

• user 表示创建的用户的名称；
• host 表示允许登录的用户主机名称；
• IDENTIFIED BY 表示用来 设置用户的密码；
• [PASSWORD] 表示使用哈希值设置密码，该参数可选；
• 'password' 表示用户登 录时使用的普通明文密码；
• IDENTIFIED WITH 语句为用户指定一个身份验证插件；
• auth_plugin 是 插件的名称，插件的名称可以是一个带单引号的字符串，或者带引号的字符串；
• auth_string 是可选 的字符串参数，该参数将传递给身份验证插件，由该插件解释该参数的意义。
  
  

        CREATE USER语句会添加一个新的MySQL账户。使用CREATE USER语句用户，必须有全 局的CREATE USER权限或MySQL数据库的INSERT权限。每添加一个用户，CREATE USER语 句会在mysql.user表中添加一条新记录，但是新创建的账户没有任何权限。如果添加的账户己经存 在，CREATE USER语句会返回一个错误。

示列：使用CREATE USER创建-个用户，用户名是Jeffrey,密码是mypass,主机名是 localhost

CREATE USER 'jeffrey@localhost' IDENTIFIED BY 'mypass';

2.2.2 使用GRANT语句创建新用户

CREATE USER语句可以用来创建账户，通过该语句可以在user表中添加一条新的记录，但 是CREATE USER语句创建的新用户没有任何权限，还需要使用GRANT语句赋予用户权限。而 GRANT语句不仅可以创建新用户，还可以在创建的同时对用户授权。GRANT还可以指定账户的 其他特点，如使用安全连接、限制使用服务器资源等。使用GRANT语句创建新用户时必须有 GRANT权限。GRANT语句是添加新用户并授权他们访问MySQL对象的首选方法。

GRANT privileges ON db.table
TO user@host [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password']]
[WITH GRANT OPTION];

• privileges 表示赋予用户的权限类型
• db.table 表示用户的权限所作用的数据库中的表；
• IDENTIFIED BY 关键字用来设置密码；
• 'password' 表示用户密码；
• WITH GRANT OPTION 为可选 参数，表示对新建立的用户赋予GRANT权限，即该用户可以对其他用户赋予权限。

示列：使用GRANT语句创建一个新的用户testUser,密码为testpwd,并授于用户对所有数据表的SELECT和UPDATE权限

GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhosf'
IDENTIFIED BY 'testpwd';

2.2.3 直接操作MySQL用户表

通过前面的介绍，不管是CREATE USER或者GRANT,在创建新用户时，实际上都是在user 表中添加一条新的记录。因此，可以使用INSERT语句向user表中直接插入一条记录来创建一个 新的用户。使用INSERT语句，必须拥有对mysql.user表的INSERT权限。

INSERT INTO mysql.user(Host, User, Password, [privilegelist])
VALUES('host', 'username', PASSWORD('password'), privilegevaluelist);

• Host、 User、Password 分别为user表中的主机、用户名称和密码字段；
• privilegelist 表示用户 的权限，可以有多个权限；
• PASSWORD 函数为密码加密函数；
• privilegevaluelist 为对应的权限的 值，只能取’Y’或者’N’。

示列：使用INSERT创建一个新账户，其用户名称为customerl,主机名称为localhost, 密码为customerl

INSERT INTO user (Host,User,Password)
VALUES('localhost','customerl',PASSWORD('customerr'));

2.3 删除普通用户

2.3.1 使用DROP USER语句删除用户

DROP USER user [, user];

DROP USER语句用于删除一个或多个MySQL账户。要使用DROP USER,必须拥有MySQL 数据库的全局CREATE USER权限或DELETE权限。使用与GRANT或REVOKE相同的格式为每个账户命名；例如，‘jefTrey’@‘localhost’, 账户名称的用户和主机部分与用户表记录的User和Host 列值相对应。

DROP USER 'user'@'localhost';
DROP USER;

第1条语句可以删除user在本地登录权限；
第2条语句可以删除来自所有授权表的账户权限记录。

2.3.2 使用DELETE语句删除用户

DELETE FROM mysql.user WHERE host='hostname' and user='username'

2.4 root用户修改自己的密码

2.4.1 使用mysqladmin命令在命令行指定新密码

mysqladmin -u root -h localhost -p password "rootpwd"

2.4.2 修改mysql数据库的user表

UPDATE mysql.user set Password=password("rootpwd2")
WHERE User='root' and Host='localhost';

2.4.3 使用SET语句修改root用户的密码

SET PASSWORD=password('rootpwd3M');

2.5 root用户修改普通用户密码

2.5.1 使用SET语句修改普通用户的密码

SET PASSWORD FOR 'testUser'@'localhost'=PASSWORD('newpwd');

2.5.2 使用UPDATE语句修改普通用户的密码

UPDATE mysql.user SET Password=PASSWORD('newpwd')
WHERE User='testUser' AND Host='localhost';

2.5.3 使用GRANT语句修改普通用户密码

GRANT USAGE ON *.* TO 'someuser'@'%' IDENTIFIED BY 'somepassword';

2.6 普通用户修改密码

普通用户登录MySQL服务器后，通过SET语句设置自己的密码。
SET语句修改自己密码的基本语法如下：

SET PASSWORD = PASSWORD('newpassword');

2.7 root用户密码丢失的解决办法

1）修改vim /etc/my.cnf
在最下面加上
skip-grant-tables
2）重启
service mysqld restart
3) 修改密码
UPDATE mysql.user SET authentication_string=PASSWORD(‘新密码’) WHERE USER=‘数据库管理员账号’ AND HOST = ‘localhost’;
flush privileges;
quit;
4）删除skip-grant-tables
（第一步添加的）
5）重启
service mysqld restart