mysql 表授权

zmx

2026 年 04 月 14 日

4 次浏览

暂无评论

2926字数

MySQL

# 引言

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

# 首先创建用户

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

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

```sql
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

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.

# 表授权

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

查询库和表

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)
```

# 字段授权

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

查看

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

# 撤销授权

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

```sql
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'
```

# 查看已有权限

```sql
SHOW GRANTS FOR 'query01'@'%';

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