MySQL用户与权限

1.用户与权限概述
    MySQL中的所有用户信息都保存在mysql.user数据表中。
使用desc即可查看user表含有的45个字段。（5.7版本中只有42个字段）
（1）账号字段
    Host和User字段共同组成的复合主键用于区分MySQL中的账户，User字段用于代表用户的名称，Host字段表示允许访问的客户端IP地址或主机地址，当Host的值为“*”时，表示所有客户端的用户都可以访问。
    在user表中，除了默认的root超级用户外，MySQL 5.7中还额外地新增了两个用户mysql.session和mysql.sys。前者用于用户身份验证，后者用于系统模式对象的定义，防止DBA（数据库管理员）重命名或者删除root用户时发生错误。
    默认情况下，用户mysql.session和mysql.sys已被锁定，使得数据库操作人员无法使用这两个用户通过客户端连接MySQL服务器。

（2）身份验证字段
    在MySQL 5.7中，mysql.user表中已不再包含Password字段，而是使用plugin和authentication_string字段保存用户身份验证的信息。其中，plugin字段用于指定用户的验证插件名称，authentication_string字段是根据plugin和authentication_string值。
    下面通过select语句查询user表中root用户默认的plugin和authentication_string值。
select plugin,authentication_string from mysql.user where user=‘root’;

（3）安全连接字段
    在客户端与MySQL服务器连接时，除了可以基于账户名以及密码的常规验证外，还可以判断当前连接是否符合SSL安全协议，与其相关的字段有以下几种：
ssl_type：用于保存安全连接的类型，它的可选值有’’(空)、ANY（任意类型）、X509（X509证书）、SPECIFIED（规定的）4种。
ssl_cipher：用于保存安全加密连接的特定密码。
x509_issuer：保存由CA签发的有效的X509证书。
x509_subject：保存包含主题的有效的X509证书。
需要注意的是。通常标准的发行版MySQL默认未启用SSL加密连接，可以通过以下SQL语句查看：
show variables like ‘have_oppenssl’;

（4）资源限制字段
max_questions:保存每小时允许用户执行查询操作的最多次数。
max_updates:保存每小时允许用户执行更新操作的最多次数。
max_connections:保存每小时允许用户建立连接的最多次数。
max_user_connections:保存允许单个用户同时建立连接的最多数量。
    以上列举的用户资源限制字段默认值均为0，表示对此用户没有任何的资源限制。

（5）权限字段
    在mysql.user表中提供的以“_priv”结尾的字段保存了用户的全局权限，如Select_priv查询权限、Insert_priv插入权限、Update_priv个更新权限。
    其中，user表对应的权限字段的数据类型都是ENUM枚举类型，取值只有N或Y两种。N表示该用户没有对应权限，Y表示该用户有对应权限。为了保证数据库的安全，这些字段的默认值都为N。

6）账户锁定字段
    在mysql.user表中提供的account_locked字段用于保存当前用户是锁定还是解锁状态。该字段是一个枚举类型，当其值为N时表示解锁，此用户可以用于连接服务器；当其值为Y时表示该用户已被锁定，不能用于连接服务器使用。

2.用户管理
（1）创建用户
    采用MySQL提供的create user和grant语句创建用户。其中，grant语句在创建用户的同时还可以完成权限的设置。
    使用create user 语句创建每一个新用户，都会在mysql.user表中添加一条记录，同时服务器会自动修改相应的授权表。但需要注意，该语句创建的新用户默认情况下没有任何权限，需要使用grant语句进行授权。

a.创建最简单的用户
create user ‘test1’;
使用select查看mysql数据库下的user表，查看创建的用户是否已添加到user表中保存
select host,user from mysql.user;
（host的值为“%”时，表示当前的用户可以在任何主机中连接MySQL服务器；当其值为“localhost”时，表示当前的用户只能从本地主机连接MySQL服务器。）
注：用户名和主机名在设置时，若不包含空格、“-”等特殊字符，则可以省略引号。另外，当创建的用户名称为空字符串时，表示创建的是一个匿名用户（登录时，不需要输入用户名和密码。不推荐使用）。

b.创建含有密码的用户
create user ‘test2’@‘localhost’ identified by ‘123456’;
identified by后指定的是字符串形式的明文密码，接着通过select查询存储在user表中test2用户的密码。
select plugin,authentication_string from mysql.user
where user=‘test2’;
由运行结果可知，创建用户时设置的明文密码，在user表中将其转换为暗码。（在MySQL 5.5中，使用select Password from mysql.user where user=‘test2’;进行查询）
    除此之外，在设置用户密码时还可以指定对密码加密的插件，只需将“identified by ‘明文密码’”修改为指定的选项即可。
create user ‘test2’@‘localhost’ identified with ‘mysql_native_password’ by ‘123456’;
（MySQL 8.0.18中应写成create user ‘test2’@‘localhost’ identified with ‘caching_sha2_password’ by ‘123456’;）

c.同时创建多个用户
create user
‘test3’@'localhost’identified by ‘333333’,
‘test4’@'localhost’identified by ‘444444’;
    多个用户之间使用逗号隔开，省略用户身份验证选项时，表明此用户在登陆服务器时可以免密登录，但为了保证数据安全，不推荐这样做。

d.设置用户可操作资源范围
例如，创建一个名为test5的本地用户，限制其每小时最多可以更新10次。
create user
‘test5’@‘localhost’identified by’555555’
with max_updates_per_hour 10;
查看user表中max_updates字段（使用mysql数据库）
select max_updates from user where user=‘test5’;

d.设置有密码期限的用户
创建一个名为test6的用户，并设置其密码每180天更新一次。
create user ‘test6’@‘localhost’ identified by '666666’password expire interval 180 day;

e.设置用户是否锁定
被锁定的用户不能在客户端登录MySQL服务器。下面创建一个锁定的用户test7.
create user ‘test7’@‘localhost’ identified by ‘777777’ password expire account lock;
利用select查看表mysql.usesr表中名为test7的account_locked字段，若其值为Y。则表示当前创建的用户已被锁定。
select account_locked from mysql.user where user=‘test7’;
此时若利用test7在客户端登录MySQL服务器，会报“账户被锁定，拒绝访问test7@localhost”用户的提示信息。

2）设置密码
a.为指定用户设置密码
    mysql.user表中的test1用户是一个可以在任何主机上连接MySQL服务器的用户，但此用户在创建时未设置密码，下面将密码设置为123456.
alter user ‘test1’@’%’ identified by ‘123456’;

b.为登录用户设置密码
    若当前通过客户端连接MySQL服务器的用户是非匿名用户，则可以使用user()函数更改自己的密码，而无须直接为自己的账户命名。
    例如，修改当前正在连接MySQL服务器的root用户密码，将其设置为000000.
alter user user() identified by ‘000000’;
user()函数获取的是客户端提供的用户和主机地址，它可能与当前通过MySQL服务器验证的用户与主机名不同，此时可以利用current_user()函数获取。
select current_user();

c.mysqladmin修改用户密码
    在MySQL安装目录bin下还有一个mysqladmin.exe应用程序，它通常用于执行一些管理性的工作，以及显示服务器状态等。同时，在MySQL中也可以使用该命令修改用户的密码。
例如，在命令窗口中，使用mysqladmin命令，将root用户的密码修改为123456
mysqladmin -u root -p password 123456
回车后需要输入原密码

d.root密码丢失找回
    若忘记MySQL服务的root用户密码，就不能通过以上设置密码的方式找回或重置。此时可以在MySQL的配置文件my.ini中添加skip-grant-tables选项，然后重启MySQL服务后再次利用root用户登录，就可以跳过密码的输入直接登录MySQL服务器，为root用户设置密码。
（但此种方法存在非常大的安全风险。建议慎用！）

（3）修改用户
a.修改验证插件
例如，将mysql.user表中名为test1的用户验证插件修改为sha256_password，密码修改为111111，并将密码设置为立刻过期。
alter user test1
identified with sha256_password by ‘111111’
password expire;
下面查看名为test1且插件算法为sha256_password的authentication_string字段的值。
select authentication_string from mysql.user
where user='test1’and plugin=‘sha256_password’;
（用户的加密算法不同，则此记录对应的authentication_string字段保存的值也不同。）

b.解锁用户
解锁mysql.user表中被锁定的用户test7.
alter user ‘test7’@'localhost’account unlock;

c.同时修改多个用户的资源限定
例如，修改mysql.user表中的test1和test2用户，限定单个用户最多可同时建立两个连接，同时将test1的验证插件修改为MySQL的默认值，test2用户的密码修改为222222.
alter user
‘test1’ identified with mysql_native_password,
‘test2’@‘localhost’ identified by ‘222222’
with max_user_connections 2;

d.删除用户
    MySQL 5.x版本之后drop user语句可以同时删除一个或多个MySQL中的指定用户，并会同时从授权表中删除账户对应的权限行。在MySQL 5.x之前的版本中，在删除用户前必须先回收用户的权限。
drop user if exists test7;
    在不添加if exists关键字时，若删除了一个不存在的用户，则该语句的执行会发生错误；在添加后，会在删除不存在的用户时生成一个警告作为提示。其中，在删除账户时，如果省略主机地址，则默认为“’%’”。

3.权限管理
（1）授予权限
查看一下root和test1用户被授权的情况
show grants for ‘root’@‘localhost’;
show grants for ‘test1’@’%’;
查询结果中all privileges表示除grant option（授权权限）和proxy（代理权限）外的所有权限，usage表示没有任何权限。on后的“ * . * ”表示全局级别的权限，即MySQL服务器下的所有数据库下的所有表，“"@"”表示任何主机中的匿名用户。
grant select,insert (name,price) on sh_goods to ‘test1’@’%’;
上述语句中，select权限是表级权限（sh_goods表），insert是列级权限（sh_goods表中的name和price字段）。因此，在SQL语句执行成功后，若要查test1的权限，可以到mysql.tables_priv中查看表权限，到mysql.columns_priv中查看列权限。
select db,table_name,table_priv,column_priv from mysql.tables_priv where user=‘test1’;
select db,table_name,column_name,column_priv from mysql.columns_priv where user=‘test1’;
（2）创建用户
grant select on * .* to ‘test8’@’’;
    在MySQL 5.7中，当grant语句中指定的账户不存在时，系统不支持自动创建用户，它会报一个在user中找不到用户的提示信息。例如上述语句（test8不存在）。
为了解决上述问题，只需确保MySQL中no_auto_create_user模式未开启，就可以利用grant自动创建一个不存在的用户。
set sql_mode=’’;
grant select on * .* to ‘test8’@’’; #MySQL5.7z中
    grant创建新用户的方式已经被遗弃，并在未来会被移除。高版本中已经不能执行，因此，MySQL官方推荐使用create user语句创建用户，使用alter user语句修改用户的非特权选项（如验证插件、资源控制选项等），使用grant为新用户授予权限。

（3）回收权限
    下面以删除test1用户对sh_goods表的name和price字段的插入权限为例进行演示。
revoke insert(name,price) on sh_goods from ‘test1’@’%’;
（执行上述操作后，用test1用户登录MySQL服务器，向sh_goods表中插入数据，就会报错。）

（4）刷新权限
    从系统数据库mysql中的权限表中重新加载用户的权限。原因在于grant、create user等操作会将服务器的缓存信息保存到内存中，而revoke、drop user操作并不会同步到内存中，因此可能会造成服务器的内存消耗，所以在revoke、drop user后推荐读者使用MySQL提供的flush privileges重新加载用户的权限。
flush privileges;
另外刷新权限也可以利用mysqladmin命令完成，如下：
mysqladmin -uroot -p reload
或mysqladmin -uroot -p flush-privileges