Mysql 權(quán)限管理-魔扣目錄

前言

為了避免刪庫(kù)跑路的事情，權(quán)限管理和數(shù)據(jù)備份是必要。

機(jī)器環(huán)境

MySQL 8.0.21 x86_64 MySQL Community Serve
centos 7

Mysql 權(quán)限管理

Mysql 8.0 可以創(chuàng)建角色，然后將操作數(shù)據(jù)庫(kù)、表、索引等的權(quán)限賦予給角色，將將角色賦予給用戶，也是我們熟悉的 RBAC 模型。

當(dāng)然也可以將權(quán)限直接授予用戶。

用戶

創(chuàng)建用戶

-- 用戶名稱是由 用戶名和登錄用戶的 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;

權(quán)限

用戶的權(quán)限信息保存在 information_schema.USER_PRIVILEGES 。也可以在 mysql.user 看到授權(quán)信息。

為了避免已經(jīng)建立的鏈接的權(quán)限無(wú)法刷新，需要搭建數(shù)據(jù)庫(kù)的時(shí)候，權(quán)限就要設(shè)計(jì)好。

有部分權(quán)限是可以動(dòng)態(tài)修改的，但是有的權(quán)限，在一個(gè)會(huì)話中是不能修改的。為了避免問(wèn)題，需要數(shù)據(jù)庫(kù)使用之前就要做好權(quán)限規(guī)劃。

權(quán)限說(shuō)明

grant 用法

權(quán)限	說(shuō)明
ALL	所有的權(quán)限，除了 `GRANT OPTION` and `PROXY`.
ALTER	修改表結(jié)構(gòu)，`ALTER TABLE`
CREATE	創(chuàng)建數(shù)據(jù)庫(kù)和表
DROP	刪除數(shù)據(jù)庫(kù)、表、視圖
GRANT OPTION	GRANT權(quán)限允許你把你自己擁有的那些權(quán)限授給其他的用戶。可以用于數(shù)據(jù)庫(kù)、表和保存的程序。
DELETE	刪除表數(shù)據(jù)
INDEX	INDEX權(quán)限允許你創(chuàng)建或刪除索引。
INSERT	插入表數(shù)據(jù)
SELECT	查詢表數(shù)據(jù)
UPDATE	更新表數(shù)據(jù)
PROCESS	show processlist 命令顯示在服務(wù)器內(nèi)執(zhí)行的線程的信息（即其它賬戶相關(guān)的客戶端執(zhí)行的語(yǔ)句）。
SHOW VIEW	查看視圖
SHOW DATABASES	查看數(shù)據(jù)庫(kù)列表，沒(méi)有授予這個(gè)權(quán)限，只能查看到 `information_schema`
LOCK TABLES	鎖表
RELOAD	FLUSH 相關(guān)的操作
CREATE TABLESPACE	允許使用操作表空間和日志的語(yǔ)句，比如創(chuàng)建，刪除，修改
CREATE TEMPORARY TABLES	創(chuàng)建臨時(shí)表

授權(quán)

-- 對(duì)從 localhost 登錄的用戶 db_dev 的數(shù)據(jù)庫(kù)：ceshi 中所有表（*） 授予 SHOW DATABASES,SELECT,RELOAD 權(quán)限
GRANT SHOW DATABASES,SELECT,RELOAD ON ceshi.* TO 'db_dev'@'localhost';

-- 也可以針對(duì)某個(gè)表授權(quán)，`` 是為了處理關(guān)鍵字，當(dāng)沒(méi)有關(guān)鍵字可以 ceshi.test1 就可以
GRANT SELECT ON ceshi.`test1` TO 'db_dev'@'localhost';

-- 刷新權(quán)限信息,有的權(quán)限是可以動(dòng)態(tài)加載的。為了避免權(quán)限出題，每次都執(zhí)行這個(gè)語(yǔ)句
FLUSH PRIVILEGES;

回收權(quán)限

-- ON 指定數(shù)據(jù)庫(kù).表 
-- FROM 指定用戶
REVOKE SHOW DATABASES,SELECT ON *.* FROM 'db_dev'@'localhost';
-- 刷新權(quán)限信息
FLUSH PRIVILEGES;

角色

使用數(shù)據(jù)庫(kù)的人員可能有，開(kāi)發(fā)，DBA，運(yùn)營(yíng)相關(guān)（只會(huì)查詢數(shù)據(jù)），程序運(yùn)行。

角色激活

給用戶賦予角色之后，角色默認(rèn)不激活的。用戶可以在會(huì)話中激活用戶賦予的角色。

也可以設(shè)置參數(shù)，讓所有角色都激活，這樣用戶登錄成功，賦予的角色全選就可以使用了

-- 查看當(dāng)前用戶下使用了哪些角色
SELECT CURRENT_ROLE();

-- 登錄之后激活定義的所有角色，給用戶賦予哪些角色，就可以使用這些角色的權(quán)限
SET global activate_all_roles_on_login=ON;

-- 在會(huì)話中修改激活哪些角色
SET ROLE ops;

創(chuàng)建及刪除角色

-- 開(kāi)發(fā)(dev)，db(db)，運(yùn)營(yíng)(ops)，程序運(yùn)行(App_run)
CREATE ROLE 'app_run', 'db', 'ops', 'dev';

-- 刪除角色
DROP ROLE 'db', 'app_run';

給角色分配權(quán)限

開(kāi)發(fā)

開(kāi)發(fā)一般會(huì)，創(chuàng)建數(shù)據(jù)庫(kù)和表，crud，操作索引，修改表結(jié)構(gòu)

drop 權(quán)限我建議不要給

-- crud,創(chuàng)建
GRANT SELECT, INSERT, UPDATE, DELETE,CREATE,CREATE VIEW,ALTER,SHOW DATABASES,SHOW VIEW,ALTER,INDEX,PROCESS,RELOAD,LOCK TABLES ON *.* TO 'dev';

db 一般擁有所有權(quán)限

-- WITH GRANT OPTION 是擁有給用戶授權(quán)的權(quán)限
GRANT ALL PRIVILEGES ON *.* TO 'db' WITH GRANT OPTION;

運(yùn)營(yíng)相關(guān)

基本都是查詢語(yǔ)句

-- 或者指定某個(gè)具體數(shù)據(jù)庫(kù)，或者表
GRANT SELECT,SHOW DATABASES,SHOW VIEW ON *.* TO 'ops';

程序運(yùn)行相關(guān)

為了使用 flyway 這種可以修改表結(jié)構(gòu)和索引的組件。對(duì)權(quán)限賦予 CREATE,INDEX,ALTER.

DELETE 語(yǔ)句不要怕，現(xiàn)在 mybatis plus 類似的組件，都帶有安全刪除的校驗(yàn)，全表刪除或者全表更新必須帶條件。在一定程度上避免刪除表中所有數(shù)據(jù)。

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

撤銷(xiāo)角色或者角色的權(quán)限

-- 從用戶撤銷(xiāo)某個(gè)角色
REVOKE ops FROM db_dev1@localhost;

-- 從角色中撤銷(xiāo)某個(gè)權(quán)限
REVOKE SHOW VIEW ON *.* FROM 'ops';

-- 刷新權(quán)限
FLUSH PRIVILEGES;

查詢用戶的權(quán)限

-- 顯示來(lái)自 localhost 登錄的 test 用戶
SHOW GRANTS FOR 'test'@'localhost';
SHOW GRANTS FOR 'root'@'%';

-- 來(lái)自某個(gè)角色 USEING 指定的角色的權(quán)限
SHOW GRANTS FOR 'read_user1'@'localhost' USING 'ops';