用户场景：角色授权与权限分配

前提条件( >= 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.* *

表权限介绍

表权限

列权限介绍

列权限

可以为表的各个列设置某些表权限。要使用列权限，请显式指定表，并在权限类型后提供列名列表。例如，以下语句将允许用户读取员工的姓名和职位，但不允许读取同一表中的其他信息，如工资。

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

功能权限介绍

功能权限

过程权限介绍

过程权限

代理权限介绍

代理权限

该权限允许一个用户代理为另一个用户，这意味着他们的权限更改为代理用户的权限，并且 CURRENT_USER（）函数返回代理用户的用户名。PROXY

该权限仅适用于支持它的身份验证插件。默认mysql_native_password身份验证插件不支持代理用户。PROXY

pam身份验证插件是 MariaDB 附带的唯一一个目前支持代理用户的插件。该权限通常与pam身份验证插件一起使用，以启用与 PAM 的用户和组映射。PROXY

例如，要向使用pam身份验证插件进行身份验证的匿名帐户授予权限，可以执行以下操作：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)