自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

使用Show Effective Grants查看權(quán)限

作者：醒嘞
數(shù)據(jù)庫 其他數(shù)據(jù)庫
根據(jù)精確匹配原則，user1可以從172.%主機連接數(shù)據(jù)庫，全局權(quán)限為N(mysql.user)，db權(quán)限匹配上user1@'%'，擁有sbtest庫的所有操作權(quán)限。

1、問題描述

用戶 show grants 顯示只有連接權(quán)限，但該用戶卻能執(zhí)行 sbtest.*下的所有操作。

GreatSQL> \s
...
Server version:  8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c
...
GreatSQL> show grants;
+---------------------------------------+
| Grants for user1@172.%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`172.%` |
+---------------------------------------+
1 row in set (0.00 sec)

GreatSQL> select * from sbtest.sbtest1 limit 1;
+----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k   | c                                                                                                                       | pad                                                         |
+----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 250 | 50739423477-59896895752-91121550334-25071371310-03454727381-25307272676-12883025003-48844794346-97662793974-67443907837 | 10824941535-62754685647-36430831520-45812593797-70371571680 |
+----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

2、官方文檔

MySQL 官方手冊，有這樣一段話

https://dev.mysql.com/doc/refman/8.0/en/show-grants.htmlSHOW 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.htmlIn 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 的權(quán)限，但 SHOW GRANTS 不會顯示這部分權(quán)限。在 Percona Server 可以通過 SHOW EFFECTIVE GRANTS 查看。

3、測試驗證

3.1、同 user 用戶

1)創(chuàng)建用戶并授權(quán)

# 創(chuàng)建用戶
GreatSQL> CREATE USER grantee@localhost IDENTIFIED BY 'grantee1';
Query OK, 0 rows affected (0.05 sec)

GreatSQL> CREATE USER grantee@'%' IDENTIFIED BY 'grantee2';
Query OK, 0 rows affected (0.01 sec)

# 創(chuàng)建數(shù)據(jù)庫
GreatSQL> CREATE DATABASE IF NOT EXISTS sbtest;
Query OK, 1 row affected, 1 warning (0.00 sec)

GreatSQL> CREATE DATABASE IF NOT EXISTS sbtest1;
Query OK, 1 row affected (0.05 sec)

# 授權(quán)
GreatSQL> GRANT ALL PRIVILEGES ON sbtest.* TO grantee@'%';
Query OK, 0 rows affected (0.02 sec)

2)查看權(quán)限

GreatSQL> show grants for grantee@localhost;
+---------------------------------------------+
| Grants for grantee@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`localhost` |
+---------------------------------------------+
1 row in set (0.01 sec)

權(quán)限列表沒有顯示 grantee@localhost 對 sbtest 庫的權(quán)限，但實際 grantee@localhost 已經(jīng)擁有 sbtest 庫下所有操作權(quán)限

3)grantee@localhost 登錄，執(zhí)行操作

GreatSQL> show grants;
+---------------------------------------------+
| Grants for grantee@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`localhost` |
+---------------------------------------------+
1 row in set (0.00 sec)

GreatSQL> create table sbtest.t1(id int primary key);
Query OK, 0 rows affected (0.04 sec)

GreatSQL> insert into sbtest.t1 select 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

4)使用 SHOW EFFECTIVE GRANTS 查看權(quán)限

GreatSQL> show effective grants;
+-------------------------------------------------------------+
| Effective grants for grantee@localhost                      |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `sbtest`.* TO `grantee`@`localhost` |
+-------------------------------------------------------------+
2 rows in set (0.01 sec)

SHOW EFFECTIVE GRANTS顯示出擁有的同 user 用戶權(quán)限

3.2、匿名用戶

匿名用戶請參考：https://dev.mysql.com/doc/refman/8.0/en/connection-access.html

1)創(chuàng)建匿名用戶并授權(quán)

# 未指定host，默認為%
GreatSQL> CREATE USER '';
Query OK, 0 rows affected (0.04 sec)

GreatSQL> GRANT ALL ON sbtest1.* TO '';
Query OK, 0 rows affected (0.02 sec)

2)查看權(quán)限

GreatSQL> show grants for grantee@localhost;
+---------------------------------------------+
| Grants for grantee@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`localhost` |
+---------------------------------------------+
1 row in set (0.01 sec)

權(quán)限列表沒有顯示 grantee@localhost 對 sbtest1 庫的權(quán)限，但實際 grantee@localhost 已經(jīng)擁有 sbtest1 庫下所有操作權(quán)限

3)grantee@localhost 登錄，執(zhí)行操作

GreatSQL> select user(), current_user();
+-------------------+-------------------+
| user()            | current_user()    |
+-------------------+-------------------+
| grantee@localhost | grantee@localhost |
+-------------------+-------------------+
1 row in set (0.00 sec)

GreatSQL> show grants;
+---------------------------------------------+
| Grants for grantee@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`localhost` |
+---------------------------------------------+
1 row in set (0.00 sec)

GreatSQL> create table sbtest1.t2(id int primary key);
Query OK, 0 rows affected (0.03 sec)

GreatSQL> insert into sbtest1.t2 select 2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

4)使用 SHOW EFFECTIVE GRANTS 查看權(quán)限

GreatSQL> show effective grants;
+-------------------------------------------------------------+
| Effective grants for grantee@localhost                      |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `grantee`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `sbtest`.* TO `grantee`@`localhost` |
+-------------------------------------------------------------+
2 rows in set (0.01 sec)

注意：SHOW EFFECTIVE GRANTS沒有顯示出擁有的匿名用戶權(quán)限，sbtest.*是擁有的同 user 用戶權(quán)限

4、建議

1)使用 SHOW EFFECTIVE GRANTS 代替 SHOW GRANTS(GreatDB、GreatSQL、Percona Server)

GreatSQL> show effective grants for user1@`172.%`;
+-------------------------------------------------------+
| Effective grants for user1@172.%                      |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`172.%`                 |
| GRANT ALL PRIVILEGES ON `sbtest`.* TO `user1`@`172.%` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

2)賬號加固

  • 匿名用戶，禁止匿名用戶登錄
GreatSQL> select user, host from mysql.user where user='';
+------+------+
| user | host |
+------+------+
|      | %    |
+------+------+
1 row in set (0.02 sec)
  • 同 user 不同 host
GreatSQL> select u.user, u.host, p.user priv_user, p.host priv_host from (
    -> select user, host from mysql.db
    -> union
    -> select user, host from mysql.tables_priv
    -> union
    -> select user, host from mysql.columns_priv) p
    -> left join mysql.user u on p.user=u.user 
    -> where p.host<>u.host;
+---------+-----------+-----------+-----------+
| user    | host      | priv_user | priv_host |
+---------+-----------+-----------+-----------+
| user1   | 172.%     | user1     | %         |
| grantee | localhost | grantee   | %         |
+---------+-----------+-----------+-----------+
2 rows in set (0.01 sec)

到各權(quán)限表查看對應user信息，核實權(quán)限'錯亂'的原因

GreatSQL> select * from mysql.user where user='user1'\G
*************************** 1. row ***************************
                    Host: 172.%
                    User: user1
             Select_priv: N
             ...
1 row in set (0.05 sec)

GreatSQL> select * from mysql.db where user='user1'\G
*************************** 1. row ***************************
                 Host: %
                   Db: sbtest
                 User: user1
          Select_priv: Y
          ...
1 row in set (0.01 sec)

user 表只有 user1@'172.%'，db 表只有 user1@'%'，對應算兩個用戶。

可能是手動更新過權(quán)限表：例如創(chuàng)建用戶xx@'%'，授權(quán)db.*所有權(quán)限，后來更新mysql.user表中的記錄為xx@'172.%'限制登錄來源。 

根據(jù)精確匹配原則，user1可以從172.%主機連接數(shù)據(jù)庫，全局權(quán)限為N(mysql.user)，db權(quán)限匹配上user1@'%'，擁有sbtest庫的所有操作權(quán)限。

責任編輯：武曉燕 來源： GreatSQL社區(qū)
權(quán)限數(shù)據(jù)庫sbtest庫
相關(guān)推薦

2010-10-28 11:24:05

oracle用戶權(quán)限

2023-06-15 11:59:05

2021-02-07 09:22:42

Zabbix5.2拓撲圖運維

2022-01-12 11:40:05

Effective C語言編譯器

2009-10-20 14:12:00

CCIE Lab

2010-10-12 16:35:05

MySQL用戶權(quán)限

2010-07-09 12:39:28

SQL Server超

2011-08-24 18:05:31

SHOW中文man

2012-01-13 12:57:48

Java

2019-11-26 10:34:13

Linuxsudo權(quán)限命令

2010-07-09 12:22:42

SQL Server超

2009-09-11 10:59:06

Effective C調(diào)用Dispose()

2010-05-10 14:04:01

Oracle系統(tǒng)用戶權(quán)

2010-10-15 11:16:52

MySQL Show語

2022-06-20 15:33:54

FlatsealFlatpak

2011-03-25 10:43:31

Oracle系統(tǒng)用戶權(quán)限賦予

2010-05-20 11:25:14

IIS服務器

2015-06-29 15:12:09

ORACLE賬號權(quán)限用戶權(quán)限

2010-06-18 13:36:49

SQL Server查

2024-04-11 13:27:19

Linuxtop命令
點贊
收藏

51CTO技術(shù)棧公眾號