數據庫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。

學習感受

通過本次實驗，我深入了解了數據庫中視圖、角色、用戶管理以及權限控制等安全機制。在實際設計中，不同角色的用戶需要訪問不同的數據內容，因此應根據角色類型創建相應的視圖，從而實現數據訪問的隔離和系統的安全保障。同時，在為角色和用戶分配權限時，也應根據其職責合理控制操作范圍，如管理員擁有全面權限，而客戶和供貨商僅能查詢與自身相關的信息。此外，用戶登錄過程中的身份驗證（如密碼校驗）也是保障系統安全的重要環節。
本次實驗不僅加深了我對數據庫安全性控制的理解，還提高了我在實際操作中規范管理權限、防止數據泄露和誤操作的意識，對后續數據庫的設計與維護具有重要的參考價值。