花开富贵 的个人博客

1 什么是用户管理

在MySQL中, 通常情况下有着严格的用户管理, 对用户权限的划分甚至细致到某个用户针对某个数据库中某张表的某些操作;

默认在没有创建其他用户的情况下, MySQL中的可用用户只有Root用户, 而通常情况下的root用户有着极高的权限, 因此在大部分业务场景中, 通常不使用root用户, 而是使用普通用户;

这种用户管理类似于各个系统下的用户管理, 就譬如Ubuntu下的用户管理;

由于MySQL中存储的是用户的某些数据, 而数据库中必然存在一定程度的敏感数据, 因此通常由MySQL指定某些用户只能对某些库/表给予不过高的权限;

假设当前MySQL中存在三个用户, 分别为root, userA, userB, 其中我们知道权限最大的是root管理者, 而在MySQL中存在许多与自身系统相关的若干个库, 而作为普通用户的userA, userB, 其管理者并不期望将MySQL系统级的数据库/表暴露给普通用户, 因此就可以对用户进行相应的管理;

在MySQL中除了root用户以外还有三个系统级用户, 分别为mysql.infoschema, mysql.session以及mysql.sys, 但这三个用户在创建时就被锁定, 无法被用户使用, 此处不做过多介绍;

2 用户

2.1 用户信息

MySQL作为一个数据库, 其用户信息同样是以表的方式存储在数据库当中;

通常在mysql->user中;

该表的结构为如下:

由于表内Column过多, 无法很好的显示, 此处摘选出关键列进行显示;

(此处的dio用户为以往创建的用户, 此处忽略);

其中这里的三列分别为:

通常情况下, 在MySQL中进行用户的增删改查操作, 本质上就是在user表中进行CURD操作;

如, 我们可以通过对user表进行INSERT作数据插入从而添加新用户;

但从表结构来看, 插入一条数据需要维护众多的列, 并且需要保证在数据插入过程中保持正确插入, 这样的话;

"那也太逊了"

2.2 创建用户与删除用户

在上文中, 提到了, 当对用户进行创建时, 若是只使用INSERT语句进行插入, 那么需要维护过多的列属性, 通常无法在插入中对其进行很好的维护;

而在MySQL中, 其对用户的创建提供了一个用于创建用户的语句:

CREATE USER 'username'@'host' identified by 'Password';

• 示例

  假设需要创建一个用户名为'ZhangSan', 且只允许在本地登录, 同时密码为123456的用户;

  

但所创建的用户属于最低权限, 无法进行过多操作;

关于权限问题, 将在后面进行解答;

同样的, 该数据库并不能进行远程登录, 本质上是因为在创建用户的时候就指名了localhost登录方式;

可以看到, 当试图使用远程登录时, 将被权限拒绝;

通常情况下, 若是需要远程登录时, 需要指名允许远程登录设备的IP地址, 或是设置为0.0.0.0或是%将运行所有IP对该数据库进行登录(此处为演示, 切记一定不能将数据库暴露在公网之中);

由于在创建用户时指明了用户名与允许登录的主机, 因此无法通过创建新用户进行覆盖, 通常有三种解决办法:

1. 直接在user表中进行CURD操作进行修改
2. 将用户进行删除并重新创建新用户
3. 使用RENAME进行重命名

当然第三种办法是最优的办法, 但此处我们重点介绍创建与删除用户, 因此演示删除;

删除用户通常使用:

DROP user 'username'@'host';

进行删除;

• 示例

  drop user 'ZhangSan'@'localhost';
  

  

  从结果看出, 对应的ZhangSan用户已经被删除;

删除后我们需要通过重新创建一个允许任意主机登录的 'ZhangSan'用户;

create user 'ZhangSan'@'%' identified by '123456';

查看远程登录情况;

2.3 修改密码

同样的, 修改密码可以直接对表进行修改, 但在修改时需要使用password('')的方式进行加密;

如:

# -- <= mysql5.7
UPDATE user set authentication_string=password('newpassword') WHERE USER='username' AND HOST='host';
# -- > mysql5.7
UPDATE user set authentication_string=SHA2('newpassword', 256) WHERE USER='username' AND HOST='host';

针对高版本(>5.7)的mysql中, 这里似乎是已经修改了, 但实际上, 并没有完全修改, mysql并不是仅仅通过hash来完成的, 还需要进行加盐等其他的配置(根据插件而定), 因此若是只使用SHA2来进行加密, 那么将会认证失败;

不同版本的mysql有不同版本的设置密码的语法方式;

• 针对version <= mysql 5.7

  修改自己密码的语法为:

  set password=password('newPassword');
  

  root用户指定修改用户密码:

  set password for 'username'@'host'=password('newPasswprd');
  

• 针对version > mysql 5.7

  ALTER USER 'ZhangSan'@'%' IDENTIFIED BY 'newpassword';
  

本质的区别是版本不同, PASSWORD()函数在> 5.7的版本中被废弃, 因此老方法不兼容;

新方法进行修改密码为:

alter user 'ZhangSan'@'localhost' identified by 'newpassword';

修改密码后可以通过新密码正常登录;

2.4 修改用户名与登录主机

在上文中, 我们通过DROP删除与CREATE的方式进行修改, 而实际上在MySQL中提供了对应的RENAME方式用于进行修改用户名与允许登录的主机;

语法为:

RENAME USER 'oldusername'@'oldhost' to 'newusername'@'newhost';

• 示例

  rename user 'ZhangSan'@'localhost' to 'LiSi'@'%';
  

  

  从图中可以观察到, 'ZhangSan'@'localhost'被改为了 'LiSi'@'%';

3 权限管理

通常情况下, 在MySQL中的新用户创建其权限通常为裸权限;

若是需要让新用户能够进行增删改查或者其他操作, 需要为他赋予其对应所需的权限;

常见的权限有如下:

通常可以通过SHOW GRANTS查看当前用户的权限;

在root用户或者拥有对应权限的用户下, 可以采用SHOW GRANTS FOR ...的方式查看权限;

SHOW GRANTS FOR 'username'@'host';

3.1 为用户授权

为用户授权的语法为:

GRANT 权限列表 ON database_name.object_name to 'username'@'host' [identified by 'password'];

• 权限列表

  1. 单权限

     GRANT SELECT(这里指代查询权限) ON ....
     

  2. 多权限

     GRANT SELECT, DELETE, .... ON ....
     

     多权限时权限与权限之间采用逗号, 进行分隔;

  3. 所有权限

     GRANT ALL [PRIVILEGES] ON ...
     

     表明赋予该用户在该对象上的所有权限;

此处的database_name表示数据库的库名, object_name为对象名, 这里的对象包括, 表, 视图以及存储过程等, 库与对象之间采用 .进行连接;

可以使用通配符*表名所有;

• xxx.*

  表示某个数据库的所有对象;

• *.*

  表示所有数据库的所有对象;

以此类推;

除此之外在上述语法中存在一个可选项[identified by 'password'], 当使用该可选项时存在两种情况:

• 用户存在

  赋予权限并更新密码;

• 用户不存在

  赋予权限并创建用户;

3.2 回收权限

回收权限的语法与赋予权限的语法类似;

赋予权限通过GRANT XXX, 而回收权限则是通过REVOKE XXX;

REVOKE 权限列表 ON database_name.object_name FROM 'username'@'host';

除了没有[identified by 'password']可选项以外, 没有其他不同, 此处不进行过多描述;