前言
為了避免刪庫跑路的事情，權限管理和數據備份是必要。
機器環境
-
MySQL 8.0.21 x86_64 MySQL Community Serve
-
centos 7
Mysql 權限管理
Mysql 8.0 可以創建角色，然后將操作數據庫、表、索引等的權限賦予給角色，將將角色賦予給用戶，也是我們熟悉的 RBAC 模型。
當然也可以將權限直接授予用戶。
用戶
創建用戶
-- 用戶名稱是由 用戶名和登錄用戶的 ip 一同組成的，% 代表任意 ip
CREATE USER 'db_dev'@'localhost' IDENTIFIED BY 'Mysql@12345678';
修改用戶密碼
-- 修改用戶密碼
ALTER USER 'test'@'localhost' IDENTIFIED BY 'password';
鎖定用戶
-- 鎖定用戶不能登錄
ALTER USER 'db_dev1'@'localhost' ACCOUNT LOCK;
-- 解鎖
ALTER USER 'db_dev1'@'localhost' ACCOUNT UNLOCK;
權限
用戶的權限信息保存在 information_schema.USER_PRIVILEGES 。也可以在 mysql.user 看到授權信息。
為了避免已經建立的鏈接的權限無法刷新，需要搭建數據庫的時候，權限就要設計好。
有部分權限是可以動態修改的，但是有的權限，在一個會話中是不能修改的。為了避免問題，需要數據庫使用之前就要做好權限規劃。
權限說明
grant 用法
| 權限 | 說明 |
|---|---|
| ALL | 所有的權限，除了 GRANT OPTION and PROXY. |
| ALTER | 修改表結構，ALTER TABLE |
| CREATE | 創建數據庫和表 |
| DROP | 刪除數據庫、表、視圖 |
| GRANT OPTION | GRANT權限允許你把你自己擁有的那些權限授給其他的用戶。可以用于數據庫、表和保存的程序。 |
| DELETE | 刪除表數據 |
| INDEX | INDEX權限允許你創建或刪除索引。 |
| INSERT | 插入表數據 |
| SELECT | 查詢表數據 |
| UPDATE | 更新表數據 |
| PROCESS | show processlist 命令顯示在服務器內執行的線程的信息（即其它賬戶相關的客戶端執行的語句）。 |
| SHOW VIEW | 查看視圖 |
| SHOW DATABASES | 查看數據庫列表，沒有授予這個權限，只能查看到 information_schema |
| LOCK TABLES | 鎖表 |
| RELOAD | FLUSH 相關的操作 |
| CREATE TABLESPACE | 允許使用操作表空間和日志的語句，比如創建，刪除，修改 |
| CREATE TEMPORARY TABLES | 創建臨時表 |
授權
-- 對從 localhost 登錄的用戶 db_dev 的數據庫：ceshi 中所有表（*） 授予 SHOW DATABASES,SELECT,RELOAD 權限
GRANT SHOW DATABASES,SELECT,RELOAD ON ceshi.* TO 'db_dev'@'localhost';
-- 也可以針對某個表授權，`` 是為了處理關鍵字，當沒有關鍵字可以 ceshi.test1 就可以
GRANT SELECT ON ceshi.`test1` TO 'db_dev'@'localhost';
-- 刷新權限信息,有的權限是可以動態加載的。為了避免權限出題，每次都執行這個語句
FLUSH PRIVILEGES;
回收權限
-- ON 指定數據庫.表
-- FROM 指定用戶
REVOKE SHOW DATABASES,SELECT ON *.* FROM 'db_dev'@'localhost';
-- 刷新權限信息
FLUSH PRIVILEGES;
角色
使用數據庫的人員可能有，開發，DBA，運營相關（只會查詢數據），程序運行。
角色激活
給用戶賦予角色之后，角色默認不激活的。用戶可以在會話中激活用戶賦予的角色。
也可以設置參數，讓所有角色都激活，這樣用戶登錄成功，賦予的角色全選就可以使用了
-- 查看當前用戶下使用了哪些角色
SELECT CURRENT_ROLE();
-- 登錄之后激活定義的所有角色，給用戶賦予哪些角色，就可以使用這些角色的權限
SET global activate_all_roles_on_login=ON;
-- 在會話中修改激活哪些角色
SET ROLE ops;
創建及刪除角色
-- 開發(dev)，db(db)，運營(ops)，程序運行(App_run)
CREATE ROLE 'app_run', 'db', 'ops', 'dev';
-- 刪除角色
DROP ROLE 'db', 'app_run';
給角色分配權限
- 開發
開發一般會，創建數據庫和表，crud，操作索引，修改表結構
drop 權限我建議不要給
-- crud,創建
GRANT SELECT, INSERT, UPDATE, DELETE,CREATE,CREATE VIEW,ALTER,SHOW DATABASES,SHOW VIEW,ALTER,INDEX,PROCESS,RELOAD,LOCK TABLES ON *.* TO 'dev';
- db
db 一般擁有所有權限
-- WITH GRANT OPTION 是擁有給用戶授權的權限
GRANT ALL PRIVILEGES ON *.* TO 'db' WITH GRANT OPTION;
- 運營相關
基本都是查詢語句
-- 或者指定某個具體數據庫，或者表
GRANT SELECT,SHOW DATABASES,SHOW VIEW ON *.* TO 'ops';
- 程序運行相關
為了使用 flyway 這種可以修改表結構和索引的組件。對權限賦予 CREATE,INDEX,ALTER.
DELETE 語句不要怕，現在 mybatis plus 類似的組件，都帶有安全刪除的校驗，全表刪除或者全表更新必須帶條件。在一定程度上避免刪除表中所有數據。
GRANT SELECT, INSERT, UPDATE, DELETE,CREATE,CREATE VIEW,SHOW DATABASES,SHOW VIEW,ALTER,INDEX,RELOAD,LOCK TABLES,CREATE TEMPORARY TABLES ON *.* TO 'app_run';
給用戶賦予角色
-- 給用戶賦予 ops 角色
GRANT 'ops' TO 'db_dev'@'localhost';
撤銷角色或者角色的權限
-- 從用戶撤銷某個角色
REVOKE ops FROM db_dev1@localhost;
-- 從角色中撤銷某個權限
REVOKE SHOW VIEW ON *.* FROM 'ops';
-- 刷新權限
FLUSH PRIVILEGES;
查詢用戶的權限
-- 顯示來自 localhost 登錄的 test 用戶
SHOW GRANTS FOR 'test'@'localhost';
SHOW GRANTS FOR 'root'@'%';
-- 來自某個角色 USEING 指定的角色的權限
SHOW GRANTS FOR 'read_user1'@'localhost' USING 'ops';
本文由 張攀欽的博客 http://www.mflyyou.cn/ 創作。 可自由轉載、引用，但需署名作者且注明文章出處。
