本文已参与「新人创作礼」活动，一起开启掘金创作之路。

【MySQL】视图及安全性

一、目的与要求：

1、掌握参照完整性约束

2、设计用户子模式

3、根据实际需要创建不同的用户授以不同的权限

4、针对不同级别的用户定义不同的视图，以保证系统的安全性

二、操作内容：

1.为fruitshop数据库的各表构造合适的外键，并设置外键级联删除或更新操作，比如表fruits的s_id字段是外键，取值来源于是suppliers表的s_id字段。

源码：

--1）表fruits的s_id字段是外键，取值来源于是suppliers表的s_id字段

ALTER TABLE fruits
ADD CONSTRAINT FOREIGN KEY(s_id) REFERENCES suppliers(s_id)
ON DELETE CASCADE ON UPDATE CASCADE;

--2）表orderitems的f_id字段是外键，取值来源于是fruits表的f_id字段

ALTER TABLE orderitems
ADD CONSTRAINT FOREIGN KEY(f_id) REFERENCES fruits(f_id)
ON DELETE CASCADE ON UPDATE CASCADE;

--3）表orders的c_id字段是外键，取值来源于是customers表的c_id字段

ALTER TABLE orders
ADD CONSTRAINT FOREIGN KEY(c_id) REFERENCES customers(c_id)
ON DELETE CASCADE ON UPDATE CASCADE;

--4）表orderitems的o_num字段是外键，取值来源于是orders表的o_ num字段
ALTER TABLE orderitems
ADD CONSTRAINT FOREIGN KEY(o_num) REFERENCES orders(o_num)
ON DELETE CASCADE ON UPDATE CASCADE;

2.创建四类用户，每类客户创建一个用户，统一设置初始密码为’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'

3.为以上四类用户设计合适的权限，并定义对应的视图（附上源码，创建用户并授权成功后在交互式运行状态下查看每个用户权限并截图）要求：客户和供货商只能查看与之相关的信息，如C10000用户只可以查看customs表内关于自己的所有信息，可以修改（update）c_id和C_name以外的关于自己的基本信息，可以查看自己的购买信息（在订单order及详单表orderitems中）；同理设置供货商S100的相关权限；商家销售工作人员B001可以查看并录入信息，但不能随意修改、删除信息；管理员拥有所有权限。

-- 客户：
CREATE VIEW CS1 AS SELECT*
FROM   orders
WHERE  c_id=10000
CREATE VIEW CS2 AS SELECT*
FROM   customers
WHERE  c_id=10000

GRANT SELECT,UPDATE(c_address,c_city,c_zip,c_contact,c_email) ON CS2 
TO 'C10000'@'%';
GRANT SELECT ON CS1
TO 'C10000'@'%';
GRANT SELECT ON orderitems
TO'C10000'@'%';

在这里插入图片描述

-- 供货商:
CREATE VIEW CS3 AS SELECT*
FROM   suppliers
WHERE  s_id=100

GRANT SELECT,UPDATE(s_city,s_zip,s_call) ON CS3 
TO 'S100'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

在这里插入图片描述

商家销售工作人员：
GRANT SELECT,INSERT ON fruitshop.* 
TO 'B001'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

在这里插入图片描述

--管理员：
GRANT ALL ON fruitshop.* 
TO 'A001'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;

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