# Lab 3: Managing TiDB Users
### Overview
In these exercises, 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.
### Notes
+ Click the private key file [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download it. It is required to log into the Control Machine for this lab.
+ Commands and SQL statements presented on this page are clickable for easy copy and paste.
+ Your environment may be different with these exercises, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide.
+ The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt.
### Assumptions
+ You have downloaded and secured the TiUP Control Machine private key (for example the file is saved under `~/.ssh/` folder locally): [chmod 400 ~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)
+ Windows users please refer to this [guide](https://github.com/pingcap-inc/tidb-course-labs/blob/master/README_303_class_setup_guide_for_student_lab_control_en.md) for private key preparation.
## Exercise 1
### Subject: Creating Users and Roles
### Overview
+ In this exercise, you create and configure users and roles. You will perform all tasks on `Control Machine` (`${HOST_MONITOR1_PUBLIC_IP}`).
### Duration
+ This exercise should take you approximately 25 minutes to complete.
### Tasks
+ Connect to TiDB and create a new user called `'jack'@'{HOST_MONITOR1_PRIVATE_IP}'`.
+ Create two new roles called `r_manager` and `r_staff`.
+ Change the password for `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` and then exit the session.
+ Connect to TiDB as the `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` user with the new password, and then exit the session.
+ Connect to TiDB as user `root`.
+ Remove the `r_staff`, `r_manager`, `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` role using `DROP USER` statement.
### Solution
1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`.
```command
$ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address
```
```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. Connect to TiDB. You should provide option `-p` if `root` has a password. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
3. Create a new user called `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'`. Please manually replace the actual IP address of `Control Machine` private IP to `${HOST_MONITOR1_PRIVATE_IP}`. Enter the following command at the terminal prompt and receive the results shown:
```command
CREATE USER 'jack'@'${HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
```
```output
tidb> CREATE USER 'jack'@'${HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
Query OK, 0 rows affected (0.03 sec)
```
4. Create two new roles called `r_manager` and `r_staff`. Enter the following command at the terminal prompt and receive the results shown:
```command
CREATE ROLE r_manager, r_staff;
```
```output
tidb> CREATE ROLE r_manager, r_staff;
Query OK, 0 rows affected (0.02 sec)
```
5. Confirm that the new user and roles have been created by querying the `user`, `host`, and `authentication_string` columns of the `mysql.user` table. Enter the following command at the terminal prompt and receive the results shown:
```command
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.
6. View the full contents of the `r_staff` account row in the `mysql.user`. Enter the following command at the terminal prompt and receive the results shown:
```command
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
Process_priv: N
Grant_priv: N
References_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Index_priv: N
Create_user_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. Change the password for `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` to `tidb` by using the `ALTER USER` statement and then exit the session. You need to manually replace `${HOST_MONITOR1_PRIVATE_IP}` with the actual IP address. Enter the following command at the terminal prompt and receive the results shown:
```command
ALTER USER 'jack'@'${HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'tidb';
```
```output
tidb> ALTER USER 'jack'@'${HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'tidb';
Query OK, 0 rows affected (0.02 sec)
```
```command
EXIT;
```
```output
tidb> EXIT
Bye
```
8. Connect to TiDB as the `'jack'@'${HOST_MONITOR1_PRIVATE_IP}’` user with the new password, confirm that the password has been changed, and then exit the session. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -ujack -p{your_password}
```
```command
EXIT
```
```output
tidb> EXIT
Bye
```
9. Connect to TiDB as user `root`. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p{your_password}
```
10. Remove the `r_staff` role using `DROP USER` statement. Confirm that the role no longer exists. Enter the following command at the terminal prompt and receive the results shown:
```command
DROP ROLE r_staff;
```
```output
tidb> DROP ROLE r_staff;
Query OK, 0 rows affected (0.03 sec)
```
```command
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. Remove the `r_manager` role using `DROP USER` statement. Confirm that the role no longer exists. Enter the following command at the terminal prompt and receive the results shown:
```command
DROP ROLE r_manager;
```
```output
tidb> DROP ROLE r_manager;
Query OK, 0 rows affected (0.03 sec)
```
```command
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. Remove the `'jack'@'${HOST_MONITOR1_PRIVATE_IP}’` user using `DROP USER` statement. Please manually replace the actual IP address of `Control Machine` private IP to `${HOST_MONITOR1_PRIVATE_IP}`. Confirm that the account no longer exists, and then exit the session. Enter the following command at the terminal prompt and receive the results shown:
```command
DROP USER 'jack'@'${HOST_MONITOR1_PRIVATE_IP}';
```
```output
tidb> DROP USER 'jack'@'${HOST_MONITOR1_PRIVATE_IP}';
Query OK, 0 rows affected (0.02 sec)
```
```command
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)
```
```command
EXIT
```
```output
tidb> EXIT
Bye
```
## Exercise 2
### Subject: Granting Permissions
### Overview
+ In this exercise, you create a user and two roles. You grant some privileges on the `test` database to the user and roles. You run some SQL statements to determine if the user session has the required privileges to access the table data. You activated the role granted to the user to test the privileges again. You will perform all tasks on `Control Machine` in multiple terminal windows.
### Duration
+ This exercise should take you approximately 45 minutes to complete.
### Tasks
+ In terminal 1, create a table called `emp` in the `test` database. Then, insert some data into it.
+ Create a new user and two roles. One role has `SELECT` privilege on the `emp` table, and the other has `INSERT`, `UPDATE`, and `DELETE` privileges on all tables. Grant both roles to the user just created.
+ In terminal 2, connect to `TiDB` as the user created in terminal 1.
+ Try to retrieve 1 row of data from the `emp` table and 1 row of data from the `dept` tables.
+ Activate all the roles granted to the new user.
+ Try to retrieve 1 row of data from the `emp` tables.
+ Try to delete the `emp` with `id`=1 from the `emp` table.
### Solution
1. In terminal 1, connect to TiDB and change the current database to `test`. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p{your_password}
```
```command
USE test;
```
```output
tidb> USE test;
Database changed
```
2. Create a table called `emp`. The `emp` table should have an int column called `id` and a varchar(20) column called `name`. Enter the following command at the terminal prompt and receive the results shown:
```command
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. Insert 2 rows into the `emp` table. Enter the following command at the terminal prompt and receive the results shown:
```command
INSERT INTO emp VALUES(1,'tom');
```
```output
tidb> INSERT INTO emp VALUES(1,'tom');
Query OK, 1 row affected (0.01 sec)
```
```command
INSERT INTO emp VALUES(2,'jack');
```
```output
tidb> INSERT INTO emp VALUES(2,'jack');
Query OK, 1 row affected (0.01 sec)
```
4. Create a new user called `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` with the password `pingcap`. You need to manually replace `${HOST_MONITOR1_PRIVATE_IP}` with the actual IP address. Enter the following command at the terminal prompt and receive the results shown:
```command
CREATE USER 'jack'@'${HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
```
```output
tidb> CREATE USER 'jack'@'${HOST_MONITOR1_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
Query OK, 0 rows affected (0.02 sec)
```
5. Create two new roles called `r_mgr` and `r_emp`. Enter the following command at the terminal prompt and receive the results shown:
```command
CREATE ROLE r_mgr, r_emp;
```
```output
tidb> CREATE ROLE r_mgr, r_emp;
Query OK, 0 rows affected (0.03 sec)
```
6. Grant the `SELECT` privilege on the `emp` tables in the `test` database to the `r_emp` role. Enter the following command at the terminal prompt and receive the results shown:
```command
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. Grant the `INSERT`, `UPDATE`, and `DELETE` privileges on all tables in the `test` database to `r_mgr` role. Enter the following command at the terminal prompt and receive the results shown:
```command
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. Grant the `r_emp` role to `r_mgr` and `'jack'@'${HOST_MONITOR1_PRIVATE_IP}’`. Enter the following command at the terminal prompt and receive the results shown:
```command
GRANT r_emp TO r_mgr, 'jack'@'${HOST_MONITOR1_PRIVATE_IP}';
```
```output
tidb> GRANT r_emp TO r_mgr, 'jack'@'${HOST_MONITOR1_PRIVATE_IP}';
Query OK, 0 rows affected (0.03 sec)
```
9. Create a table called `dept`. The `dept` table should have an `int` column called `id` and a varchar(20) column called `dname`. Then insert 2 rows into the `dept` table. Enter the following command at the terminal prompt and receive the results shown:
```command
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)
```
```command
INSERT INTO dept VALUES(1, 'dev');
```
```output
tidb> INSERT INTO dept VALUES(1, 'dev');
Query OK, 1 row affected (0.01 sec)
```
```command
INSERT INTO dept VALUES(2, 'sales');
```
```output
tidb> INSERT INTO dept VALUES(2, 'sales');
Query OK, 1 row affected (0.01 sec)
```
10. Grant the `SELECT` privilege on the `dept` tables in the `test` database to `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` user account. Enter the following command at the terminal prompt and receive the results shown:
```command
GRANT SELECT ON test.dept TO 'jack'@'${HOST_MONITOR1_PRIVATE_IP}';
```
```output
tidb> GRANT SELECT ON test.dept TO 'jack'@'${HOST_MONITOR1_PRIVATE_IP}';
Query OK, 0 rows affected (0.02 sec)
```
11. In terminal 2, connect to `TiDB` as the `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` user. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} -P4000 -ujack -ppingcap
```
12. Change the default database to `test`. Try to retrieve 1 row of data from the `emp` table and 1 row of data from the `dept` tables. Does the user have sufficient privileges to do so? Enter the following command at the terminal prompt and receive the results shown:
```command
USE test;
```
```output
tidb> USE test;
Database changed
```
```command
SELECT * FROM emp LIMIT 1;
```
```output
tidb> SELECT * FROM emp LIMIT 1;
ERROR 1142 (42000): SELECT command denied TO user 'jack'@'${HOST_MONITOR1_PRIVATE_IP}' for TABLE 'emp'
```
```command
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'@'${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'@'${HOST_MONITOR1_PRIVATE_IP}'` has the privilege to retrieve rows from the dept table. The privilege is granted directly to the user.
13. Check what roles are activated in `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` session and show all the privileges granted to the user. Enter the following command at the terminal prompt and receive the results shown:
```command
SELECT CURRENT_ROLE();
```
```output
tidb> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
```
```command
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.
14. Activate all the roles granted to the user `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` in the connected session and check what roles have been enabled. Show all the privileges granted to the user again. Enter the following command at the terminal prompt and receive the results shown:
```command
SET ROLE ALL;
```
```output
tidb> SET ROLE ALL;
Query OK, 0 rows affected (0.00 sec)
```
```command
SELECT CURRENT_ROLE();
```
```output
tidb> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `r_emp`@`%` |
+----------------+
1 row in set (0.01 sec)
```
```command
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.
15. In terminal 2, try to retrieve 1 row of data from the `emp` tables. Does the user have sufficient privileges to do so? Enter the following command at the terminal prompt and receive the results shown:
```command
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'@'${HOST_MONITOR1_PRIVATE_IP}'` has the privilege to retrieve rows from the `emp` table after the `r_emp` role has been enabled.
16. In terminal 2, try to delete the `emp` with `id`=1 from the `emp` table. Does the user have sufficient privileges to do so? Enter the following command at the terminal prompt and receive the results shown:
```command
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'@'${HOST_MONITOR1_PRIVATE_IP}'` does not have the privilege to delete rows from the employees table.
17. Exit the clients that are logged in as `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` and `root`. Enter the following statement at the prompt logged in as `'jack'@'${HOST_MONITOR1_PRIVATE_IP}'` and `root`, and receive the results shown:
```command
EXIT
```
```output
tidb> EXIT
Bye
```
18. Close all terminal windows.
## End of Exercises