MySQL 用戶與權限管理全攻略：從底層表到實操命令

MySQL 用戶與權限管理全攻略：從底層表到實操命令

1. 權限系統表結構

核心庫 mysql 存儲權限：

• user：全局賬號與權限（Select_priv、Insert_priv...、SSL、資源限制）；
• db：庫級權限（含存儲過程權限）；
• tables_priv：表級權限；
• columns_priv：列級權限；
• procs_priv：存儲過程/函數權限。

2. 用戶全生命周期操作

-- 查看
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') FROM mysql.user;

-- 創建（推薦 CREATE USER）
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
CREATE USER 'user1'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%';

-- 刪除
DROP USER 'user1'@'localhost';

-- 修改密碼
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('123456');
-- 或
UPDATE mysql.user SET PASSWORD=PASSWORD('123') WHERE User='user1' AND Host='localhost';
FLUSH PRIVILEGES;

-- Root 忘記密碼破解
service mysql stop
mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root mysql
UPDATE mysql.user SET PASSWORD=PASSWORD('123456') WHERE User='root';
FLUSH PRIVILEGES;
service mysql restart

3. 權限分類與授予/回收

-- 授予語法
GRANT priv_type [(columns)] ON db.table TO user [IDENTIFIED BY 'pwd'] [WITH GRANT OPTION | MAX_QUERIES_PER_HOUR 10 ...];

-- 示例：user4 擁有全庫 SELECT/UPDATE 並可再授權
GRANT SELECT,UPDATE ON *.* TO 'user4'@'localhost' IDENTIFIED BY '123' WITH GRANT OPTION;

-- 回收
REVOKE ALL ON *.* FROM 'user4'@'localhost';