MySQL 账户管理

原创

已于 2024-07-03 14:21:53 修改 · 787 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #database

于 2021-12-29 21:03:34 首次发布

MySQL 账户户管理

MySQL权限处理原则
用户管理
MySQL 8.0用户管理增强
- 密码过期
- 双密码

MySQL权限处理原则

权限，通常都是用户A拥有对象B的权限。在Mysql中，加了一个维度，从哪儿来？即来源主机。
权限认证的目的，都是为了让用户只能做允许他做的事情。MySQL采用的是白名单的权限策略。也就是说，明确指定了哪些用户能够做什么，但没法明确的指定某些用户不能做什么，对权限的验证主要是通过mysql库下的几个数据字典表，来实现不同粒度的权限需求。
权限的处理逻辑，MySQL在检查用户连接时可以分为两个阶段：
1）能不能连接。
在其他大型数据库中，直接指定用户即可登录数据库，但在MySQL数据库中，则额外还需要有主机这一维度，用户和主机(‘user’@‘host’)组成一个唯一账户，登录MySQL数据库时，实际上是通过账户进行验证。
host：IP地址，主机名，或者可以被正确解析至IP地址的域名，支持通配符。
host可以指定为通配符，有2中：1）%：对应任意长度的任意字符；2）_:对应一位长度的任意字符。
host为空或%，表示任意主机。
可以有如下形式：

localhost
www.enmo.com
10.1.11.30
10.1.11.% 或 10.1.11._
10.1.11.0/255.255.255.0

user：建议少于10个字符，不限制使用特殊字符。也可以为空。表示任意用户，随便输一个用户名，均可登录。
所以在MySQL里面，不再通过用户名来确认唯一一条记录，而是通过’user’@'host’来确认记录是否唯一。user表中每一条记录都是一个独立的账户，每一个独立的账户都可以拥有各自的权限设置。
匹配原则：精确匹配。最明确的值优先匹配。

MySQL5.7之后，user字典表中的password字段用authentication_string代替。
所以唯一确定一行记录，可以用下面的查看：

root@postgre 11:28:  [(none)]> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| test1            | %         |                                                                        |
| test3            | %         | $A$005$=/qZ{
   
   p`rOCdlk@%8gwPEVnL4MRENc6wfwdyXOVhfPGvIl2Zad3Uvr5NV13. |
| test2            | 10.1.11.% | $A$005$u
                                         A0Z!QnZ%[5   UR6uWrKcIMxG2cAm5qEEzsqzt6PNPiV00rxK9H35zT5eYy5 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
| enmo_app         | zhuo%.com |                                                                        |
+------------------+-----------+------------------------------------------------------------------------+
8 rows in set (0.00 sec)

user名字，host来源，authentication_string密码。密码和user都可以为空，表示登录不用密码，随便哪个用户等可以登录。
验证时，需要检查3项值：user字典表中user，host，authentication_string。
2）能不能执行操作
连接数据库成功后，能不能执行操作。就是要授权，授权的原则就是按照粒度。

查看没有密码的用户：

root@postgre 22:46:  [(none)]> select user,host from mysql.user where authentication_string='';
+----------+-----------+
| user     | host      |
+----------+-----------+
| test1    | %         |
| root     | localhost |

用户管理

创建用户

用户和主机(‘user’@‘host’)组成一个唯一账户
在这里插入图片描述
用户连接匹配原则：精确匹配原则。

最简单的用户创建，无密码，无主机来源。

root@postgre 21:04:  [(none)]> create user test1;
Query OK, 0 rows affected (0.06 sec)

root@postgre 21:04:  [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test1            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

登录测试，随便哪个客户端，不用密码都可以登录：

[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h 10.1.11.30 -P3306 -utest1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.27 MySQL Community Server - GPL

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> select user(),current_user();
+------------------+----------------+
| user()           | current_user() |
+------------------+----------------+
| [email protected] | test1@%        |
+------------------+----------------+
1 row in set (0.00 sec)

创建本地test2用户，只能本地登录。

root@postgre 21:05:  [(none)]> create user test2@localhost identified by 'test2';
Query OK, 0 rows affected (0.04 sec)

root@postgre 21:05:  [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test1            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| test2            | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

修改密码

test3@postgre 22:26:  [(none)]> alter user test3 identified by 'mysql';
Query OK, 0 rows affected (0.04 sec)

alter user ‘root’@‘%’ identified by ‘xxxx’;
alter user ‘root’@‘localhost’ identified by ‘xxxx’;

查看权限

show grants
查看帮助

root@postgre 22:31:  [(none)]> help show grants
Name: 'SHOW GRANTS'
Description:
Syntax:
SHOW GRANTS
    [FOR user_or_role
        [USING role [, role] ...]]

user_or_role: {
   
   
    user (see )
  | role (see .
}
。。。
mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';
+------------------------------------------------------------------+
| Grants for jeffrey@localhost                                     |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`localhost`                      |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |
+------------------------------------------------------------------+

The host part, if omitted, defaults to '%'. For additional information
about specifying account and role names, see
https://dev.mysql.com/doc/refman/8.0/en/account-names.html, and
https://dev.mysql.com/doc/refman/8.0/en/role-names.html.

To display the privileges granted to the current user (the account you
are using to connect to the server), you can use any of the following
statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
。。。。

查看当前登录用户的权限，有以下三种：

test1@postgre 22:34:  [(none)]> select user(),current_user();
+-----------------+----------------+
| user()          | current_user() |
+-----------------+----------------+
| test1@localhost | test1@%        |
+-----------------+----------------+
1 row in set (0.00 sec)

test1@postgre 22:34:  [(none)]> SHOW GRANTS;
+-----------------------------------+
| Grants for test1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

test1@postgre 22:34:  [(none)]> SHOW GRANTS FOR CURRENT_USER;
+-----------------------------------+
| Grants for test1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

test1@postgre 22:34:  [(none)]> SHOW GRANTS FOR CURRENT_USER();
+-----------------------------------+
| Grants for test1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

查看非当前用户：

root@postgre 22:33:  [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test1            | %         |
| test2            | 10.1.11.% |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
root@postgre 22:36:  [(none)]> show grants for 'test2'@'10.1.11.%';
+-------------------------------------------+
| Grants for [email protected].%                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `test2`@`10.1.11.%` |
+-------------------------------------------+
1 row in set (0.00 sec)

删除用户

删除用户要精确，用户名@来源
root@postgre 21:10: [(none)]> drop user test2;
ERROR 1396 (HY000): Operation DROP USER failed for ‘test2’@‘%’
从日志都都可以看出，不带来源，删除的是’test2’@'%'账号，而这个账号我们库里并没有。

如果我们不知道库里有哪些账号，可以查询user表。

root@postgre 21:11:  [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test1            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| test2            | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)

root@postgre 21:17:  [(none)]> drop user test2@localhost;
Query OK, 0 rows affected (0.04 sec)

root@postgre 21:18:  [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test1            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.01 sec)

客户端连接并没有实际断开，之前连接进来的客户端，依然还能够进行查询。

test2@postgre 13:32:  [(none)]> show tables;
ERROR 1046 (3D000): No database selected
test2@postgre 13:33:  [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

test2@postgre 13:33:  [(none)]> select user(),current_user();
+-----------------+-----------------+
| user()          | current_user()  |
+-----------------+-----------------+
| test2@localhost | test2@localhost |
+-----------------+-----------------+
1 row in set (0.00 sec)

他的权限是固定在THD中。句柄里的。如果退出之前的已有连接，就连不上了。

[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -utest2 -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test2'@'localhost' (using password: YES)

drop user不会自动中止已连接的用户会话，也就是说被删的用户如果在删前已经连接上了服务器，并且连接尚未中断，那它此时还能继续执行一定的操作，只是他的身份已经变成了黑户。

利用精确匹配拒绝某台应用连接

create user test2@‘10.1.11.%’ identified by ‘mysql’;
grant all privileges on . to test2@‘10.1.11.%’;
create user test2@‘10.1.11.10’ identified by ‘oracle’;

创建模糊匹配用户并授权：

root@postgre 10:47:  [(none)]> create user test2@'10.1.11.%' identified by 'mysql';
Query OK, 0 rows affected (0.03 sec)c)
root@postgre 13:04:  [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test1            | %         |
| test2            | 10.1.11.% |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
root@postgre 22:43:  [(none)]> grant all privileges on *.* to test2@'10.1.11.%'