day09-2視圖和用戶許可權

視圖和用戶許可權

1.視圖（view）

  • 看一個需求

    emp表的列資訊很多，有些資訊是個人重要資訊（比如：sal、comm、mgr、hiredate），如果我們希望某個用戶只能查詢emp表的empno、enamel、job和deptno資訊，有什麼辦法呢？

    答案是使用視圖。

image-20221009190711048

1.1基本原理

  1. 視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含列，其數據來自對應的真實的表（基表）

    視圖和基表關係的示意圖：

    image-20221009193325802

  2. 對視圖的總結

    • 視圖是根據基表（可以是多個基表）來創建的，視圖是虛擬的表
    • 視圖也有列，數據來自基表
    • 通過視圖可以修改基表的數據
    • 基表的改變也會影響到視圖的數據

1.2視圖使用細節

  • 語法
1. create view 視圖名 as select 語句 -- 創建一個視圖
2. alter view 視圖名 as select 語句 -- 修改視圖，更新成新的視圖
3. show create view 視圖名 -- 顯示創建視圖的指令
4. drop view 視圖名1[,視圖名2,...] -- 刪除視圖

例子

完成前面提出的需求

創建一個視圖emp_view01，只能查詢emp表的empno、ename、job和deptno列的資訊

SELECT * FROM emp;
-- 創建一個視圖emp_view01，只能查詢emp表的empno、ename、job和deptno列的資訊

-- 創建視圖
CREATE VIEW emp_view01
	AS 
	SELECT empno,ename,job,deptno FROM emp;
	
-- 查看視圖
DESC emp_view01;
SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;

-- 顯示創建視圖的指令
SHOW CREATE VIEW emp_view01; 

-- 刪除視圖
DROP VIEW emp_view01;
  • 視圖細節討論

  1. 創建視圖後，到資料庫去看，對應的視圖只有一個視圖結構文件（形式：視圖名.frm）

    視圖的數據只是來源於基表，本身是沒有數據的（一個映射關係）

  2. 視圖的數據變化會影響到基表，基表的數據變化也會影響到視圖 [insert，delete，update]

  3. 視圖中可以再使用視圖，數據仍然來自基表

-- 視圖細節討論
-- 1. 創建視圖後，到資料庫去看，對應的視圖只有一個視圖結構文件
-- （形式：視圖名.frm）

-- 2. 視圖的數據變化會影響到基表 [insert，delete，update]
UPDATE emp_view01 SET job = 'MANAGER' WHERE empno=7369;-- 修改視圖
SELECT * FROM emp; -- 查詢基表，，發現數據變了

-- 基表的數據變化也會影響到視圖
UPDATE emp SET job = 'SALESMAN' WHERE empno=7369;-- 修改基表
SELECT * FROM emp_view01; -- 查詢視圖，發現數據變了

-- 3. 視圖中可以再使用視圖，比如從emp_view01視圖中選出empno和ename做出新的視圖
CREATE VIEW emp_view02
	AS 
	SELECT empno,ename FROM emp_view01;

1.3視圖應用實例

  • 視圖最佳實踐

  1. 安全：

    一些數據表有著重要的資訊。有些欄位是保密的，不能讓用戶直接看到。這時就可以創建一個視圖，在這張視圖中只保留一部分欄位。這樣，用戶就可以查詢自己需要的欄位，不能查看保密的欄位。

  2. 性能：

    關係資料庫的數據常常會分表存儲，使用外鍵建立這些表之間的關係。這時，資料庫查詢經常會用到連接（JOIN）。這樣做不但麻煩，效率相對也比較低。如果建立一個視圖（基於多個基表），將相關的表和欄位組合在一起，就可以避免使用JOIN查詢數據。

  3. 靈活：

    如果系統中有一張舊的表，這張表由於設計的問題即將被廢棄。然而，很多應用都是基於這張表，不易修改。這時就可以建立一張視圖，視圖中的數據直接映射到新的表。這樣就可以少做很多改動，也達到了升級數據表的目的。

  • 練習

    針對emp，dept，salgrade三張表。創建一個視圖emp_view03，可以顯示僱員編號，僱員名，僱員部門名稱和薪水級別。

    -- 針對emp，dept，salgrade三張表。
-- 創建一個視圖emp_view03，可以顯示僱員編號，僱員名，僱員部門名稱和薪水級別。
/*
	分析：
	1.使用三張表聯合查詢，得到結果
	笛卡爾乘積 = 表1記錄數*表2記錄數*表3記錄數
	利用過濾條件 emp.deptno= dept.deptno AND sal>=losal AND sal<=hisal
	2.將結果構建成視圖
*/
CREATE VIEW emp_view03
	AS
	SELECT empno,ename,dname,grade
		FROM emp,dept,salgrade
		WHERE emp.deptno= dept.deptno AND
		sal>=losal AND sal<=hisal
	
DESC emp_view03
SELECT * FROM emp_view03
    image-20221009214914097

2.MySQL管理

2.1Mysql用戶管理

  • MySQL用戶

mysql中的用戶，都存儲在系統資料庫mysql中的user表中

image-20221010154107761 image-20221010154209757

其中user表的重要欄位說明：

  1. host：允許登錄的「位置「，localhost表示該用戶只允許本機登錄，也可以指定ip地址，如：192.168.1.100
  2. user：用戶名
  3. authentication_string：密碼，是通過mysql的password()函數加密之後的密碼。
  • 創建用戶
create user '用戶名'@'允許登錄的位置' identified by '密碼' -- 創建用戶，同時指定密碼
  • 刪除用戶
drop user '用戶名'@'允許登錄的位置';

例子

-- mysql用戶管理
-- 原因：當我們做項目開發時，可以根據不同的開發人員，賦給他們相應的操作許可權
-- 所以，mysql資料庫管理人員（root），根據需要創建不同的用戶，賦給相應的許可權，供人員使用

-- 1.創建新用戶
-- 1.1 'liyuelian'@'localhost' 表示用戶的完整資訊
--     'liyuelian' 用戶名  'localhost' 登錄ip

-- 1.2 123456 為密碼，在mysql表存放的是password函數加密過後的密碼
--  *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER 'liyuelian'@'localhost' IDENTIFIED BY '123456';

SELECT `host`,`user`,authentication_string FROM mysql.user;

-- 2.刪除用戶
DROP USER 'liyuelian'@'localhost';

-- 3.登錄

不同的資料庫用戶，登錄到DBMS後，根據相應的許可權，可以操作的資料庫和數據對象（表，視圖，觸發器）都不一樣

image-20221010161513484
  • 用戶修改密碼
-- 修改自己的密碼
set password = password('密碼');
-- 修改他人的密碼（需要有修改用戶密碼許可權）
set password for'用戶名'@'登錄的位置' = password('密碼');

例子

-- 修改本用戶密碼
SET PASSWORD= PASSWORD('abcdef');

-- 修改他人的密碼（需要有修改用戶密碼許可權）
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');-- 錯誤，提示許可權不夠

-- 切換到root用戶，修改一般用戶的密碼
SET PASSWORD FOR 'olien'@'localhost' = PASSWORD('123456');-- 成功，root用戶許可權很高

2.2Mysql許可權管理

  • Mysql中的許可權
image-20221010164159592

  • 給用戶授權

    # 基本語法：
grant 許可權列表 on 庫.對象名 to '用戶名'@'登錄位置' [identified by '密碼']

#說明：
# 1. 許可權列表，多個許可權用逗號分開
#   如：grant select on....
#      grant select ,delete,create on....

#2. 特別說明
#  *.*   ：表示本系統中的所有資料庫的所有對象（表，視圖，存儲過程）
#   庫.*  ： 表示某個資料庫中的所有數據對象（表，視圖，存儲過程）

# 3. identified by可以省略，也可以寫出
# (1)如果用戶存在，就是修改該用戶的密碼
# (2)如果該用戶不存在，就是創建該用戶

  • 回收用戶許可權

    #基本語法
revoke 許可權列表 on 庫.對象名 from '用戶名'@'登錄位置';

  • 許可權生效指令

    #如果許可權沒有生效，可以執行下面命令
#基本語法
flush privileges;

練習

  1. 創建一個用戶（用戶名隨意），密碼為123，並且只可以從本地登錄，不讓遠程登錄mysql
  2. 創建testdb庫和表news，要求：使用root用戶創建
  3. 給用戶分配查看news表和添加數據的許可權
  4. 測試看看用戶是否只有這幾個許可權
  5. 修改密碼為abc，要求使用root用戶完成
  6. 重新登錄
  7. 回收新用戶的許可權
  8. 使用root用戶刪除你的用戶

root用戶：

-- 演示用戶許可權的管理
-- 1. 創建一個用戶（用戶名隨意），密碼為123，並且只可以從本地登錄，不讓遠程登錄mysql
CREATE USER 'olien'@'localhost' IDENTIFIED BY '123'; 

-- 2. 創建testdb庫和表news，要求：使用root用戶創建
-- 使用root用戶創建庫和表
CREATE DATABASE testdb;
CREATE TABLE news(
	id INT ,
	content VARCHAR(32)
)
-- 添加一條測試數據
INSERT INTO news VALUES(100,'北京新聞');

-- 3. 給用戶分配查看news表和添加數據的許可權
-- 4. 測試看看用戶是否只有這幾個許可權
GRANT SELECT,INSERT
	ON testdb.news
	TO 'olien'@'localhost';

-- 5. 修改olien用戶的密碼為abc，要求使用root用戶完成
SET PASSWORD FOR'olien'@'localhost' = PASSWORD('abc');


-- 6. 重新登錄

-- 7.回收新用戶的許可權
REVOKE SELECT,INSERT ON testdb.news FROM 'olien'@'localhost';

-- 8. 使用root用戶刪除你的用戶
DROP USER 'olien'@'localhost';

新創建的用戶：

-- 在默認情況下，olien用戶只能看到一個默認的系統資料庫

SELECT * FROM news;

INSERT INTO news VALUES(200,'上海新聞');

-- 測試看看用戶是否只有這幾個許可權
-- 不能進行沒有授權的操作，如：update
UPDATE news SET content ='廣州新聞'
	WHERE id = 100; -- 錯誤，提示沒有許可權

2.3Mysql管理細節

細節說明：

  1. 在創建用戶的時候，如果不指定host，則默認為%，%表示所有的IP都有連接許可權 。如create user xxx;
  2. 也可以這樣指定：create user 'xxx'@'192.168.1.%' ，表示xxx用戶在192.168.1.*的ip地址都可以登錄mysql
  3. 在刪除用戶時，如果host不是%，需要明確指定'用戶'@'host值'

例子

-- 細節說明：
-- 1. 在創建用戶的時候，如果不指定host，則默認為%，%表示所有的IP都有連接許可權.如create user xxx;
CREATE USER 'jack';

SELECT `host`,`user` FROM mysql.user;

-- 2. 也可以這樣指定：create user 'xxx'@'192.168.1.%' 
-- 表示xxx用戶在192.168.1.*的ip地址都可以登錄mysql
CREATE USER 'smith'@'192.168.1.%' 

-- 3. 在刪除用戶時，如果host不是%，需要明確指定'用戶'@'host值'
DROP USER jack; -- 默認就是 drop user 'jack'@'%'
DROP USER smith@'192.168.1.%'
Tags:

VirMach 便宜 VPS