Mysql 權限管理-魔扣目錄

前言

為了避免刪庫跑路的事情，權限管理和數據備份是必要。

機器環境

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 一般擁有所有權限

-- 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';