MySQL中的視圖特性和用戶(hù)權(quán)限管理詳解

更新時(shí)間：2026年05月21日 09:54:48 作者：思麟呀

文章詳細(xì)介紹了MySQL視圖和用戶(hù)管理與權(quán)限控制,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧

1：MySQL視圖

1：什么是視圖

視圖是一個(gè)虛擬表，其內(nèi)容由一條 SELECT 查詢(xún)語(yǔ)句定義。它本身不存儲(chǔ)數(shù)據(jù)，所有數(shù)據(jù)都來(lái)自于底層的 "基表"。

核心特性：數(shù)據(jù)雙向聯(lián)動(dòng)

修改視圖中的可更新數(shù)據(jù)，會(huì)直接影響基表
基表的數(shù)據(jù)發(fā)生變化，視圖的查詢(xún)結(jié)果也會(huì)實(shí)時(shí)更新

主要作用：

簡(jiǎn)化復(fù)雜的多表查詢(xún)，將常用查詢(xún)封裝為視圖
隱藏基表的敏感字段（如密碼、薪資），提升數(shù)據(jù)安全性
實(shí)現(xiàn)數(shù)據(jù)隔離，不同用戶(hù)只能看到自己權(quán)限內(nèi)的數(shù)據(jù)

2：視圖基本操作

1：創(chuàng)建員工表

-- 創(chuàng)建部門(mén)表
CREATE TABLE DEPT (
    deptno INT PRIMARY KEY,
    dname VARCHAR(20),
    loc VARCHAR(20)
);
-- 創(chuàng)建員工表
CREATE TABLE EMP (
    empno INT PRIMARY KEY,
    ename VARCHAR(20),
    job VARCHAR(20),
    mgr INT,
    hiredate DATE,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT,
    FOREIGN KEY (deptno) REFERENCES DEPT(deptno)
);
-- 插入測(cè)試數(shù)據(jù)
INSERT INTO DEPT VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

2：創(chuàng)建視圖并查詢(xún)

-- 創(chuàng)建視圖
CREATE VIEW v_ename_dname AS
SELECT e.ename, d.dname
FROM EMP e
JOIN DEPT d ON e.deptno = d.deptno;
-- 查詢(xún)視圖（和查詢(xún)普通表語(yǔ)法完全一致）
SELECT * FROM v_ename_dname ORDER BY dname;

視圖本質(zhì)是保存了一條 SELECT 語(yǔ)句，每次查詢(xún)視圖都會(huì)執(zhí)行這條語(yǔ)句并返回結(jié)果。

3：修改視圖數(shù)據(jù)，驗(yàn)證對(duì)基表的影響

-- 先查看基表中CLARK的信息
SELECT ename FROM EMP WHERE ename = 'CLARK'; -- 結(jié)果：CLARK
-- 修改視圖中的數(shù)據(jù)
UPDATE v_ename_dname SET ename = 'TEST' WHERE ename = 'CLARK';
-- 再次查看基表
SELECT ename FROM EMP WHERE ename = 'CLARK'; -- 無(wú)結(jié)果
SELECT ename FROM EMP WHERE ename = 'TEST';  -- 結(jié)果：TEST

可更新視圖的修改會(huì)直接作用于底層基表。

4：修改基表數(shù)據(jù)，驗(yàn)證對(duì)視圖的影響

-- 修改基表中JAMES的部門(mén)編號(hào)
UPDATE EMP SET deptno = 10 WHERE ename = 'JAMES';
-- 查詢(xún)視圖中JAMES的部門(mén)
SELECT * FROM v_ename_dname WHERE ename = 'JAMES';

基表數(shù)據(jù)變化會(huì)實(shí)時(shí)反映在視圖中，因?yàn)橐晥D是動(dòng)態(tài)查詢(xún)的。

5：刪除視圖

-- 刪除視圖
DROP VIEW v_ename_dname;
-- 驗(yàn)證刪除
SHOW TABLES; -- 視圖不再出現(xiàn)在列表中

3：視圖的規(guī)則與限制

命名唯一：視圖名不能和已有表或視圖重名
性能影響：基于復(fù)雜查詢(xún)創(chuàng)建的視圖，每次查詢(xún)都會(huì)執(zhí)行復(fù)雜 SQL，注意性能優(yōu)化
無(wú)索引 / 觸發(fā)器：視圖不能創(chuàng)建索引，也不能關(guān)聯(lián)觸發(fā)器或設(shè)置默認(rèn)值
權(quán)限控制：可以給不同用戶(hù)分配不同視圖的權(quán)限，實(shí)現(xiàn)數(shù)據(jù)隔離
ORDER BY 覆蓋：如果查詢(xún)視圖時(shí)也指定了 ORDER BY，視圖定義中的 ORDER BY 會(huì)被覆蓋
可更新限制：包含以下內(nèi)容的視圖不可更新：
- 聚合函數(shù)（SUM、COUNT、AVG 等）
- GROUP BY、HAVING、UNION、DISTINCT
- 子查詢(xún)、多表 JOIN（部分簡(jiǎn)單 JOIN 可更新）

2：MySQL用戶(hù)管理與權(quán)限控制

1：為什么需要用戶(hù)管理

root 用戶(hù)擁有所有權(quán)限，日常使用存在極大安全風(fēng)險(xiǎn)
多用戶(hù)協(xié)作場(chǎng)景下，需要給不同角色分配不同權(quán)限（如開(kāi)發(fā)只能查測(cè)試庫(kù)，運(yùn)維有生產(chǎn)庫(kù)權(quán)限）
遵循最小權(quán)限原則：只給用戶(hù)完成工作所需的最小權(quán)限

2：MySQL用戶(hù)信息存儲(chǔ)

MySQL 的所有用戶(hù)信息都存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù) mysql的user 表中，核心字段如下：

字段	含義
`host`	允許該用戶(hù)登錄的主機(jī)地址- `localhost`：只能從本機(jī)登錄- `%`：任意主機(jī)- `192.168.1.%`：指定網(wǎng)段
`user`	用戶(hù)名
`authentication_string`	經(jīng)過(guò) password 函數(shù)加密后的用戶(hù)密碼
`*_priv`	各種權(quán)限字段（如 Select_priv、Insert_priv 等）

查看所有用戶(hù)：

USE mysql;
SELECT host, user, authentication_string FROM user;

3：用戶(hù)基本操作

所有實(shí)驗(yàn)均使用 root 用戶(hù)登錄 MySQL 執(zhí)行

1：創(chuàng)建新用戶(hù)

-- 創(chuàng)建只能從本機(jī)登錄的用戶(hù)dev，密碼為Dev@123456
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'Dev@123456';
-- 驗(yàn)證用戶(hù)創(chuàng)建成功
SELECT host, user FROM mysql.user WHERE user = 'dev';

常見(jiàn)錯(cuò)誤

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因：MySQL 默認(rèn)開(kāi)啟密碼復(fù)雜度驗(yàn)證，要求密碼長(zhǎng)度≥8，包含大小寫(xiě)字母、數(shù)字和特殊字符。解決：使用符合要求的強(qiáng)密碼（生產(chǎn)環(huán)境禁止關(guān)閉密碼驗(yàn)證）。

2：刪除用戶(hù)

-- 錯(cuò)誤寫(xiě)法（默認(rèn)匹配%主機(jī)，會(huì)報(bào)錯(cuò)）
DROP USER dev; -- ERROR 1396 (HY000): Operation DROP USER failed for 'dev'@'%'
-- 正確寫(xiě)法（必須完整指定'用戶(hù)名'@'主機(jī)'）
DROP USER 'dev'@'localhost';

MySQL 用戶(hù)是'用戶(hù)名'@'主機(jī)'的組合，兩者共同唯一標(biāo)識(shí)一個(gè)用戶(hù)。

3：修改用戶(hù)密碼

-- 1. 用戶(hù)自己修改自己的密碼
SET PASSWORD = PASSWORD('NewDev@123456');
-- 2. root用戶(hù)修改指定用戶(hù)的密碼
SET PASSWORD FOR 'dev'@'localhost' = PASSWORD('RootSet@123456');

退出 MySQL，使用新密碼登錄 dev 用戶(hù)。

4：權(quán)限管理

1：給用戶(hù)授權(quán)

-- 1. 授予dev用戶(hù)test庫(kù)所有表的查詢(xún)權(quán)限
GRANT SELECT ON test.* TO 'dev'@'localhost';
-- 2. 授予dev用戶(hù)test庫(kù)所有表的增刪改查權(quán)限
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'dev'@'localhost';
-- 3. 授予所有庫(kù)所有權(quán)限（生產(chǎn)環(huán)境絕對(duì)禁止?。?
-- GRANT ALL PRIVILEGES ON *.* TO 'dev'@'localhost';
-- 查看用戶(hù)權(quán)限
SHOW GRANTS FOR 'dev'@'localhost';
-- 權(quán)限生效（如果授權(quán)后不生效，執(zhí)行此命令）
FLUSH PRIVILEGES;

登錄

-- 查看數(shù)據(jù)庫(kù)（應(yīng)該能看到test庫(kù)）
SHOW DATABASES;
-- 查詢(xún)test庫(kù)的表
USE test;
SHOW TABLES;
-- 執(zhí)行查詢(xún)（成功）
SELECT * FROM account;
-- 執(zhí)行刪除（如果只給了SELECT權(quán)限，會(huì)報(bào)錯(cuò)）
DELETE FROM account; -- ERROR 1142 (42000): DELETE command denied to user 'dev'@'localhost' for table 'account'

2：回收用戶(hù)權(quán)限

-- 回收dev用戶(hù)對(duì)test庫(kù)的所有權(quán)限
REVOKE ALL ON test.* FROM 'dev'@'localhost';
-- 驗(yàn)證：dev用戶(hù)執(zhí)行show databases，只能看到information_schema庫(kù)
-- 查看權(quán)限
SHOW GRANTS FOR 'dev'@'localhost'; -- 顯示 GRANT USAGE ON *.* TO 'dev'@'localhost'（USAGE表示無(wú)任何權(quán)限）

5：常見(jiàn)權(quán)限說(shuō)明

權(quán)限類(lèi)別	權(quán)限名稱(chēng)	說(shuō)明
數(shù)據(jù)操作	SELECT	查詢(xún)數(shù)據(jù)
	INSERT	插入數(shù)據(jù)
	UPDATE	更新數(shù)據(jù)
	DELETE	刪除數(shù)據(jù)
結(jié)構(gòu)操作	CREATE	創(chuàng)建數(shù)據(jù)庫(kù)、表、索引
	DROP	刪除數(shù)據(jù)庫(kù)、表、視圖
	ALTER	修改表結(jié)構(gòu)
視圖相關(guān)	CREATE VIEW	創(chuàng)建視圖
	SHOW VIEW	查看視圖定義
管理權(quán)限	CREATE USER	創(chuàng)建用戶(hù)
	GRANT OPTION	將自己的權(quán)限授予其他用戶(hù)
	SHOW DATABASES	查看所有數(shù)據(jù)庫(kù)

6：安全實(shí)踐

禁止使用 root 用戶(hù)進(jìn)行日常操作，為每個(gè)用戶(hù)創(chuàng)建獨(dú)立賬號(hào)
不要?jiǎng)?chuàng)建'user'@'%'這樣允許任意主機(jī)登錄的用戶(hù)，限制登錄 IP
嚴(yán)格遵循最小權(quán)限原則，只給用戶(hù)完成工作所需的最小權(quán)限
定期修改密碼，使用包含大小寫(xiě)、數(shù)字、特殊字符的強(qiáng)密碼
及時(shí)刪除不再使用的用戶(hù)，避免遺留安全隱患
通過(guò)視圖實(shí)現(xiàn)列級(jí)權(quán)限控制，不要直接給用戶(hù)基表的查詢(xún)權(quán)限

到此這篇關(guān)于MySQL中的視圖特性和用戶(hù)權(quán)限管理詳解的文章就介紹到這了,更多相關(guān)mysql視圖特性和用戶(hù)權(quán)限管理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: