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)