# 演習3: TiDB ユーザーの管理

## 概要

この演習では、ユーザーとロールを作成および構成し、ユーザーがデータベース上でクエリを実行できるようにユーザーの権限を変更します。以下の図は、TiDBクラスタのデプロイメントトポロジーを示しています。

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


> [!TIP]
> 演習環境を起動するには、**開始**をクリックしてください。
> 環境の準備が整ったら、下部のツールバーで **次のモジュール** を選択して **モジュール 1** に進みます。
> そして、ステップバイステップの演習ガイドに従って、ハンズオン環境に接続し、タスクを開始してください。

## モジュール 1: ユーザーとロールの作成

### 手順 1. 仮想マシンにログインする

+ ここをクリック - [ターミナル 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 に接続する

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`という2つの新しいロールを作成します
  
  ```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`に変更し、その後、セッションを終了します。
  
  ```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}’` ユーザーを削除します。実際の IP アドレス `${HOST_MONITOR1_PRIVATE_IP}` を手動で実際の 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` を作成する

+ パスワードが`pingcap`の新しいユーザー`'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.02 sec)
  ```

### 手順 5. 2つのロール `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` に権限を付与する

+ Grant the `SELECT` privilege on the `emp` tables in the `test` database to the `r_emp` role.
  
  ```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}'` に付与します。実際のIPアドレス `${HOST_MONITOR1_PRIVATE_IP}` を実際の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}'` ユーザーアカウントに付与します。実際のIPアドレス `${HOST_MONITOR1_PRIVATE_IP}` を実際の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@${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}'` には、従業員テーブルから行を削除する権限がありません。

## 演習終了