评论

收藏

[MySQL] MySQL深入研究:用户管理

数据库 数据库 发布于:2021-06-26 10:51 | 阅读数:357 | 评论:0

俗世游子:专注技术研究的程序猿
  
说在前面的话


  安装完成之后,默认情况下会有一个账户:root,该账户拥有数据库最高权限,如果我们下发root账户做开发账号,很容易出现误删系统库的问题,所以我们最好能够新建账户,授予需要的权限就好
  下面我们来看看该如何操作

操作MySQL

  当前MySQL环境: 8.0.23

连接到MySQL

  再回顾一下,上节聊到通过命令行连接到MySQL的命令:
mysql -uroot -p
  -u表示要跟上用户名,-p表示密码,其实可以写成这样:
mysql -uroot -p123456
  这种方式只适合用于简单的密码,如果一些密码中存在特殊字符,那么就不行了
  当然,我们还可以连接到远程服务器,通过-h来操作:
mysql -h127.0.0.1 -uroot -p
  连接本地MySQL的话,可以省略-h

进入到MySQL

  连接进来之后,我们可以通过以下方式查看有多少数据库:
show databases;
  
DSC0000.png

  MySQL刚刚安装,包含这5个库,其中:

  • information_schema:其中存储整个数据库中的所有结构和信息,包含表结构,字段等信息
  • mysql:存储数据库中用户,角色等等的信息
  • sys:其中只有一张表,存储的是系统配置信息
  • performance_schema:存储MySQL监控信息,如果SQL执行时间,CPU占用率,线程数等等很多的信息,如果我们想做一个MySQL监控工具,这个库中的表很关键


  • test:这个库就是一个测试库,我们一般操作都是在这里或者是新建库
  如果我们想要进入到某一个库,通过use来进入
use test;
  这样就进入到了test库中
  如果不想用test,想自己创建的话,那么通过help create database可以查看详细的说明
create database test1;
用户管理

角色:create role

  这里需要注意一点,角色这一特性在MySQL 8.x之下的版本是不存在的
CREATE ROLE [IF NOT EXISTS] role [, role ] ...
  创建角色的方式非常简单,通过这一sql就可以创建完成,同时创建角色的同时也可以指定其Host方式,比如:
CREATE ROLE 'administrator', 'developer';
CREATE ROLE 'webapp'@'localhost';
  那么,创建好的角色我们应该在哪里查看?进入到mysql库,通过show tables可以查看所有的表。事实上,创建好的角色保存在了user表中,是不是很奇怪
select Host,User,plugin from user;
  
DSC0001.png

  可以看到,我们新建的3个角色

用户:create user

  成功进入到MySQL之后,我们就开始创建用户吧。
  记住,想要新建用户,必须拥有特定的权限,不然是无法新建用户的:

  • CREATE USER的权限
  • mysql库中user表的INSERT权限
help create user;
create user新建用户的格式很长,我们只看我们常用的,其他的大家自己尝试  下面我们通过实际语句来操作一下:
创建用户并设置密码
CREATE USER 'test1'@'%' IDENTIFIED BY 'ASDFghjk123!@#';
%表示为连接主机名,表示任意客户端都可以正常连接,如果设置为localhost,那么只有本地可以连接,当前可以设置为IP等,  
DSC0002.png

  可以发现是可以正常进入的,只有一个库,而且没有任何权限
  权限后面说,创建用户的过程还没有完
  创建好的用户会存储在mysql/user表中,我们只需要进入到这种表查看:
use mysql
select Host,User,plugin from user;
  
DSC0003.png

  所以说,MySQL8.x的版本默认创建的身份验证方式是:caching_sha2_password,下面我们指定一下验证方式的方式来创建
caching_sha2_password这种方式一些远程客户端是无法验证通过的,需要指定到之前的验证方式
  创建用户并指定身份验证
CREATE USER
  'test2'@'localhost' IDENTIFIED WITH mysql_native_password
                   BY 'ASDFghjk123!@#';
  
DSC0004.png

创建用户并指定角色
  前面提到,单纯的创建用户没有任何操作权限,所以我们在创建的时候通过指定角色的方式来让用户拥有权限
CREATE USER 'test3'@'%' IDENTIFIED WITH mysql_native_password BY 'ASDFghjk123!@#' DEFAULT ROLE administrator, developer;
  这里在指定角色的时候,角色必须是要存在的,不然会报错

DSC0005.png
  其中关系对应存在存储在default_roles中

DSC0006.png
  这里想象成RBAC形式的授权方式,用户只需要授权角色,当前用户就可以拥有该角色下的所有资源
  
DSC0007.png


修改:alter user

  如果我们用户已经是存在的,比如:root用户,我们需要对该用户进行调整,我们只能通过alter user来调整了
help alter user;
  同样,通过help来查看当前如何使用,这里就不贴图了,下面具体看实例
查看当前用户
  如果我们想要查看我们当前登录的用户,可以通过以下方式
select CURRENT_USER();
  
DSC0008.png

修改指定用户密码和身份验证
ALTER USER 'test2'@'localhost'
  IDENTIFIED WITH mysql_native_password
       BY 'ZXCvbn457$%^';
记住,如果我们在修改一个没有的用户,那么就会报错,也就是说这里是关键:'test2'@'localhost'
  上一节里面为了能够让root用户远程连接,我们是用过这个命令的
  修改指定用户的角色

  同样的,如果需要修改用户的授权角色的话,那么我们也可以通过这种方式来进行操作
ALTER USER 'test3'@'%' DEFAULT ROLE administrator;
  这样我们通过select * from default_roles就可以查询到最终授权
  修改完之后,需要刷新一下资源才能生效
FLUSH PRIVILEGES;
资源授权

  相信大家也发现了,我们现在只是创建好了角色和用户,但是无法进行任何操作,所以我们接下来就对这些进行授权,让我们可以通过我们新建的用户进行正常操作
  授权语句采用GRANT来进行操作,我们通过help来查看具体的使用方式:
help grant
  通过help其实我们可以看到,官方除了给出了和具体的介绍,还给出了实际案例

DSC0009.png
  我们不做程序,我们只是程序的搬运工

单用户授权

  先来看个简单的操作:
grant all on *.* to 'test2'@'localhost';
执行完最好刷新一下资源flush privileges;
  如果我们通过test2这个用户就可以执行正常操作了
  下面我们就来解释下各个含义:

  • all:表示操作,是指SELECT,UPDATE,INSERT,DELETE等很多很多的权限
具体权限信息请查看:MySQL支持的权限


  • \*.\*:前一个表示库名,后一个表示表名,<em>表示全部,可以操作全部的库包括系统库。如果想要操作某一个库或者某个库下的某一张表,那么就可以通过具体名称指定的方式来限制
grant all on test.user to 'test2'@'localhost';
  如果我们是想给表中的某个字段授予权限,那么授予的每个权限都必须跟上列名
grant select(id), insert(login_name, login_pwd) on test.user to 'test2'@'localhost';
  如果我们想通过表来查看具体的授权信息,那么我们需要关注mysql库中关于</em>_priv的表,比如:
create user 'test1'@'%' identified with mysql_native_password by 'ASDfghjkl456^&*';
grant update on test.user to 'test1'@'%';
flush privileges;
  对应的关系就存储在这里了

DSC00010.png
  而且对应的效果也在这里,可以很明显的看出来

DSC00011.png
  关于用户的授权方式就到这里了,大家可以自己尝试一下

角色授权

  除了用户,角色的授权方式也非常重要,这样同一组授权资源通过角色授权就非常方便了
  首先,我们先来看如何将角色授权给用户
GRANT 'role1', 'role2' TO 'test3'@'%';
  需要注意的是,这里角色和用户都是必须要存在的,还记得创建角色和用户的语句么?
create role role1, role2;
create user 'test3'@'%' identified with mysql_native_password by 'ASDfghjkl456^&*';
  接下来我们通过grant给角色授权,其实和给用户授权是一样的方式
grant select on test.user to 'role1';
grant delete on test.user to 'role2';
  现在将角色授权给用户
GRANT 'role1', 'role2' TO 'test3'@'%';
  和直接授权用户不同的一点在于:

  • 在帐户会话中,授予用户帐户的角色可以是活动的,也可以是不活动的。如果授予的角色在会话中处于活动状态,则其权限适用;否则,他们相反
  如果我们查看当前角色是否活动状态,那么通过SELECT CURRENT_ROLE();来查看
  出现NONE说明是不活动的状态,我们需要激活角色
SET DEFAULT ROLE role1, role2 TO 'test3'@'%'
SET DEFAULT ROLE ALL TO 'test3'@'%'
  如果想要激活所有权限的话,那么就通过all来设置
  这样就正常了,具体表现就不贴图了,大家自己尝试下

最后的话

  关于其他更多的操作,通过查看MySQL官网来看,下面给出具体网址:
MySQL账户设置
关注下面的标签,发现更多相似文章