## Module 2: Granting Permissions

### Step 1. Connect to TiDB

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

  ```sql
  USE test;
  ```

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

### Step 2. Create table `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)
  ```

### Step 3. Insert some data `emp` table

  ```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)
  ```

### Step 4. Create user `jack`

+ Create a new user called `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` with the password `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)
  ```

### Step 5. Create two roles `r_mgr` and `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)
  ```

### Step 6. Grant privileges to `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)
  ```

### Step 7. Grant privileges to `r_mgr`

+ Grant the `INSERT`, `UPDATE`, and `DELETE` privileges on all tables in the `test` database to `r_mgr` role.

  ```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)
  ```

### Step 8. Grant the `r_emp` role to `r_mgr` and `jack`

+ Grant the `r_emp` role to `r_mgr` and `'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)
  ```

### Step 9. Create table `dept` and insert some data

  ```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)
  ```

### Step 10. Grant privilege to `jack`

+ Grant the `SELECT` privilege on the `dept` tables in the `test` database to `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` user account.

  ```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)
  ```

### Step 11. Start a new terminal and login to your virtual machine

+ Let's call this new terminal, [Terminal 2](ssh://monitor1-ssh#2).

### Step 12. In terminal 2, connect to `TiDB` as the `jack`

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

### Step 13. In terminal 2, retrive data from the `emp` and the `dept` table

  ```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)
  ```

+ User `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` does not have the privilege to retrieve rows from the `emp` table. The privilege is granted through the `r_emp` role, which is not enabled in this session.

+ User `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` has the privilege to retrieve rows from the `dept` table. The privilege is granted directly to the user.

### Step 14. Check the role of `jack`

+ Check what roles are activated in `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` session and show all the privileges granted to the user.

  ```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)
  ```

  + The session does not have any roles enabled.

  + The user has been granted the `r_emp` role, but it has not been activated.

### Step 15. Activate all roles granted to `jack`

+ Activate all the roles granted to the user `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` in the connected session and check what roles have been enabled. Then, show all the privileges granted to the user again.

  ```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)
  ```

+ After a role has been activated in the session, `SHOW GRANTS` result contains all the privileges granted to the role.

### Step 16. In terminal 2, try to retrieve 1 row of data from the `emp` tables

  ```sql
  SELECT * FROM emp LIMIT 1;
  ```

  ```output
  tidb> SELECT * FROM emp LIMIT 1;
  +------+------+
  | id   | name |
  +------+------+
  |    1 | tom  |
  +------+------+
  1 row in set (0.01 sec)
  ```

+ The user `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` has the privilege to retrieve rows from the `emp` table after the `r_emp` role has been enabled.

### Step 17. In terminal 2, delete data from `emp`

+ In [Terminal 2](ssh://monitor1-ssh#2) , try to delete the `emp` with `id`=1 from the `emp` table.

  ```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'
  ```

+ The user `'jack'@'${LABS:HOST_MONITOR1_PRIVATE_IP}'` does not have the privilege to delete rows from the employees table.