引言

MySQL支持表级甚至是字段级权限管理，授予部分账户部分权限。

首先创建用户

create user 'query01'@'%' identified with mysql_native_password by '1234qwer';

登录可以看到只能看到部分schema表。

mysql -uquery01 -p1234qwer
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 37
Server version: 8.0.43 MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.01 sec)

表授权

grant select ON test.test_ab TO 'query01'@'%';

查询库和表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_ab        |
+----------------+
1 row in set (0.01 sec)

mysql> select * from test_ab;
+-------+------+------+
| index | A    | B    |
+-------+------+------+
|     0 |    1 |    2 |
+-------+------+------+
1 row in set (0.00 sec)

字段授权

grant select(`INDEX`,A) ON test.test_ab_2 TO 'query01'@'%';

查看

mysql> select `index` from test_ab_2;
+-------+
| index |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> select * from test_ab_2;
ERROR 1142 (42000): SELECT command denied to user 'query01'@'localhost' for table 'test_ab_2'
 # 不允许*查询
mysql> desc test_ab_2;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| A     | bigint | YES  |     | NULL    |       |
| index | bigint | YES  | MUL | NULL    |       |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

撤销授权

REVOKE select ON test.test_ab FROM 'query01'@'%';
REVOKE select(`INDEX`,A) ON test.test_ab_2 FROM 'query01'@'%';

mysql> desc test_ab_2;
ERROR 1142 (42000): SELECT command denied to user 'query01'@'localhost' for table 'test_ab_2'
mysql> desc test_ab;
ERROR 1142 (42000): SELECT command denied to user 'query01'@'localhost' for table 'test_ab'
mysql> show tables;
ERROR 1044 (42000): Access denied for user 'query01'@'%' to database 'test'

查看已有权限

SHOW GRANTS FOR 'query01'@'%';

GRANT USAGE ON *.* TO `query01`@`%` -- 本质上是“不给任何权限的授权”