GRANT <role>

    GrantRoleStmt

    RolenameList

    GRANT <role> - 图2

    以 root 用户连接 TiDB：

    1. mysql -h 127.0.0.1 -P 4000 -u root

    创建新角色 analyticsteam 和新用户 jennifer：

    1. CREATE ROLE analyticsteam;
    2. Query OK, 0 rows affected (0.02 sec)
    4. GRANT SELECT ON test.* TO analyticsteam;
    5. Query OK, 0 rows affected (0.02 sec)
    7. CREATE USER jennifer;
    8. Query OK, 0 rows affected (0.01 sec)
    10. GRANT analyticsteam TO jennifer;
    11. Query OK, 0 rows affected (0.01 sec)

    以 jennifer 用户连接 TiDB：

    1. SHOW GRANTS;
    2. +---------------------------------------------+
    3. | Grants for User                             |
    5. | GRANT USAGE ON *.* TO 'jennifer'@'%'        |
    6. | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' |
    7. +---------------------------------------------+
    9. SHOW TABLES in test;
    10. ERROR 1044 (42000): Access denied for user 'jennifer'@'%' to database 'test'
    11. SET ROLE analyticsteam;
    12. Query OK, 0 rows affected (0.00 sec)
    14. SHOW GRANTS;
    15. +---------------------------------------------+
    16. | Grants for User                             |
    17. +---------------------------------------------+
    18. | GRANT USAGE ON *.* TO 'jennifer'@'%'        |
    19. | GRANT SELECT ON test.* TO 'jennifer'@'%'    |
    20. | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' |
    21. +---------------------------------------------+
    22. 3 rows in set (0.00 sec)
    24. SHOW TABLES IN test;
    25. +----------------+
    26. | Tables_in_test |
    27. +----------------+
    28. | t1             |
    29. +----------------+

    以 root 用户连接 TiDB：

      执行 SET DEFAULT ROLE 语句将用户 jennifer 与 analyticsteam 角色相关联：

      以 jennifer 用户连接 TiDB：

      1. mysql -h 127.0.0.1 -P 4000 -u jennifer

      此时 jennifer 用户无需执行 SET ROLE 语句就能拥有 analyticsteam 角色相关联的权限：

      1. SHOW GRANTS;
      2. +---------------------------------------------+
      3. | Grants for User                             |
      4. +---------------------------------------------+
      5. | GRANT USAGE ON *.* TO 'jennifer'@'%'        |
      6. | GRANT SELECT ON test.* TO 'jennifer'@'%'    |
      7. | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' |
      8. +---------------------------------------------+
      9. 3 rows in set (0.00 sec)
      11. SHOW TABLES IN test;
      12. +----------------+
      13. | Tables_in_test |
      14. +----------------+
      15. | t1             |
      16. +----------------+