MYSQL学习与数据库综合实验(十二)——自主存取控制实验(基于Ubuntu18.04中Docker安装的MySQL8实验)

发布于 2020-12-19  2187 次阅读


12.1MySQL的权限

用户连接到MySQL,可以做各种查询,这都是MySQL用户与权限功能在背后维持着操作。

用户与数据库服务器交互数据,分为两个阶段:

(1)你有没有权连接上来
(2)你有没有权执行本操作

12.1.1你有没有权连接上来

服务器如何判断用户有没有权连接上来?

依据:

1)你从哪里来?host
2)你是谁?user
3)你的密码是多少?password

用户的这三个信息,存储在mysql库中的user表中。

修改host域,使IP可以连接

mysql>``update` `user` `set` `host=``'192.168.137.123'` `where` `user` `= ``'root'``;``mysql>flush ``privileges``; ``--冲刷权限

修改用户密码

mysql>``update` `user` `set` `password``=``password``(``'11111111'``) ``where` `xxx;``mysql>flush ``privileges``; ``--冲刷权限

12.1.2你有没有权执行本操作

在mysql中,有一个库是mysql库,在这个库中有三个表,一个是user表,user表中存储了所有用户的权限信息。一个是db表,db表存储的是所有用户在数据库层的权限信息。一个是tables_priv表,tables_priv表存储的是所有用户在表层的权限信息。

用户登录,user表首先能限制用户登录,其次还保存了该用户的全局权限,如果该用户没有任何权限,那么将从db表中查找该用户是否有某个数据库的操作权限,如果都没有,将从table_priv表中查找该用户是否有某个表的操作权限,如果有,则该用户可以按照已有的权限来操作该表。

12.2创建MySQL的用户

命令:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明:

username:你将创建的用户名.

host:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%代替host。

password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

关于创建用户时提示“Operation CREATE USER failed for XXX”的解决办法

在这里插入图片描述

出现该原因的结果有可能是你使用了

delete  from  mysql.user  where user ='user_1';

的语句删除用户,即使你刷新了 flush privileges;也没用。

删除用户需要使用

drop user   用户
在这里插入图片描述

然后重新进行创建就好了:

create user 'user_1'@'%' identified by '123456';
在这里插入图片描述

12.3用户授权

在这里插入图片描述

12.3.1授权命令格式

GRANT privileges ON databasename.tablename TO 'username'@'host';

privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL;

databasename:数据库名;

tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示,如.*;

例子:

GRANT SELECT, INSERT ON test.user TO 'user_1'@'%';
GRANT ALL ON *.* TO 'user_1'@'%';
GRANT ALL ON maindataplus.* TO 'user_1'@'%';

注意:

用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

12.3.2使用REVOKE命令可以回收授予的权限,语法格式如下:

REVOKE priv_type ON {表名|数据库名} FROM 用户;

注:只有拥有当前数据库全局CREATE或者UPDATE权限的用户才能使用REVOKE命令。

12.3.3全局授权和收回

全局授权格式(使用通配符*):

grant` `[权限1,权限2,权限3] ``on` `*.* ``to` `user``@``'host'` `identified ``by` `'password'

常用权限:all、create、drop、select、insert、delete、update

授权:

创建lisi用户,host为192.168.191.%,%通配符表示192.168.191.xxx结尾的主机都可以连接,密码为12345678。

grant` `all` `on` `*.* ``to` `lisi@``'192.168.191.%'` `identified ``by` `'12345678'``;

收回权限:

revoke` `all` `on` `*.* ``from` `lisi@``'192.168.191.%'``;

12.3.4数据库级授权和收回

需求:让lisi用户拥有mysqlmaster数据库的所有操作权限

授权:

grant` `all` `on` `mysqlmaster.* ``to` `lisi@``'192.168.191.%'` `identified ``by` `'12345678'``;

收回:

revoke` `all` `on` `mysqlmaster.* ``from` `lisi@``'192.168.191.%'``;

12.3.5表级授权和收回

需求:让lisi用户具有mysqlmaster数据库下的goods表的insert、update、select三个操作的权限。

授权:

grant` `insert``,``update``,``select` `on` `mysqlmaster.goods ``to` `lisi@``'192.168.191.%'` `identified ``by` `'12345678'``;

收回:

revoke` `insert``,``update``,``select` `on` `mysqlmaster.goods ``from` `lisi@``'192.168.191.%'``;

12.4更新用户语法格式如下:

12.4.1如果是修改当前登录用户密码语法:

SET PASSWORD FOR 用户=PASSWORD(‘新密码’);

12.4.2通过修改mysql数据库下的user表中的来修改(不可取)

命令和update普通表一样,语法格式如下:

UPDATE MYSQL.USER SET 属性名= 新属性值  WHERE  条件;

比如,修改User的用户名为“user_1”将其用户名改为“user_3”,密码改为“1234”。

注意:MySQL8之前修改密码是 set password= password("新密码");而MySQL8是 set authentication_string = "新密码".也就是MySQL8的密码字段为authentication_string 。

update mysql.user set user ="user_3" ,authentication_string ="1234" where user ="user_1";
在这里插入图片描述

可以发现如果直接使用 set authentication_string ='新密码',那么该密码直接被填充到用户中而没有经过加密。而我们知道,MySQL的密码是经过加密的,因此这样子使用update修改密码是有问题的,在你登录时候虽然用户名和密码都正确,但是却没法进行登录,会提示“ Access denied for user 'user_3'@'117.154.88.249' (using password: YES)”。

​ MySQL8.0后请使用alter修改用户密码,因为在MySQL8.0以后的加密方式为caching_sha2_password,如果使用update修改密码会给user表中root用户的authentication_string字段下设置newpassowrd值,当再使用alter user 'root'@'localhost' identified by 'newpassword'修改密码时会一直报错,必须清空后再修改(如果不首先清空这个authentication_string,在使用alter user 进行修改时会发生“Operation ALTER USER failed for XXX”的提示)
在这里插入图片描述

因为authentication_string字段下只能是MySQL加密后的43位字符串密码,其他的会报格式错误,所以在MySQL8.0以后能修改密码的方法只能是使用alter来修改:

ALTER USER   用户   IDENTIFIED  WITH mysql_native_password BY '新密码';
#或者是
ALTER USER 用户 IDENTIFIED BY ‘新密码’;
在这里插入图片描述

比如:将用户 ‘user_1’@‘%’ 的密码改为“1234”

ALTER USER ‘user_1’@‘%’ IDENTIFIED BY ‘1234’;
#或者
ALTER USER   ‘user_1’@‘%’    IDENTIFIED  WITH mysql_native_password BY '1234';

最后刷新一定要刷新下

flush privileges;

12.5查询某个用户权限

show grants for  用户;

比如:

show grants for 'user_1'@'%';
在这里插入图片描述

12.6删除用户

12.6.1使用drop命令

drop user 用户;

比如:

drop user  'user_1'@'%';
在这里插入图片描述

删除的是 用户 'user_1'@'%'。

12.6.2使用delete命令

delete from  mysql.user  where  条件

比如:

delete from  mysql.user where user='user_1' and host ='%';

删除的是 用户 'user_1'@'%'。

12.6.3使用drop和delete命令的区别

12.6.3.1drop user 用户名;

删除已经存在的用户,例如要删除‘user_1’这个用户,(drop user 'user_1';)默认删除的是user_1@”%”这个用户,如果还有其他用户,例如user_1@”localhost”,user_1@”ip”,则不会一起被删除。如果只存在一个用户'user_1'@”localhost”,使用语句(drop user 'user_1';)会报错,应该用(drop user 'user_1'@”localhost”;)如果不能确定(用户名@机器名)中的机器名,可以在mysql中的user表中进行查找,user列对应的是用户名,host列对应的是机器名。

12.6.3.5 delete from user where user=”用户名” and host=”localhost”;

delete也是删除用户的方法,例如要删除'user_1'@”localhost”用户,则可以

delete from user where user=”user_1” and host=”localhost”;

drop删除掉的用户不仅将user表中的数据删除,还会删除诸如db和其他权限表的内容。而(delete from mysql.user)只是删除了mysql数据库中user表的内容,其他表不会被删除,后期如果命名一个和已删除用户相同的名字,权限就会被继承。

12.7测试用例

12.7.1.创建用户user_1和user_2,密码都为123456
 create user 'user_1'@'%' identified by "123456";
create user 'user_2'@'%' identified by "123456";
在这里插入图片描述
12.7.2.将用户user_2的名称修改为user_3,并将其密码修改为1234
在这里插入图片描述

首先使用update mysql.user语句来更新user_2中的用户名

update  mysql.user set user ='user_3' where  user ='user_2' and host='%';
在这里插入图片描述

然后使用alter命令修改密码:

alter user 'user_3'@'%' identified with mysql_native_password by '1234';

在这里插入图片描述
这样就能完成将user_2表改为user_3表并且修改对应的的代码了。

12.7.3.以user_1身份登陆数据库
在这里插入图片描述

从可以发现可以远程登录了。

12.7.4.授予用户user_1对DBEM数据库中employee表的查询、插入、修改、删除等权限。
grant insert,update,select,delete on dbms.employee to 'user_1'@'%';
在这里插入图片描述
在这里插入图片描述
12.7.5.授予用户user_1对salary表的查询权限,并允许其将权限授予其他用户,然后用user_1登陆数据库并将salary表的查询权限授予user_3。

首先给与user_1对salary表的查询权限并允许其将权限授予其他用户:

grant select on dbms.salary to 'user_1'@'%' with grant option;
在这里插入图片描述

然后使用user_1的用户名和密码登录user_1进入数据库中,并将对salary表的查询权限给user_3:

grant select on dbms.salary to 'user_3'@'%';
在这里插入图片描述
12.7.7.回收user_1的employee表上的select权
revoke select on dbms.employee from 'user_1'@'%';
在这里插入图片描述

可以发现,user_1上的表的select权限已经被收回了。

12.8整理笔记不易,如果有帮助到你,请给个一键三连。

12.9详细的实验资料以及指导PPT请访问笔者GitHub地址:https://github.com/LJF2402901363/database_experiment.git


繁华落尽,雪花漫天飞舞。