用户场景:角色授权与权限分配
前提条件( >= 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身份验证插件一起使用,以启用与 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';