0098.T TiDB创建用户和角色

rundba 2021-11-18

1332

在这个练习中，您将会创建用户并为用户设置密码，之后创建2 个角色，最后将用户和角色删除。

1. 启动一个mysql 客户端会话

[root@tidb1 ~]# mysql -h192.168.80.141 -P4000 -uroot -p

2. 创建用户名为'jack'@'192.168.80.141'

mysql> create user'jack'@'192.168.80.141' identified by 'pingcap';

3. 创建2个新的角色，名为r_manager 和r_staff

mysql> create role r_manager, r_staff;

4. 查询mysql.user 表的user, host 和authentication_string 列，确认新的用户和角色已经被创建了

mysql> select user, host, authentication_string from mysql.user\G;
*************************** 1. row ***************************
                 user: root
                 host: %
authentication_string: 
*************************** 2. row ***************************
                 user: jack
                 host: 192.168.80.141
authentication_string: *926E4B88EB93FD344DF0870EE025D6EB153C02DE
*************************** 3. row ***************************
                 user: r_manager
                 host: %
authentication_string: 
*************************** 4. row ***************************
                 user: r_staff
                 host: %
authentication_string: 
4 rows in set (0.01 sec)


ERROR: 
No query specified

这里我们可以注意到:

(1)用户和角色都被存储在mysql.user 表中。

(2)角色是没有密码的。

5. 在mysql.user 表中查询角色r_staff 的详细信息

mysql> select * from mysql.user where user='r_staff'\G;
*************************** 1. row ***************************
                  Host: %
                  User: r_staff
 authentication_string: 
           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
          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
       Repl_slave_priv: N
      Repl_client_priv: N
1 row in set (0.01 sec)
ERROR: 
No query specified

我们会发现角色的特点：

（1）角色是被锁定的（Account_locked: Y）

（2）角色没有密码（authentication_string 为空）

6. 修改'jack'@'192.168.80.141'的密码为tidb,并退出

mysql> alter user 'jack'@'192.168.80.141' identified by 'tidb';

7. 使用用户'jack'@'192.168.80.141’的新密码重新连接，验证密码是否修改成功

（请根据自己实验环境输入密码）

[root@tidb1 ~]# mysql -h192.168.80.141 -P 4000 -ujack -ptidb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.25-TiDB-v5.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible


Copyright (c) 2000, 2021, Oracle and/or its affiliates.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>

8. 退出当前会话

mysql> Exit;
Bye

9. 以root 用户登录数据库

[root@tidb1 ~]# mysql -h192.168.80.141 -P 4000 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.25-TiDB-v5.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible


Copyright (c) 2000, 2021, Oracle and/or its affiliates.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show processlist;

10. 删除角色r_staff, 并且通过mysql.user表进行确认

mysql> drop role r_staff;
Query OK, 0 rows affected (0.04 sec)


mysql> select user,host from mysql.user where user='r_staff';
Empty set (0.01 sec)

11. 删除角色r_manager, 并且通过mysql.user 表进行确认

mysql> drop role r_manager;
Query OK, 0 rows affected (0.06 sec)


mysql> select user,host from mysql.user where user='r_manager';
Empty set (0.00 sec)

12. 删除用户'jack'@'192.168.80.141’，并且通过mysql.user 表进行确认

mysql> drop user 'jack'@'192.168.80.141';
Query OK, 0 rows affected (0.05 sec)


mysql> select user,host from mysql.user where user='jack';
Empty set (0.00 sec)

长按二维码

加入>>国产DB学习交流群

请注明:来自rundba，申请加入国产DB学习交流群

tidb

文章转载自rundba，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

0098.T TiDB创建用户和角色

评论