015、数据库管理之用户和权限

认证与赋权

• 认证：对用户进行验证
  1. 是权限控制的第一步
  2. 当用户第一次连接数据库时必须进行认证
  3. 如果认证失败则无法连接数据库
• 授权： 对用户的权限进行验证
  1. 这个权限控制的第二步
  2. TiDB 将会决定用户是否有权限进行想做的操作。

连接过程

本地连接

使用mysql客户端从本地服务器连接TiDB数据库，需要执行用户名和密码

mysql -uusername -ppassword -hlocalhost -Pport

远程连接

使用mysql客户端从远程机器连接TiDB数据库，需要执行用户名和密码

mysql -uusername -ppassword -hip -Pport

查看用户信息

mysql> select user,host,authentication_string from mysql.user;
+------+------+-----------------------+
| user | host | authentication_string |
+------+------+-----------------------+
| root | %    |                       |
+------+------+-----------------------+
1 row in set (0.00 sec)

创建用户账号

注意：用户名和密码都会大小写敏感

create user 'test'@'127.0.0.1' identifid by '1234';
create user 'test';

创建角色

创建2个角色r_admin和r_dev@localhost:
角色名，大小写敏感
需要带上客户端主机名和IP地址
如果主机名被忽略则默认是’%’
用户与角色差异：

1. 是被锁住的
2. 没有密码
3. 被存储在mysql.user表中
4. 用户登录后，必须使用set role all命令开启用户被赋予的角色。

管理用户账户

mysql> revoke all privileges on *.* from 'user1'@'localhost';
Query OK, 0 rows affected (0.10 sec)

mysql> rename user 'test'@'localhost' to 'user1'@'localhost';
Query OK, 0 rows affected (0.14 sec)

mysql> grant all privileges on *.* to 'user1'@'localhost' with grant option;
Query OK, 0 rows affected (0.12 sec)

mysql> drop user 'user1'@'localhost';
Query OK, 0 rows affected (0.15 sec)

管理角色

1、 赋予角色权限:
grant select on 'test'.* to 'r_dev'@'localhost';
2、将角色授予用户
grant 'r_admin' to 'user1'@'localhost';
3、查看角色拥有的权限
show grants for 'dev'@'localhost';

4、回收角色权限
revoke insert,update,delete on 'test'.'*' from 'r_dev'@'localhost';

5、删除角色
drop role 'r_admin'@'localhost';

设置账号密码

1、create user 创建密码
CREATE USER 'TEST'@'localhost' identified by 'mypass'
2、为存在的帐号修改密码
set password for 'test'@'localhost' = 'mypasswd'
alter user 'test'@'localhost' identified by 'mypasswd'

忘记root密码

1、修改配置文件
[security]
skip-grant-table = ture
2、重启数据库后生效
mysql -uroot -p -P 4000

实验1-用户和角色

1、创建用户和角色
[root@tidb ~]# mysql --host 192.168.16.12 --port 4000 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 415
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2023, 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> create user 'hulk'@'192.168.16.12' identified by 'pingcap';
Query OK, 0 rows affected (0.12 sec)

mysql> create role r_manager,r_staff;
Query OK, 0 rows affected (0.11 sec)


2、 查看用户和角色
用户和角色都被存储到mysql.user 表中。
角色是没有密码的

角色是被锁定的
角色没有密码


mysql> select user,host,authentication_string from mysql.user \G;
*************************** 1. row ***************************
                 user: root
                 host: %
authentication_string: 
*************************** 2. row ***************************
                 user: hulk
                 host: 192.168.16.12
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)

ERROR: 
No query specified

mysql> 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.03 sec)

ERROR: 
No query specified

mysql> alter user 'hulk'@'192.168.16.12' identified by 'tidb';
Query OK, 0 rows affected (0.10 sec)

mysql> exit
Bye
[root@tidb ~]# mysql -h192.168.16.12 -P 4000 -uhulk -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 417
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2023, 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> exit
Bye
[root@tidb ~]# mysql --host 192.168.16.12 --port 4000 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 419
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2023, 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.


3、删除用户和角色
mysql> drop role t_staff;
ERROR 1396 (HY000): Operation DROP ROLE failed for t_staff@%
mysql> drop role r_staff;
Query OK, 0 rows affected (0.13 sec)

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

mysql> drop user 'hulk'@'192.168.16.12';
Query OK, 0 rows affected (0.14 sec)

mysql> 

实验2-授权注意事项

创建对象
mysql> create table emp(id int,name varchar(20));
Query OK, 0 rows affected (0.15 sec)

mysql> insert into emp values(1,'tom');
Query OK, 1 row affected (0.03 sec)

mysql> insert into emp values(2,'jack');
Query OK, 1 row affected (0.02 sec)

创建用户
mysql> create user 'hulk'@'192.168.16.12' identified by 'pingcap';
Query OK, 0 rows affected (0.22 sec)

创建角色
mysql> create role r_mgr,r_emp;
Query OK, 0 rows affected (0.09 sec)

mysql> grant select on test.emp to r_emp;
Query OK, 0 rows affected (0.15 sec)

授予权限
mysql> grant insert ,update,delete on test.* to r_mgr;
Query OK, 0 rows affected (0.10 sec)

授予角色，注意不是立即生效
mysql> grant r_emp to r_mgr,'hulk'@'192.168.16.12';
Query OK, 0 rows affected (0.11 sec)

mysql> 


mysql> create table dept(id int ,dname varchar(20));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into dept values(1,'dev');
Query OK, 1 row affected (0.08 sec)

mysql> insert into dept values(2,'sales');
Query OK, 1 row affected (0.04 sec)

mysql> grant select on test.dept to 'hulk'@'192.168.16.12';
Query OK, 0 rows affected (0.34 sec)

指定用户登录
[root@tidb ~]# mysql --host 192.168.16.12 -P4000 -uhulk -ppingcap
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 409
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2023, 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> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
角色包含的权限没有生效
mysql> select * from emp;
ERROR 1142 (42000): SELECT command denied to user 'hulk'@'192.168.16.12' for table 'emp'
mysql> select * from dept;
+------+-------+
| id   | dname |
+------+-------+
|    1 | dev   |
|    2 | sales |
+------+-------+
2 rows in set (0.02 sec)

• 用户’hulk’@‘192.168.16.12’ 无法查询emp，因为权限是通过角色r_emp赋予的，但这个角色并没有在会话总开启。
• 用户’hulk’@‘192.168.16.12’ 可以查询表dept，因为权限是直接赋予用户的。

----查看当前角色并没有
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.09 sec)

mysql> show grants;
+-----------------------------------------------------+
| Grants for User                                     |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'hulk'@'192.168.16.12'        |
| GRANT SELECT ON test.dept TO 'hulk'@'192.168.16.12' |
| GRANT 'r_emp'@'%' TO 'hulk'@'192.168.16.12'         |
+-----------------------------------------------------+
3 rows in set (0.00 sec)

--- 角色生效
mysql> set role roll;
ERROR 3530 (HY000): `roll`@`%` is not granted to hulk@192.168.16.12
mysql> set role all;
Query OK, 0 rows affected (0.02 sec)

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `r_emp`@`%`    |
+----------------+
1 row in set (0.01 sec)

mysql> show grants;
+-----------------------------------------------------+
| Grants for User                                     |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'hulk'@'192.168.16.12'        |
| GRANT SELECT ON test.dept TO 'hulk'@'192.168.16.12' |
| GRANT SELECT ON test.emp TO 'hulk'@'192.168.16.12'  |
| GRANT 'r_emp'@'%' TO 'hulk'@'192.168.16.12'         |
+-----------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
|    2 | jack |
+------+------+
2 rows in set (0.02 sec)