数据库3——视图及安全性

学习内容

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

二、实验内容：
1、 先创建四类用户角色：
管理员角色Admin、客户角色Cusm、供货商角色Supp、商家销售工作人员角色Salor。
源码：

create role Admin;
create role Cusm;
create role Supp;
create role Salor;

2、 创建客户视图、供货商视图
（1）客户视图要求显示所有客户号及对应客户下的所有订单及每个订单的价格
源码：

create view customers_view as
select customers.c_id as 客户号,orderitems.o_num as 订单号,sum(orderitems.quantity*orderitems.item_price) as 订单价格
from customers
join orders on customers.c_id=orders.c_id
join orderitems on orders.o_num=orderitems.o_num
group by customers.c_id,orderitems.o_num;select * from customers_view;

效果：

（2）供货商视图要求显示所有供货商号及供货商所供水果信息
源码：

create view suppliers_view as 
select suppliers.s_id as 供应商编号,suppliers.s_name as 供应商名,fruits.f_id as 水果编号,fruits.f_name as 水果名称,fruits.f_price as 水果单价
from suppliers
join fruits on suppliers.s_id=fruits.s_id;select * from suppliers_view;

效果：

3.为以上四类用户角色授予合适的权限，要求：
（1）管理员拥有所有权限:
源码：

grant all on fruitshop.* to Admin;

（2）客户只能查看与之相关的信息（基本信息，购买信息）
源码：

create view view1 as
select customers.*,orders.o_num,orderitems.o_item,orderitems.quantity,orderitems.item_price
from customers
join orders on customers.c_id=orders.c_id
join orderitems on orders.o_num=orderitems.o_num
where customers.c_id=CAST(USER() AS UNSIGNED int);grant select on view1 to Cusm;

（3）供货商只能查看与之相关的信息(可以查看自己供货的水果信息)。
源码：

create view view2 as
select suppliers.*,fruits.f_id,fruits.f_name,fruits.f_price
from suppliers
join fruits on suppliers.s_id=fruits.s_id
where suppliers.s_id=CAST(USER() AS UNSIGNED int);grant select on view2 to Supp;

（4）商家销售工作人员可以查看数据库并录入信息，但不能修改、删除；
源码：

grant select, insert on fruitshop.* to Salor;

4． 为每类用户角色至少创建一个用户（新建用户并分配对应的角色，统一设置初始密码为’123456’）
注意：附上源码，创建用户并授权成功后在交互式运行状态下查看每个用户权限并截图
客户用户：C001，任意主机地址，授权为客户角色的所有权限；
供货商用户：S001，任意主机地址，授权为供货商角色的所有权限；；
商家销售工作人员用户：B001，任意主机地址，授权为销售工作人员角色的所有权限；；
管理员用户：A001，本机，拥有所有权限
源码：
1.创建客户

create user 'C001'@'%' identified by '123456';
create user 'S001'@'%' identified by '123456';
create user 'B001'@'%' identified by '123456';
create user 'A001'@'localhost' identified by '123456';

2.给客户授权
2.1给管理员用户‘A001’授权

grant Admin to 'A001'@'localhost';

2.2给客户‘C001’授权

grant Cusm to 'C001'@'%';

2.3给供应商’S001’授权

grant Supp to 'S001'@'%';

2.4给商家销售工作人员用户’B001’授权

grant Salor to 'B001'@'%';

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

测试客户用户：

mysql -uC001 -p123456

登录客户用户‘C001’时，因为在customers表格中没有对应的c_id，因此查询的视图为空。结果如下图所示：

我们可以新建一个客户用户‘10001’

create user '10001'@'%' identified by '123456';
grant Cusm to '10001'@'%';

登录用户‘10001’

mysql -u10001 -p123456

对应customers表格中的c_id为10001时的客户信息，查询视图的结果如下图所示，即输出10001的基本信息和购买信息

可以查询：

select * from view1;

不能插入：

insert view1 values (10001,'RedHook','200 Street',' Tianjin','300000','LiMing','LMing@163.com',NULL,NULL,NULL,NULL);

不能修改：

update view1
set c_name='Red'
where c_id=10001;

不能删除：

delete from view1
where c_id=10001;

测试供货商用户:

mysql -uS001 -p123456

登录供货商用户‘S001’时，在Suppliers表格中没有对应的s_id，因此查询的结果为空，如下图所示：

我们可以新创建供货商用户‘101’

create user '101'@'%' identified by '123456';
grant Supp to '101'@'%';

登录供货商用户‘101’

mysql -u101 -p123456

对应Suppliers表格中的s_id=101时的供货商信息，查询的结果如下图所示：

只能进行查询操作，不能进行插入、删除等其他的操作。
可以查询：

select * from view2;

不能插入（会报错）：

insert view2 values(101,'FastFruit Inc','Tianjin',300000,48075,t4,banana,3,5);

不能删除（会报错）：

drop view view2;

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

mysql -uB001 -p123456

登录‘B001’商家销售工作人员用户，可以查看数据库并录入信息，但不能进行修改、删除操作，如下图所示：

可以查询信息：

select * from customers;

可以插入信息：

insert customers values(10005,'Bo','福建泉州','靖江','000000','LL',NULL);

不能修改：

update customers
set c_name='Ao',
c_zip=111111
where c_id=10005;

不能删除：

drop table customers;

测试管理员用户：

mysql -uA001 -p123456

登录管理员用户，可以拥有对fruitshop数据库的所有权限，可以进行增、删、改、查操作。

可以进行查看：

select * from customers;

可以增加数据：

insert customers values(10006,'Co','福建福州','福清','111111','LL',NULL);

可以删除数据：

delete from customers
where c_id=10006;

可以更改数据：

update customers
set c_name='BBo',
c_zip='111111'
where c_id=10005;

三、实验小结
1．实验中遇到的问题及解决过程
在给客户和供货商分配查询权限时，我们把“客户视图”和“供货商视图”的查看权限分别给了客户角色和供货商角色。这样他们就可以查询了，但有个问题：他们能查到所有客户或所有供货商的信息，这就不太合适了。实际上，我们希望的是：客户只能看到自己的信息，供货商也只能看到自己的信息，而不是大家的信息都能查。
为了解决这个问题，我们做了一个处理：让系统自动根据当前登录用户的用户名，去匹配他自己的客户编号或供货商编号。一个用户名是“10001”的客户登录后，系统就只给他显示客户编号是“10001”的那一行数据。
解决过程：通过当前客户或供货商的用户名与查询的信息对应，例如在给供货商角色授权时，使suppliers.s_id=CAST(USER() AS UNSIGNED INT)。

2．实验中产生的错误及原因分析
在测试阶段登录用户后没有设置当前角色set role,无法测试

原因分析：没有激活当前角色，需要set role。

学习感受

通过本次实验，我深入了解了数据库中视图、角色、用户管理以及权限控制等安全机制。在实际设计中，不同角色的用户需要访问不同的数据内容，因此应根据角色类型创建相应的视图，从而实现数据访问的隔离和系统的安全保障。同时，在为角色和用户分配权限时，也应根据其职责合理控制操作范围，如管理员拥有全面权限，而客户和供货商仅能查询与自身相关的信息。此外，用户登录过程中的身份验证（如密码校验）也是保障系统安全的重要环节。
本次实验不仅加深了我对数据库安全性控制的理解，还提高了我在实际操作中规范管理权限、防止数据泄露和误操作的意识，对后续数据库的设计与维护具有重要的参考价值。