---
title: "Managing Database Users"
version: "v2"
status: "GA"
description: "In this hands-on lab, you will learn how to manage TiDB users. Specifically, we will cover how to manage TiDB users, interact with the TiDB cluster, and monitor the TiDB cluster. This lab is a practical workshop that allows you to learn how to manage TiDB users."
---

# Lab 3: Managing Database Users
 
## Overview

>[!NOTE]
> This hands-on lab is also included in the [TiDB Certification Training: TiDB Administration Using TiUP](https://www.pingcap.com/education/course-tidb-database-administration-for-self-hosted-deployments/) course.

In this lab, you create and configure users and roles, and modify user permissions so that the user can execute queries on the database. The diagram below illustrates the deployment topology of the TiDB cluster.

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


> [!TIP]
> Click **Start** to launch the lab environment.
> Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**.
> And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks.

## Module 1: Creating Users and Roles

### Step 1. Log into the remote Linux

+ Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab.

+ The banner after you logged in:

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

### Step 2. Connect to TiDB

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

### Step 3. Create user `jack`

+ Create a new user called `'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)
  ```

### Step 4. Create new roles

+ Create two new roles called `r_manager` and `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)
  ```

### Step 5. Check the users and roles

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

+ Both the users and roles are stored in the `mysql.user` table.  Roles have no password.

### Step 6. Check `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)
  ```

### Step 7. Change the password for `jack`

+ Change the password for `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}'` to `tidb` by using the `ALTER USER` statement and then exit the session. 
  
  ```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
  ```

### Step 8. Connect to TiDB as `jack`

+ Connect to TiDB as the `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}’` user with the new password, and then exit the session.

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

### Step 9. Connect to TiDB as `root`

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

### Step 10. Remove `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)
  ```

### Step 11. Remove `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)
  ```

### Step 12. Remove user `jack`
+ Remove the `'jack'@'${LAB:HOST_MONITOR1_PRIVATE_IP}’` user using `DROP USER` statement.
  
  ```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
  ```

## 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`. P
   
  ```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).

+ The banner after you logged in:

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

### 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.

## End of Exercises