数据库——安全性
智能2112杨阳
一、目的与要求：
1、设计用户子模式
2、根据实际需要创建用户角色及用户，并授权
3、针对不同级别的用户定义不同的视图，以保证系统的安全性
二、内容：
- 先创建四类用户角色：
管理员角色Cusm、客户角色Supp、供货商角色Admin、商家销售工作人员角色Salor。
源码：
create role 'Cusm';
create role 'Supp';
create role 'Admin';
create role 'Salor';
- 创建客户视图、供货商视图
（1）客户视图要求显示所有客户号及对应客户下的所有订单及每个订单的价格
源码：
create view view_Supp as select customers.c_id,orders.o_num,orderitems.item_price from customers,orders,orderitems where customers.c_id=orders.c_id and orderitems.o_num=orders.o_num;
效果：
（2）供货商视图要求显示所有供货商号及供货商所供水果信息
源码：
create view view_Admin as select suppliers.s_id,fruits.f_id,fruits.f_name,fruits.f_price from suppliers,fruits where fruits.s_id=suppliers.s_id;
效果：
3.为以上四类用户角色授予合适的权限，要求：
（1）管理员拥有所有权限:
源码：
grant all on fruitshop.* to 'Cusm';
（2）客户只能查看与之相关的信息（基本信息，购买信息）
源码：
grant select on view_Supp to 'Supp';
（3）供货商只能查看与之相关的信息(可以查看自己供货的水果信息)。
源码：
grant select on view_Admin to 'Admin';
（4）商家销售工作人员可以查看数据库并录入信息，但不能修改、删除；
源码：
grant select,insert on fruitshop.* to 'Salor';
- 为每类用户角色至少创建一个用户（新建用户并分配对应的角色，统一设置初始密码为’123456’）
注意：附上源码，创建用户并授权成功后在交互式运行状态下查看每个用户权限并截图
客户用户：C001，任意主机地址，授权为客户角色的所有权限；
供货商用户：S001，任意主机地址，授权为供货商角色的所有权限；；
商家销售工作人员用户：B001，任意主机地址，授权为销售工作人员角色的所有权限；；
管理员用户：A001，本机，拥有所有权限
源码：
create user 'C001'@'%' identified by '123456';
grant Supp to 'C001';
create user 'S001'@'%' identified by '123456';
grant Admin to 'S001';
create user 'B001'@'%' identified by '123456';
grant Salor to 'B001';
create user 'A001'@'localhost' identified by '123456';
grant all on fruitshop.* to 'A001'@'localhost'
5．测试：
退出已用root账号登陆的MySQL服务器，用这四类用户帐号分别登陆MySQL服务器并测试其功能（在交互式运行状态下测试，先附上测试源码，关键结果截图）。
测试要求：退出root帐号,重新登陆指定帐号，显示当前数据库中所有表，查看权限内某张表内的所有信息，对权限内某张表进行增、删、改、查操作并查看能否正确执行这些操作，如果执行错误请说明导致此错误的原因。
注意：每次登录后先设置当前角色，否则无效果：set role 角色名；
测试客户用户：
mysql -uC001 -p123456;
set role Supp;
use fruitshop;
show tables;
slect* from `view_supp`;
update view_supp set c_address="第五社区",c_city="厦门",c_zip=111000,c_contact="cxr",c_email="123@678" where c_id=10000;
insert into view_supp values(01,"AAA","BBB","CCC",111,"123@321");
delete from view_supp.c_zip;
显示表：
Select操作：
Update操作：
原因：无权限
Insert操作：
原因：无权限
Delete操作：
原因：无权限
测试供货商用户:
mysql -uS001 -p123456
se role Admin;
use fruitshop;
show tables;
select* from `view_Admin`;
update view_Admin set s_city="Beijing",s_zip=111000,s_call=23333 where s_id=100;
insert into view_Admin values(01,"AAA","BBB",111,222);
delete from view_Admin.s_zip;
显示表：
Select操作：
Update操作：
原因：无权限
Insert操作：
原因：无权限
Delete操作：
原因：无权限
测试商家销售工作人员用户：
mysql -uB001 -p123456
set role Salor;
use fruitshop;
show tables;
select* from suppliers;
update suppliers SET s_name="Sariel",s_city="Shanghai",s_zip=520000,s_call=777777 where s_id=107;
insert into suppliers values(108,"Sariel","GQ City",520000,777777);
delete from suppliers.s_name;
显示表：
Select操作：
Update操作：
原因：无权限
Insert操作：
Delete操作：
原因：无权限
测试管理员用户：
mysql -uA001 -p123456
use fruitshop;
show tables;
select* from customers;
update customers set c_name="Evan",c_address="WZ Mall",c_city="Shenzhen",c_zip=521000,c_contact=666666,c_email="LU@666" where c_id=10003;
insert into customers values(10005,"Charlie","NOVATEN INC","GQ City",233333,555555,"love@sweety");
delete from customers where c_id=10000;
显示表：
Select操作：
Update操作：
Insert操作：
Delete操作：
原因：c_id具有外键约束
三、小结
1．遇到的问题及解决过程
a. 用户权限设置问题：通过创建视图，让用户获得部分对视图操作的权限
b. 用户账号切换问题：命令行quit退出，重新输入账号密码进行登录
c. 角色与用户权限问题：学习相关语法，让用户获得角色的权限
- 产生的错误及原因分析
在测试用户的增删改查时，不能完成增删改查功能
原因：未授权，c_id的相关语句具有外键约束
- 体会和收获。
通过本次博客，对数据库进行一些列操作，掌握参照完整性约束、设计用户子模式等功能，并且根据实际需要创建了不同的用户授以不同的权限，以此来针对不同级别的用户定义不同的视图，保证了系统的安全性。同时在这个过程中有遇到很多的问题与错误，但是都在不断的实验与互联网的帮助下解决了出现的问题与错误。感受到即使是表面上看上去很简单的题目，要去实践也会遇到很多不容易解决的问题与错误。总的来说收获满满。