mysql角色授權模式案例參考

使用者場景：角色授權與權限配置設定

前提條件( >= mysql5.7)

角色權限配置設定參考示意圖

檢視目前使用者相關權限資訊

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS for 【指定使用者】;           

角色配置設定與授權相關操作介紹

mysql 權限介紹

全局層級

全局權限适用于一個給定伺服器中的所有資料庫。這些權限存儲在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .隻授予和撤銷全局權限。

資料庫層級

資料庫權限适用于一個給定資料庫中的所有目标。這些權限存儲在mysql.db和mysql.host表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.隻授予和撤銷資料庫權限。

表層級

表權限适用于一個給定表中的所有列。這些權限存儲在mysql.tables_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name隻授予和撤銷表權限。

列層級

列權限适用于一個給定表中的單一列。這些權限存儲在mysql.columns_priv表中。當使用REVOKE時，您必須指定與被授權列相同的列。

子程式層級

CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT權限适用于已存儲的子程式。這些權限可以被授予為全局層級和資料庫層級。而且，除了CREATE ROUTINE外，這些權限可以被授予為子程式層級，并存儲在mysql.procs_priv表中。

資料庫權限介紹

資料庫權限

下表列出了可在資料庫級别授予的權限。您還可以在資料庫級别授予所有表和函數特權。資料庫上的表和函數特權适用于該資料庫中的所有表或函數，包括以後建立的表或函數。

要為資料庫設定特權，請指定用于priv_level的資料庫，或僅用于指定預設資料庫。

db_name.*

*

特權	描述
CREATE	在為資料庫授予特權時，使用 CREATE DATABASE 語句建立資料庫。您可以授予對尚不存在的資料庫的特權。這還會授予對資料庫中所有表的特權。 CREATE CREATE
CREATE ROUTINE	使用 CREATE PROCEDURE 和 CREATE FUNCTION 語句 建立 存儲程式。
CREATE TEMPORARY TABLES	使用 CREATE TEMPORARY TABLE 語句 建立臨時表 。此權限允許寫入和删除這些臨時表
DROP	在授予資料庫特權時，使用 DROP DATABASE 語句删除資料庫。這還會授予對資料庫中所有表的特權。 DROP
EVENT	建立、删除和更改 s。 EVENT
GRANT OPTION	授予資料庫權限。您隻能授予您擁有的權限。
LOCK TABLES	使用 LOCK TABLES 語句擷取顯式鎖;您還需要對表具有權限，以便鎖定它。 SELECT

表權限介紹

表權限

ALTER	使用 ALTER TABLE 語句更改現有 表的結構 。
CREATE	使用 CREATE TABLE 語句 建立表 。您可以授予對尚不存在的表的權限。 CREATE
CREATE VIEW	CREATE_VIEW 語句建立視圖。
DELETE	DELETE 語句從表中删除行。
DELETE HISTORY	DELETE HISTORY 語句從表中删除 曆史行 。在運作 SHOW GRANTS 時顯示為在 MariaDB 10.3.15 和 MariaDB 10.4.5 （MDEV-17655） 之前運作，或者在運作 SHOW 權限直到 MariaDB 10.5.2、MariaDB 10.4.13 MariaDB 10.3.23 （MDEV-20382） 時顯示。來自 MariaDB 10.3.4 .從 MariaDB 10.3.5 中，如果使用者具有此權限但不是此權限，則運作 mysql_upgrade 也将授予此權限。 DELETE VERSIONING ROWS SUPER
DROP	使用 DROP TABLE 語句 删除表 或使用 DROP VIEW 語句删除視圖 。還需要執行 截斷表 語句。
GRANT OPTION	授予表權限。您隻能授予您擁有的權限。
INDEX	使用 CREATE INDEX 語句在表上 建立索引 。如果沒有該特權，則在建立表時，如果您具有 該 權限，則在建立表時仍可以建立索引;如果您具有該權限，則可以使用 ALTER TABLE 語句建立索引。 INDEX CREATE ALTER
INSERT	INSERT 語句向表中添加行。也可以在單個列上設定權限;有關詳細資訊 ，請參閱下面的列權限 INSERT
REFERENCES	閑置。
SELECT	SELECT 語句從表中讀取資料。也可以在單個列上設定權限;有關詳細資訊 SELECT
SHOW VIEW	顯示 CREATE VIEW 語句以使用 SHOW CREATE VIEW
TRIGGER	執行與更新的表關聯的觸發器，執行 CREATE TRIGGER DROP TRIGGER 語句。您仍然可以看到觸發器。
UPDATE	UPDATE 語句更新表中的現有行。 語句通常包含僅更新某些行的子句。您必須對表或子句的相應列具有特權。也可以在單個列上設定權限;有關詳細資訊 UPDATE WHERE SELECT WHERE UPDATE

列權限介紹

列權限

可以為表的各個列設定某些表權限。要使用列權限，請顯式指定表，并在權限類型後提供列名清單。例如，以下語句将允許使用者讀取員工的姓名和職位，但不允許讀取同一表中的其他資訊，如工資。

GRANT SELECT (name, position) on Employee to 'jeffrey'@'localhost';           

INSERT (column_list)	使用INSERT語句添加指定列中值的行。如果您隻有列級特權，則必須指定要在語句中設定的列。所有其他列将設定為其預設值，或 。INSERTINSERTNULL
REFERENCES (column_list)
SELECT (column_list)	使用SELECT語句讀取列中的值。您不能通路或查詢您沒有權限的任何列，包括 、 、 和 子句。SELECTWHEREONGROUP BYORDER BY
UPDATE (column_list)	使用UPDATE語句更新現有行的列中的值。 語句通常包含僅更新某些行的子句。您必須對表或子句的相應列具有特權。UPDATEWHERESELECTWHERE

功能權限介紹

功能權限

ALTER ROUTINE	使用 ALTER FUNCTION 語句更改存儲 函數 的特征。
EXECUTE	使用存儲的函數。您需要對函數通路的任何表或列具有權限。 SELECT
GRANT OPTION	授予函數權限。您隻能授予您擁有的權限。

過程權限介紹

過程權限

ALTER ROUTINE	使用 ALTER PROCEDURE 語句更改存儲 過程
EXECUTE	CALL 語句執行 存儲過程 。調用過程的權限可能允許您執行否則無法執行的操作，例如在表中插入行。
GRANT OPTION	授予過程權限。您隻能授予您擁有的權限。

代理權限介紹

代理權限

PROXY	允許一個使用者成為另一個使用者的代理。

該權限允許一個使用者代理為另一個使用者，這意味着他們的權限更改為代理使用者的權限，

并且 CURRENT_USER（）

函數傳回代理使用者的使用者名。

PROXY

該權限僅适用于支援它的身份驗證插件。預設

mysql_native_password

身份驗證插件不支援代理使用者。

PROXY

pam

身份驗證插件是 MariaDB 附帶的唯一一個目前支援代理使用者的插件。該權限通常與

身份驗證插件一起使用，以啟用

與 PAM 的使用者群組映射

PROXY

例如，要向使用

身份驗證插件進行身份驗證的

匿名帳戶

授予權限，可以執行以下操作：

PROXY

CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword'; GRANT ALL PRIVILEGES ON . TO 'dba'@'%' ; CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb'; GRANT PROXY ON 'dba'@'%' TO ''@'%';

僅當授予者還具有特定使用者帳戶的權限，并且定義了該權限時，使用者帳戶才能授予該特權。例如，以下示例失敗，因為授予者根本沒有該特定使用者帳戶的權限：

PROXY

PROXY

WITH GRANT OPTION

PROXY

資料來源：

格蘭特 - 瑪麗亞資料庫知識庫 (mariadb.com)

mysql 所在伺服器上登入給主賬号 root 授權(如不需要遠端授權，此步驟可以忽略)

-- % 替換成ip，就是指定的ip才能操作 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'dev-whbj@WHBJ' WITH GRANT OPTION;

-- 這個必須用 root@localhost 從本機登入後先授權，不然後續的操作，都無法遠端操作代理角色
GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION;

-- 重新整理權限
FLUSH PRIVILEGES;
           

準備測試庫

-- 建立測試庫 ,請勿以 test_ 開頭建立資料庫，不然預設授權給所有使用者，授權的相關操作相當于無效
CREATE DATABASE tst_db;
-- 建立測試表
CREATE TABLE `tst_db`.`test_1` (`id` int NOT NULL, `txt` text NOT NULL,   PRIMARY KEY (`id`) );
           

建立使用者（角色）

-- 建立兩個使用者，其中一個使用者可以當作是角色
create USER 'db_role1';  -- 測試角色
create USER 'user_1'; -- 測試使用者

-- 檢視使用者資訊
select * from mysql.user;           

角色授權

-- 角色授權給使用者
grant proxy on 'db_role1' to 'user_1';

-- 将權限授權給角色
-- 使用 * 時，授權資料庫層級權限
grant select,insert,update,delete,create on tst_db.* to db_role1;
-- 通過資料庫層面檢視資料庫權限資訊
select * from mysql.db where User='db_role1';

-- 指定表名時，授予 mysql.tables_priv 表級權限
grant select,insert,update,delete on tst_db.test_1 to db_role1;
-- 檢視使用者表權限資訊
select * from mysql.tables_priv where User='db_role1';           

權限立即生效

-- 立即啟用修改
flush  privileges ;
           

權限回收相關介紹

收回角色權限時，需要根據 全局權限 、資料庫權限、表權限、 列權限 、子程式權限 5個方面的權限查詢後，收回對應的權限，避免權限回收失敗，導緻權限仍然生效的尴尬情況

-- 檢視使用者的所有權限 (會顯示所有的授權語句)
-- 來源: https://mariadb.com/kb/en/show-grants/
SHOW GRANTS for db_role1;
           

-- 參照頂部的 mysql 權限介紹，進行權限查詢和回收
-- 查詢全局權限
select * from mysql.user where  User='db_role1';
-- 回收使用者的全局權限
REVOKE ALL ON *.* FROM db_role1;

-- 資料庫權限
select * from mysql.db where User='db_role1';
-- 回收使用者的資料庫權限
REVOKE ALL ON tst_db.* FROM db_role1;

-- 表級權限
select * from mysql.tables_priv where User='db_role1';
-- 回收表級權限（同時也會進行列權限、子程式權限 回收）
REVOKE ALL ON tst_db.test_1 FROM db_role1;

-- 列權限
select * from mysql.columns_priv where User='db_role1';

--  回收列權限,可指定相應的列權限進行回收，使用All時，回包括表權限進行回收
REVOKE ALL ON tst_db.test_1 FROM db_role1;

-- 子程式權限
select * from mysql.procs_priv where User='db_role1';

           

參考來源:

撤銷 - 瑪麗亞資料庫知識庫 (mariadb.com)