发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

使用show effective grants查看权限

金越成金属科技苏州有限公司

12

主题

12

帖子

36

积分

新手上路

Rank: 1

积分
36
显示全部楼层
1、问题描述

用户 show grants 显示只有连接权限，但该用户却能执行 sbtest.*下的所有操作
  1. GreatSQL> \s
  2. ...
  3. Server version:  8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c
  4. ...
  5. GreatSQL> show grants;
  6. +---------------------------------------+
  7. | Grants for user1@172.%                |
  8. +---------------------------------------+
  9. | GRANT USAGE ON *.* TO `user1`@`172.%` |
  10. +---------------------------------------+
  11. 1 row in set (0.00 sec)
  13. GreatSQL> select * from sbtest.sbtest1 limit 1;
  14. +----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
  15. | id | k   | c                                                                                                                       | pad                                                         |
  16. +----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
  17. |  1 | 250 | 50739423477-59896895752-91121550334-25071371310-03454727381-25307272676-12883025003-48844794346-97662793974-67443907837 | 10824941535-62754685647-36430831520-45812593797-70371571680 |
  18. +----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
  19. 1 row in set (0.00 sec)
复制代码
2、官方文档

MySQL 官方手册，有这样一段话
https://dev.mysql.com/doc/refman/8.0/en/show-grants.html
SHOW GRANTS does not display privileges that are available to the named account but are granted to a different account. For example, if an anonymous account exists, the named account might be able to use its privileges, but SHOW GRANTS does not display them.
Percona Server 官方手册，有类似一段话
https://docs.percona.com/percona-server/8.0/management/extended_show_grants.html
In Oracle MySQL SHOW GRANTS displays only the privileges granted explicitly to the named account. Other privileges might be available to the account, but they are not displayed. For example, if an anonymous account exists, the named account might be able to use its privileges, but SHOW GRANTS will not display them. Percona Server for MySQL offers the SHOW EFFECTIVE GRANTS command to display all the effectively available privileges to the account, including those granted to a different account.
概括如下：

  • 用户 A 的 user 与用户 B 的 user 相同，或者用户 A 是匿名用户
  • 用户 B 的 host 范围是用户 A 的 host 范围的子集
满足上述两个条件，此时用户 B 拥有显式授予给用户 A 的权限，但 SHOW GRANTS 不会显示这部分权限。在 Percona Server 可以通过 SHOW EFFECTIVE GRANTS 查看。
3、测试验证

3.1、同 user 用户

1)、创建用户并授权
  1. # 创建用户
  2. GreatSQL> CREATE USER grantee@localhost IDENTIFIED BY 'grantee1';
  3. Query OK, 0 rows affected (0.05 sec)
  5. GreatSQL> CREATE USER grantee@'%' IDENTIFIED BY 'grantee2';
  6. Query OK, 0 rows affected (0.01 sec)
  8. # 创建数据库
  9. GreatSQL> CREATE DATABASE IF NOT EXISTS sbtest;
  10. Query OK, 1 row affected, 1 warning (0.00 sec)
  12. GreatSQL> CREATE DATABASE IF NOT EXISTS sbtest1;
  13. Query OK, 1 row affected (0.05 sec)
  15. # 授权
  16. GreatSQL> GRANT ALL PRIVILEGES ON sbtest.* TO grantee@'%';
  17. Query OK, 0 rows affected (0.02 sec)
复制代码
2)、查看权限
  1. GreatSQL> show grants for grantee@localhost;
  2. +---------------------------------------------+
  3. | Grants for grantee@localhost                |
  4. +---------------------------------------------+
  5. | GRANT USAGE ON *.* TO `grantee`@`localhost` |
  6. +---------------------------------------------+
  7. 1 row in set (0.01 sec)
复制代码
权限列表没有显示 grantee@localhost 对 sbtest 库的权限，但实际 grantee@localhost 已经拥有 sbtest 库下所有操作权限
3)、grantee@localhost 登录，执行操作
  1. GreatSQL> show grants;
  2. +---------------------------------------------+
  3. | Grants for grantee@localhost                |
  4. +---------------------------------------------+
  5. | GRANT USAGE ON *.* TO `grantee`@`localhost` |
  6. +---------------------------------------------+
  7. 1 row in set (0.00 sec)
  9. GreatSQL> create table sbtest.t1(id int primary key);
  10. Query OK, 0 rows affected (0.04 sec)
  12. GreatSQL> insert into sbtest.t1 select 1;
  13. Query OK, 1 row affected (0.01 sec)
  14. Records: 1  Duplicates: 0  Warnings: 0
复制代码
4)、使用 SHOW EFFECTIVE GRANTS 查看权限
  1. GreatSQL> show effective grants;
  2. +-------------------------------------------------------------+
  3. | Effective grants for grantee@localhost                      |
  4. +-------------------------------------------------------------+
  5. | GRANT USAGE ON *.* TO `grantee`@`localhost`                 |
  6. | GRANT ALL PRIVILEGES ON `sbtest`.* TO `grantee`@`localhost` |
  7. +-------------------------------------------------------------+
  8. 2 rows in set (0.01 sec)
复制代码
SHOW EFFECTIVE GRANTS****显示出拥有的同 user 用户权限
3.2、匿名用户

匿名用户请参考：https://dev.mysql.com/doc/refman/8.0/en/connection-access.html
1)、创建匿名用户并授权
  1. # 未指定host，默认为%
  2. GreatSQL> CREATE USER '';
  3. Query OK, 0 rows affected (0.04 sec)
  5. GreatSQL> GRANT ALL ON sbtest1.* TO '';
  6. Query OK, 0 rows affected (0.02 sec)
复制代码
2)、查看权限
  1. GreatSQL> show grants for grantee@localhost;
  2. +---------------------------------------------+
  3. | Grants for grantee@localhost                |
  4. +---------------------------------------------+
  5. | GRANT USAGE ON *.* TO `grantee`@`localhost` |
  6. +---------------------------------------------+
  7. 1 row in set (0.01 sec)
复制代码
权限列表没有显示 grantee@localhost 对 sbtest1 库的权限，但实际 grantee@localhost 已经拥有 sbtest1 库下所有操作权限
3)、grantee@localhost 登录，执行操作
  1. GreatSQL> select user(), current_user();
  2. +-------------------+-------------------+
  3. | user()            | current_user()    |
  4. +-------------------+-------------------+
  5. | grantee@localhost | grantee@localhost |
  6. +-------------------+-------------------+
  7. 1 row in set (0.00 sec)
  9. GreatSQL> show grants;
  10. +---------------------------------------------+
  11. | Grants for grantee@localhost                |
  12. +---------------------------------------------+
  13. | GRANT USAGE ON *.* TO `grantee`@`localhost` |
  14. +---------------------------------------------+
  15. 1 row in set (0.00 sec)
  17. GreatSQL> create table sbtest1.t2(id int primary key);
  18. Query OK, 0 rows affected (0.03 sec)
  20. GreatSQL> insert into sbtest1.t2 select 2;
  21. Query OK, 1 row affected (0.01 sec)
  22. Records: 1  Duplicates: 0  Warnings: 0
复制代码
4)、使用 SHOW EFFECTIVE GRANTS 查看权限
  1. GreatSQL> show effective grants;
  2. +-------------------------------------------------------------+
  3. | Effective grants for grantee@localhost                      |
  4. +-------------------------------------------------------------+
  5. | GRANT USAGE ON *.* TO `grantee`@`localhost`                 |
  6. | GRANT ALL PRIVILEGES ON `sbtest`.* TO `grantee`@`localhost` |
  7. +-------------------------------------------------------------+
  8. 2 rows in set (0.01 sec)
复制代码
注意：****SHOW EFFECTIVE GRANTS没有显示出拥有的匿名用户权限，sbtest.*是拥有的同 user 用户权限
4、建议

1)、使用 SHOW EFFECTIVE GRANTS 代替 SHOW GRANTS(GreatDB、GreatSQL、Percona Server)
  1. GreatSQL> show effective grants for user1@`172.%`;
  2. +-------------------------------------------------------+
  3. | Effective grants for user1@172.%                      |
  4. +-------------------------------------------------------+
  5. | GRANT USAGE ON *.* TO `user1`@`172.%`                 |
  6. | GRANT ALL PRIVILEGES ON `sbtest`.* TO `user1`@`172.%` |
  7. +-------------------------------------------------------+
  8. 2 rows in set (0.00 sec)
复制代码
2)、账号加固

  • 匿名用户，禁止匿名用户登录
  1. GreatSQL> select user, host from mysql.user where user='';
  2. +------+------+
  3. | user | host |
  4. +------+------+
  5. |      | %    |
  6. +------+------+
  7. 1 row in set (0.02 sec)
复制代码

  • 同 user 不同 host
  1. GreatSQL> select u.user, u.host, p.user priv_user, p.host priv_host from (
  2.     -> select user, host from mysql.db
  3.     -> union
  4.     -> select user, host from mysql.tables_priv
  5.     -> union
  6.     -> select user, host from mysql.columns_priv) p
  7.     -> left join mysql.user u on p.user=u.user
  8.     -> where p.host<>u.host;
  9. +---------+-----------+-----------+-----------+
  10. | user    | host      | priv_user | priv_host |
  11. +---------+-----------+-----------+-----------+
  12. | user1   | 172.%     | user1     | %         |
  13. | grantee | localhost | grantee   | %         |
  14. +---------+-----------+-----------+-----------+
  15. 2 rows in set (0.01 sec)
复制代码
到各权限表查看对应user信息，核实权限'错乱'的原因
  1. GreatSQL> select * from mysql.user where user='user1'\G
  2. *************************** 1. row ***************************
  3.                     Host: 172.%
  4.                     User: user1
  5.              Select_priv: N
  6.              ...
  7. 1 row in set (0.05 sec)
  9. GreatSQL> select * from mysql.db where user='user1'\G
  10. *************************** 1. row ***************************
  11.                  Host: %
  12.                    Db: sbtest
  13.                  User: user1
  14.           Select_priv: Y
  15.           ...
  16. 1 row in set (0.01 sec)
复制代码
user 表只有 user1@'172.%'，db 表只有 user1@'%'，对应算两个用户。
可能是手动更新过权限表：例如创建用户xx@'%'，授权db.*所有权限，后来更新mysql.user表中的记录为xx@'172.%'限制登录来源。
根据精确匹配原则，user1可以从172.%主机连接数据库，全局权限为N(mysql.user)，db权限匹配上user1@'%'，拥有sbtest库的所有操作权限。

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/17581628.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: 使用show effective grants查看权限

下一篇: MySQL存储过程

发表于 2023-7-26 17:44:50

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4