背景

从 MairaDB 10.0.5 开始，MariaDB 开始提供 Role（角色）的功能，补全了大家一直吐槽的 MySQL 不能像 Oracle 一样支持角色定义的功能。

一个角色就是把一堆的权限捆绑在一起授权，这个功能对于有很多用户拥有相同权限的情况可以显著提高管理效率。在有角色之前，这种情况只能为每个用户都做一大堆的授权操作，或者是给很多个需要相同权限的用户提供同一个账号去使用，这又会导致你要分析用户行为的时候不知道哪个操作是哪个具体用户发起的。

有了角色，这样的管理就太容易了。例如，可以把权限需求相同的用户赋予同一个角色，只要定义好这个角色的权限就行，要更改这类用户的权限，只需要更改这个角色的权限就可以了，变化会影响到所有这个角色的用户。

用法

创建角色需要使用 CREATE ROLE 语句，删除角色使用 DROP ROLE 语句。然后再通过 GRANT 语句给角色增加授权，也可以把角色授权给用户，然后这个角色的权限就会分配给这个用户。同样，REVOKE 语句也可以用来移除角色的授权，或者把一个用户移除某个角色。

一旦用户连接上来，他可以执行 SET ROLE 语句来把自己切换到某个被授权的角色下，从而使用这个角色的权限。通过 CURRENT_ROLE 函数可以显示当前用户执行在哪个角色下，没有就是 NULL。

只有直接被授予用户的角色才可以使用 SET ROLE 语句，间接授予的角色并不能被 SET ROLE 设置。例如角色B被授予角色A，而角色A被授予用户A，那么用户A只能 SET ROLE 角色A，而不能设置角色B。

从 MariaDB 10.1.1 开始，可以利用 SET DEFAULT ROLE 语句来给某个用户设置默认的角色。当用户链接的时候，会默认使用这个角色，其实就是连接后自动做了一个 SET ROLE 语句。

使用举例

创建一个角色并给他赋权:

  1. CREATE ROLE journalist;
  2. GRANT SHOW DATABASES ON *.* TO journalist;
  3. GRANT journalist to hulda;

这里 hulda 并不马上拥有 SHOW DATABASES 权限，他还需要先执行一个 SET ROLE 语句启用这个角色：

  1. SHOW DATABASES;
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | information_schema |
  6. +--------------------+
  7. SELECT CURRENT_ROLE;
  8. +--------------+
  9. | CURRENT_ROLE |
  10. +--------------+
  11. | NULL         |
  12. +--------------+
  13. SET ROLE journalist;
  14. SELECT CURRENT_ROLE;
  15. +--------------+
  16. | CURRENT_ROLE |
  17. +--------------+
  18. | journalist   |
  19. +--------------+
  20. SHOW DATABASES;
  21. +--------------------+
  22. | Database           |
  23. +--------------------+
  24. | ...                |
  25. | information_schema |
  26. | mysql              |
  27. | performance_schema |
  28. | test               |
  29. | ...                |
  30. +--------------------+
  31. SET ROLE NONE;

角色也可以授权给另一个角色：

  1. CREATE ROLE writer;
  2. GRANT SELECT ON data.* TO writer;
  3. GRANT writer TO journalist;

但是只能 SET ROLE 直接给用户的角色。像这里 hulda 只能 SET ROLE journalist，而不能 SET ROLE writer，并且只要启用了 journalist 角色，hulda 也自动获得了 writer 角色的权限：

  1. SELECT CURRENT_ROLE;
  2. +--------------+
  3. | CURRENT_ROLE |
  4. +--------------+
  5. | NULL         |
  6. +--------------+
  7. SHOW TABLES FROM data;
  8. Empty set (0.01 sec)
  10. SET ROLE journalist;
  11. SELECT CURRENT_ROLE;
  12. +--------------+
  13. | CURRENT_ROLE |
  14. +--------------+
  15. | journalist   |
  16. +--------------+
  17. SHOW TABLES FROM data;
  18. +------------------------------+
  19. | Tables_in_data               |
  20. +------------------------------+
  21. | set1                         |
  22. | ...                          |
  23. +------------------------------+

角色和视图、存储过程。

当用户设置启用了一个角色，从某种意义上说他有两个身份的权限集合（用户本身和他的角色）但是一个视图或者存储过程只能有一个定义者。所以，当一个视图或者存储过程通过 SQL SECURITY DEFINER 创建时，只能指定 CURRENT_USER 或者 CURRENT_ROLE 中的一个。所以有些情况下，你创建了一个视图，但是你却可能没法使用它。

  1. CREATE ROLE r1;
  2. GRANT ALL ON db1.* TO r1;
  3. GRANT r1 TO foo@localhost;
  4. GRANT ALL ON db.* TO foo@localhost;
  5. SELECT CURRENT_USER
  6. +---------------+
  7. | current_user  |
  8. +---------------+
  9. | foo@localhost |
  10. +---------------+
  11. SET ROLE r1;
  12. CREATE TABLE db1.t1 (i int);
  13. CREATE VIEW db.v1 AS SELECT * FROM db1.t1;
  14. SHOW CREATE VIEW db.v1;
  15. +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  16. | View | Create View                                                                                                                              | character_set_client | collation_connection |
  17. +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  18. | v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY DEFINER VIEW `db`.`v1` AS SELECT `db1`.`t1`.`i` AS `i` from `db1`.`t1` | utf8                 | utf8_general_ci      |
  19. +------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  20. CREATE DEFINER=CURRENT_ROLE VIEW db.v2 AS SELECT * FROM db1.t1;
  21. SHOW CREATE VIEW db.b2;
  22. +------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  23. | View | Create View                                                                                                                 | character_set_client | collation_connection |
  24. +------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
  25. | v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`r1` SQL SECURITY DEFINER VIEW `db`.`v2` AS select `db1`.`t1`.`a` AS `a` from `db1`.`t1` | utf8                 | utf8_general_ci      |
  26. +------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+