(一)实验目的
掌握用户、权限、视图等操作。
(二)实验内容
(1)创建用户test
create user 'test'@'%' ;
(2)创建视图view1从sh_goods表中查询出id,name。权限控制使用默认值,且默认安全控制由定义视图的用户权限来执行
create SQL SECURITY DEFINER view view1 as
select id,name from shop.sh_goods;
(3)创建视图view2从sh_goods表中查询出id,name。权限控制为test用户,且默认安全控制由定义视图的用户权限来执行
create DEFINER='test' SQL SECURITY DEFINER VIEW view2 AS
select id ,name from shop.sh_goods;
(4)创建视图view3从sh_goods表中查询出id,name。,权限控制使用默认值,且默认安全控制由调用视图的用户权限来执行
create SQL SECURITY INVOKER VIEW view3 AS
select id,name from shop.sh_goods;
(5)为用户test赋予对视图view1,view2,view3的select权限
grant select on view1 to 'test';
grant select on view2 to 'test';
grant select on view3 to 'test';
(6)应用test用户查询视图view1,view2,view3
select * from mysql.view1;
①视图view1的DEFINER是root用户,root具有对sh_goods表的select权限,查询成功;
②视图view2的DEFINER是test用户,test用户不具有对sh_goods表的select权限,查询失败;
③视图view3的SQL SECURITY INVOKER 设置了由调用视图的用户test来执行,而test用户不具有对sh_goods表的select权限,查询失败。
(5)为用户test赋予对表sh_goods查询的权限
grant select on shop.sh_goods to 'test';
(6)再一次应用test用户查询视图view1,view2,view3
select * from mysql.view1;
select * from mysql.view2;
select * from mysql.view3;
(7)修改view2权限控制为root用户
grant all privileges on view2.* to 'root'@'localhost';
(8)通过view1向基本表添加数据 id=15,name=HB铅笔
insert into view1 VALUES (15, 'HB铅笔');
当向视图view1中插入数据时,同时也会向原表shop.sh_goods插入数据插入数据 ,而原表shop.sh_goods中存在多个字段不允许为空,所以无法插入。
(9)通过view2向基本表更新数据 id=15为id=13
update view2 set id=15 where id=13;
更新失败
查询所有视图的库名、表名、定义者,view2的定义者是test
select TABLE_SCHEMA,TABLE_NAME,DEFINER from information_schema.VIEWS;
test没有更新view2数据的权限,向view2插入数据失败
(10)通过view2向基本表删除数据 id=1
delete from view2 where id =1;
删除失败,因为test不具有删除view2的权限
(11)删除视图view2
drop view view2;
(12)查看sh_goods表数据
select * from shop.sh_goods;
(13)创建从sh_goods表中查询出id<30的id,name的视图view4
create VIEW view4 AS
select id,name from shop.sh_goods where id<30;
(14)应用级联检查创建从view4表中查询出id<30的id,name的视图view5
create view view5 as
select id,name from view4
where id <30 with cascaded check option;
(15)应用检查本视图规则创建从视图view4表中查询出id>20的id,name的视图view6
create view view6 as
select id,name from view4
where id >20 with local check option;
(16)向view5分别添加数据 id=20,id=25, id=30
insert into view5
values ('20', ' '),('25',' '),('30',' ');添加失败,view5应用了级联检查,受到where id<30的限制,不能有id>=30的数据
(17)向view6分别添加数据 id=20,id=25, id=30
insert into view6
values ('20', ' '),('25',' '),('30',' ');view6只检查本视图规则,只受到where id>20的限制,不能有id<=20的数据
(18)授权给test用户将自己权限赋予他人的权限
grant grant option on *.* to test;
(19)收回test的权限
revoke all ,grant option from 'test'@'%';
(20)删除用户test
drop user 'test'@'%';