視圖和用戶權限

1.視圖（view）

• 看一個需求

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

  答案是使用視圖，

1.1基本原理

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

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

   

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
  
  

  

2.MySQL管理

2.1Mysql用戶管理

• MySQL用戶

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

其中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后，根據相應的權限，可以操作的資料庫和資料物件（表，視圖，觸發器）都不一樣

• 用戶修改密碼

-- 修改自己的密碼
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中的權限

• 給用戶授權

  # 基本語法：
  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.%'

標籤：MySQL

上一篇：StoneDB讀寫分離實踐方案

下一篇：[20221008]sql profile優化失效問題.txt