# 实验 3：管理 TiDB 用户

## 概述
在本实验中，你将创建并配置用户和角色，以及修改用户权限，以便用户能够在数据库上执行查询。下图展示了 TiDB 集群的部署拓扑结构。

   ![60%](https://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExercise_aa_007_20220914.png)


> [!TIP]
> 点击 **开始** 启动实验环境。
> 环境准备就绪后，点击底部工具栏中的 **前进** 进入 **模块 1**。
> 然后，按照实验指南的步骤连接到实践环境并开始你的任务。

## 模块 1：创建用户和角色

### 步骤 1：登录远程 Linux

+ 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。

+ 登录后显示的横幅：

    ```output
     ,     #_
    ~\_  ####_        Amazon Linux 2
    ~~  \_#####\
    ~~     \###|      
    ~~       \#/ ___
    ~~       V~' '->
    ~~~         /    A newer version of Amazon Linux is available!
     ~~._.   _/
        _/ _/       Amazon Linux 2023, GA and supported until 2028-03-15.
      _/m/'           https://aws.amazon.com/linux/amazon-linux-2023/
    ```

### 步骤 2. 连接到 TiDB

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

### 步骤 3. 创建用户 `jack`

+ 创建一个名为 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'`的新用户。
 
  ```sql
  CREATE USER 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
  ```
  ```output
  tidb> CREATE USER 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
  Query OK, 0 rows affected (0.03 sec)
  ```

### 步骤 4. 创建新角色

+ 创建两个名为 `r_manager` 和 `r_staff` 的新角色。
  
  ```sql
  CREATE ROLE r_manager, r_staff;
  ```
  ```output
  tidb> CREATE ROLE r_manager, r_staff;
  Query OK, 0 rows affected (0.02 sec)
  ```

### 步骤 5. 查看用户和角色

  ```sql
  SELECT user, host, authentication_string FROM mysql.user\G
  ```
  ```output
  tidb> SELECT user, host, authentication_string FROM mysql.user\G
  *************************** 1. row ***************************
                    user: root
                    host: %
  authentication_string: *7451D85E369BF796CD15900AF9BFE0D5D920716E
  *************************** 2. row ***************************
                    user: jack
                    host: 172.31.0.106
  authentication_string: *926E4B88EB93FD344DF0870EE025D6EB153C02DE
  *************************** 3. row ***************************
                    user: r_manager
                    host: %
  authentication_string:
  *************************** 4. row ***************************
                    user: r_staff
                    host: %
  authentication_string:
  4 rows in set (0.00 sec)
  ```

+ 用户和角色都存储在 `mysql.user` 表中。角色没有密码。

### 步骤 6. 查看 `r_staff`

  ```sql
  SELECT * FROM mysql.user WHERE user='r_staff'\G
  ```
  ```output
  tidb> SELECT * FROM mysql.user WHERE user='r_staff'\G
  *************************** 1. row ***************************
                    Host: %
                    User: r_staff
   authentication_string: 
                  plugin: mysql_native_password
             Select_priv: N
             Insert_priv: N
             Update_priv: N
             Delete_priv: N
             Create_priv: N
               Drop_priv: N
               ...
              Event_priv: N
         Repl_slave_priv: N
        Repl_client_priv: N
            Trigger_priv: N
        Create_role_priv: N
          Drop_role_priv: N
          Account_locked: Y
           Shutdown_priv: N
             Reload_priv: N
               FILE_priv: N
             Config_priv: N
  Create_Tablespace_Priv: N
  1 row in set (0.00 sec)
  ```

### 步骤 7. 修改 `jack` 的密码

+ 使用 `ALTER USER` 语句将 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 的密码更改为 `tidb` ，然后退出会话。请手动 `${HOST_MONITOR1_PRIVATE_IP}` 替换为真实 IP。
  
  ```sql
  ALTER USER 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'tidb';
  ```
  ```output
  tidb> ALTER USER 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'tidb';
  Query OK, 0 rows affected (0.02 sec)
  ```

  ```sql
  EXIT;
  ```
  ```output
  tidb> EXIT
  Bye
  ```

### 步骤 8. 以 `jack` 身份连接到 TiDB

+ 使用新密码以 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}’` 用户身份连接到 TiDB，然后退出会话。

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -ujack -ptidb
  ```
  ```sql
  EXIT
  ```
  ```output
  tidb> EXIT
  Bye
  ```

### 步骤 9. 以 `root` 身份连接到 TiDB

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

### 步骤 10. 删除 `r_staff`
   
  ```sql
  DROP ROLE r_staff;
  ```
  ```output
  tidb> DROP ROLE r_staff;
  Query OK, 0 rows affected (0.03 sec)    
  ```

  ```sql
  SELECT user, host FROM mysql.user WHERE user='r_staff';
  ```
  ```output
  tidb> SELECT user, host FROM mysql.user WHERE user='r_staff';
  Empty set (0.01 sec)
  ```

### 步骤 11. 删除 `r_manager`

  ```sql
  DROP ROLE r_manager;
  ```
  ```output
  tidb> DROP ROLE r_manager;
  Query OK, 0 rows affected (0.03 sec)
  ```
  ```sql
  SELECT user, host FROM mysql.user WHERE user='r_manager';
  ```
  ```output
  tidb> SELECT user, host FROM mysql.user WHERE user='r_manager';
  Empty set (0.01 sec)
  ```

### 步骤 12. 删除用户 `jack`
+ 使用 `DROP USER` 语句删除 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}’`  用户。
  
  ```sql
  DROP USER 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}';
  ```
  ```output
  tidb> DROP USER 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}';
  Query OK, 0 rows affected (0.02 sec) 
  ```

  ```sql
  SELECT user, host FROM mysql.user WHERE user='jack';
  ```
  ```output
  tidb> SELECT user, host FROM mysql.user WHERE user='jack';
  Empty set (0.00 sec)
  ```

  ```sql
  EXIT
  ```
  ```output
  tidb> EXIT
  Bye
  ```

## 模块 2: 授予权限

### 步骤 1. 连接到 TiDB

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root 
  ```
  ```sql
  USE test;
  ```

  ```output
  tidb> USE test;
  Database changed
  ```

### 步骤 2. 创建表 `emp`

  ```sql
  CREATE TABLE emp(id int, name varchar(20));
  ```
  ```output
  tidb> CREATE TABLE emp(id int, name varchar(20));
  Query OK, 0 rows affected (0.08 sec)
  ```

### 步骤 3. 向 `emp` 表插入一些数据 

  ```sql
  INSERT INTO emp VALUES(1,'tom');
  ```
  ```output
  tidb> INSERT INTO emp VALUES(1,'tom');
  Query OK, 1 row affected (0.01 sec)
  ```

  ```sql
  INSERT INTO emp VALUES(2,'jack');
  ```
  ```output
  tidb> INSERT INTO emp VALUES(2,'jack');
  Query OK, 1 row affected (0.01 sec)
  ```

### 步骤 4. 创建用户 `jack`

+ 创建一个名为 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 且密码为 `pingcap` 的新用户。 
   
  ```sql
  CREATE USER 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
  ```
  ```output
  tidb> CREATE USER 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
  Query OK, 0 rows affected (0.02 sec)
  ```

### 步骤 5. 创建两个角色 `r_mgr` 和 `r_emp`

  ```sql
  CREATE ROLE r_mgr, r_emp;
  ```
  ```output
  tidb> CREATE ROLE r_mgr, r_emp;
  Query OK, 0 rows affected (0.03 sec)
  ```

### 步骤 6. 向 `r_emp` 授予权限

+ 将 `test` 数据库中 `emp` 表的 `SELECT` 权限授予 `r_emp` 角色。
  
  ```sql
  GRANT SELECT ON test.emp TO r_emp;
  ```
  ```output
  tidb> GRANT SELECT ON test.emp TO r_emp;
  Query OK, 0 rows affected (0.02 sec)
  ```

### 步骤 7. 向 `r_mgr` 授予权限

+ 将 `test` 数据库中所有表的 `INSERT`, `UPDATE` 和 `DELETE` 权限授予 `r_mgr` 角色。
  
  ```sql
  GRANT INSERT, UPDATE, DELETE ON test.* TO r_mgr;
  ```
  ```output
  tidb> GRANT INSERT, UPDATE, DELETE ON test.* TO r_mgr;
  Query OK, 0 rows affected (0.02 sec)
  ```

### 步骤 8. 将 `r_emp` 角色授予 `r_mgr` 和 `jack`

+ 将 `r_emp` 角色授予 `r_mgr` 和 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}’`. 
  
  ```sql
  GRANT r_emp TO r_mgr, 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}';
  ```
  ```output
  tidb> GRANT r_emp TO r_mgr, 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}';
  Query OK, 0 rows affected (0.03 sec)
  ```

### 步骤 9. 创建表 `dept` 并插入一些数据
   
  ```sql
  CREATE TABLE dept(id int, dname varchar(20));
  ```
  ```output
  tidb> CREATE TABLE dept(id int, dname varchar(20));
  Query OK, 0 rows affected (0.08 sec)
  ```

  ```sql
  INSERT INTO dept VALUES(1, 'dev');
  ```
  ```output
  tidb> INSERT INTO dept VALUES(1, 'dev');
  Query OK, 1 row affected (0.01 sec)
  ```

  ```sql
  INSERT INTO dept VALUES(2, 'sales');
  ```
  ```output
  tidb> INSERT INTO dept VALUES(2, 'sales');
  Query OK, 1 row affected (0.01 sec)
  ```

### 步骤 10. 向 `jack` 授予权限

+ 将 `test` 数据库中 `dept` 表的 `SELECT` 权限授予 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 用户。
  
  ```sql
  GRANT SELECT ON test.dept TO 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}';
  ```
  ```output
  tidb> GRANT SELECT ON test.dept TO 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}';
  Query OK, 0 rows affected (0.02 sec)
  ```

### 步骤 11. 启动一个新终端并登录到你的虚拟机

+ 我们将这个新终端称为[终端 2](ssh://monitor1-ssh#2) 。

+ 登录后显示的横幅：

  ```output
     ,     #_
     ~\_  ####_        Amazon Linux 2
     ~~  \_#####\
     ~~     \###|       
     ~~       \#/ ___
     ~~       V~' '->
        ~~~         /    A newer version of Amazon Linux is available!
        ~~._.   _/
           _/ _/       Amazon Linux 2023, GA and supported until 2028-03-15.
           _/m/'           https://aws.amazon.com/linux/amazon-linux-2023/
  ```

### 步骤 12. 在终端2中，以 `jack` 身份连接到 `TiDB`

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} -P4000 -ujack -ppingcap
  ```

### 步骤 13. 在终端2中，从 `emp` 表和 `dept` 表检索数据。
  
  ```sql
  USE test;
  ```
  ```output
  tidb> USE test;
  Database changed
  ```

  ```sql
  SELECT * FROM emp LIMIT 1;
  ```
  ```output
  tidb> SELECT * FROM emp LIMIT 1;
  ERROR 1142 (42000): SELECT command denied TO user 'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}' for TABLE 'emp'
  ```

  ```sql
  SELECT * FROM dept LIMIT 1;
  ```
  ```output
  tidb> SELECT * FROM dept LIMIT 1;
  +------+-------+
  | id   | dname |
  +------+-------+
  |    1 | dev   |
  +------+-------+
  1 row in set (0.00 sec)
  ```

  + 用户 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 没有从 `emp` 表检索行的权限。该权限是通过 `r_emp` 角色授予的，但在这个会话中未启用该角色。

  + 用户 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 有权从 `dept` 表检索行。该权限是直接授予该用户的。

### 步骤 14. 查看 `jack` 的角色

+ 查看 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 在会话中激活了哪些角色，并显示授予该用户的所有权限。
  
  ```sql
  SELECT CURRENT_ROLE();
  ```
  ```output
  tidb> SELECT CURRENT_ROLE();
  +----------------+
  | CURRENT_ROLE() |
  +----------------+
  | NONE           |
  +----------------+
  1 row in set (0.00 sec)
  ```
  ```sql
  SHOW GRANTS;
  ```
  ```output
  tidb> SHOW GRANTS;
  +----------------------------------------------------+
  | GRANT USAGE ON *.* TO 'jack'@'172.31.0.106'        |
  | GRANT SELECT ON test.dept TO 'jack'@'172.31.0.106' |
  | GRANT 'r_emp'@'%' TO 'jack'@'172.31.0.106'         |
  +----------------------------------------------------+
  3 rows in set (0.00 sec)
  ```

  + 该会话没有启用任何角色。

  + 用户已被授予 `r_emp` 角色，但尚未激活。

### 在 15. 激活授予 `jack` 的所有角色

+ 在已连接的会话中激活授予用户 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 的所有角色，并查看已启用了哪些角色。然后，再次显示授予该用户的所有权限。
  
  ```sql
  SET ROLE ALL;
  ```
  ```output
  tidb> SET ROLE ALL;
  Query OK, 0 rows affected (0.00 sec)
  ```

  ```sql
  SELECT CURRENT_ROLE();
  ```
  ```output
  tidb> SELECT CURRENT_ROLE();
  +----------------+
  | CURRENT_ROLE() |
  +----------------+
  | `r_emp`@`%`    |
  +----------------+
  1 row in set (0.01 sec)
  ```

  ```sql
  SHOW GRANTs;
  ```
  ```output
  tidb> SHOW GRANTS;
  +----------------------------------------------------+
  | GRANT USAGE ON *.* TO 'jack'@'172.31.0.106'        |
  | GRANT SELECT ON test.dept TO 'jack'@'172.31.0.106' |
  | GRANT SELECT ON test.emp TO 'jack'@'172.31.0.106'  |
  | GRANT 'r_emp'@'%' TO 'jack'@'172.31.0.106'         |
  +----------------------------------------------------+
  4 rows in set (0.00 sec)
  ```

+ 在会话中激活一个角色后，`SHOW GRANTS` 的结果将包含授予该角色的所有权限。

### 步骤 16. 在终端2中，尝试从 `emp` 表检索1行数据。
   
  ```sql
  SELECT * FROM emp LIMIT 1;
  ```
  ```output
  tidb> SELECT * FROM emp LIMIT 1;
  +------+------+
  | id   | name |
  +------+------+
  |    1 | tom  |
  +------+------+
  1 row in set (0.01 sec)
  ```

  + 用户 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 在启用 `r_emp` 角色后有权从`emp`表检索行

### 步骤 17. 在终端2中，从 `emp` 表删除数据

+ 在[终端 2](ssh://monitor1-ssh#2)中，尝试从 `emp` 表删除 `id`=1 的 `emp` 

  ```sql
  DELETE FROM emp WHERE id=1;
  ```
  ```output
  tidb> DELETE FROM emp WHERE id=1;
  DELETE command denied to user 'jack'@'172.31.0.106' for table 'emp'
  ```
     
  + 用户 `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` 没有从员工表中删除行的权限。

## 实验结束