数据库实验三：视图、安全性

一、实验目的

1、设计用户子模式
2、根据实际需要创建用户角色及用户，并授权
3、针对不同级别的用户定义不同的视图，以保证系统的安全性

二、实验内容

1.创建四类用户角色

管理员角色、客户角色、供货商角色、商家销售工作人员角色

#创建用户
 CREATE USER 'C10000'@'%' IDENTIFIED BY '123456';
 CREATE USER 'S100'@'%' IDENTIFIED BY '123456';
 CREATE USER 'B001'@'%' IDENTIFIED BY '123456';
 CREATE USER 'A001'@'localhost' IDENTIFIED BY '123456';

2.为以上四类用户角色操作基本表的权限

要求：
客户只能查看与之相关的信息，如C10000用户（是customers表中的C客户编号）只可以查看customs表内关于自己的所有信息，可以修改（update）c_id和C_name以外的关于自己的基本信息，可以查看自己的购买信息（在订单order及详单表orderitems中）。
供货商只能查看与之相关的信息如S100用户（是suppliers表中的s客户编号）只可以查看suppliers表内关于自己的所有信息，可以修改（update）s_id和s_name以外的关于自己的基本信息，可以查看自己供货的水果信息。
商家销售工作人员B001可以查看并录入订单及详单信息，但不能随意修改、删除；
管理员A001拥有所有权限。

 #客户可操作范围
 CREATE VIEW `view1` AS SELECT* FROM customers WHERE c_id=10000;
 CREATE VIEW `view2` AS SELECT* FROM orders WHERE c_id=10000;
 #客户授权
 GRANT SELECT,UPDATE(c_address,c_city,c_zip,c_contact,c_email)ON `view1` TO 'C10000'@'%';
 GRANT SELECT ON `view2` TO 'C10000'@'%';
 GRANT SELECT ON orderitems TO 'C10000'@'%';

#供应商可操作范围
CREATE VIEW `view3` AS SELECT* FROM suppliers WHERE s_id=100;
CREATE VIEW `view4` AS SELECT* FROM fruits WHERE s_id=100;
#供应商授权
 GRANT SELECT,UPDATE(s_city,s_zip,s_call)ON `view3` TO 'S100'@'%';
 GRANT SELECT ON `view4` TO 'S100'@'%';

#商家销售工作人员授权
GRANT SELECT,INSERT ON fruitshop.* TO 'B001'@'%';

#管理员授权
GRANT ALL ON fruitshop.* TO 'A001'@'localhost';

3.为每类用户角色至少创建一个用户

新建用户并分配对应的角色，统一设置初始密码为’123456’

客户用户：C10000，对应customs表内的10000客户，任意主机地址，视图只能查看当前客户的所有信息；
供货商用户：S100，对应suppliers表内的100供货商，任意主机地址，视图只能查看当前客户的所有信息；
商家销售工作人员用户：B001，任意主机地址，视图可以查看并录入信息，但不能随意修改、删除信息；
管理员用户：A001，本机，拥有所有权限

（↓前面两部分代码汇总一下，已经运行过就不用重复运行了。）

#创建用户
 CREATE USER 'C10000'@'%' IDENTIFIED BY '123456';
 CREATE USER 'S100'@'%' IDENTIFIED BY '123456';
 CREATE USER 'B001'@'%' IDENTIFIED BY '123456';
 CREATE USER 'A001'@'localhost' IDENTIFIED BY '123456';
 #客户可操作范围
 CREATE VIEW `view1` AS SELECT* FROM customers WHERE c_id=10000;
 CREATE VIEW `view2` AS SELECT* FROM orders WHERE c_id=10000;
 #客户授权
 GRANT SELECT,UPDATE(c_address,c_city,c_zip,c_contact,c_email)ON `view1` TO 'C10000'@'%';
 GRANT SELECT ON `view2` TO 'C10000'@'%';
 GRANT SELECT ON orderitems TO 'C10000'@'%';

#供应商可操作范围
CREATE VIEW `view3` AS SELECT* FROM suppliers WHERE s_id=100;
CREATE VIEW `view4` AS SELECT* FROM fruits WHERE s_id=100;
#供应商授权
 GRANT SELECT,UPDATE(s_city,s_zip,s_call)ON `view3` TO 'S100'@'%';
 GRANT SELECT ON `view4` TO 'S100'@'%';

#商家销售工作人员授权
GRANT SELECT,INSERT ON fruitshop.* TO 'B001'@'%';

#管理员授权
GRANT ALL ON fruitshop.* TO 'A001'@'localhost';

①客户用户

②供货商用户

③商家销售工作人员用户

④管理员用户

4.测试

退出已用root账号登陆的MySQL服务器，用这四类用户分别登陆MySQL服务器并测试其功能（在交互式运行状态下测试，先附上测试源码，关键结果截图）。
测试要求：退出root帐号,重新登陆指定帐号，显示当前数据库中所有表，查看权限内某张表内的所有信息，对权限内某张表进行增、删、改、查操作并查看能否正确执行这些操作，如果执行错误请说明导致此错误的原因。

①测试客户用户

#客户检测
mysql -uC10000 -p123456
USE fruitshop;
SHOW TABLES;
SELECT* FROM `view1`;
UPDATE view1 SET c_address="第五社区",c_city="厦门",c_zip=111000,c_contact="cxr",c_email="123@678" WHERE c_id=10000;
INSERT INTO view1 VALUES(01,"AAA","BBB","CCC",111,"123@321");
DELETE FROM view1.c_zip;

②测试供货商用户

#供货商检测
mysql -uS100 -p123456
USE fruitshop;
SHOW TABLES;
SELECT* FROM `view3`;
UPDATE view3 SET s_city="Beijing",s_zip=111000,s_call=23333 WHERE s_id=100;
INSERT INTO view3 VALUES(01,"AAA","BBB",111,222);
DELETE FROM view3.s_zip;

③测试商家销售工作人员用户

#商家销售工作人员检测
mysql -uS100 -p123456
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;

④测试管理员用户

#管理员检测
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;

三、实验小结

1．实验中遇到的问题及解决过程

给角色赋予权限的时候已经先做好了视图，就没有去考虑不设置视图的情况下的操作。
本次实验遇到的问题主要是老是忘记表和视图分属的信息的不同，导致有时候总以为自己没给用户授权成功。后来记清楚之后实验做的还算顺利。

2．实验中产生的错误及原因分析

以A001管理员身份做删除操作的时候，多次报错显示没有权限，检查了授权情况也没有问题，最后意识到是sql语句的表述有问题。
之前一直在Navicat Premium可视化界面中进行操作，这次实验用了命令行反而不太习惯了。

3．实验体会和收获

主要学会了根据实际需要创建了不同的用户授以不同的权限，以此来针对不同级别的用户定义不同的视图，保证了系统的安全性。