IT源码网

MySQL列权限授予及注意事项

sanshao 2021年05月15日 数据库 369 0

对于权限的授予,库和表级别的都比较常见,但具体到表的某个列的访问权限怎么授予呢?创建视图?是否也可以按照独立的列授予insert|delete|update的权限呢?
其实,MySQL对列的授权,是有专门的语法实现的,例如授予test库priv_test表的id列的select权限给sam用户,可以用以下语法实现:
grant select(id) on test.priv_test to sam@‘localhost’;

mysql> select * from priv_test; 
+------+------+ 
| id   | id2  | 
+------+------+ 
|    1 |    1 | 
+------+------+ 
1 row in set (0.00 sec) 
 
mysql> grant select(id) on test.priv_test to sam@'localhost'; 
Query OK, 0 rows affected (0.15 sec) 
 
mysql> show grants for sam@'localhost'; 
+----------------------------------------------------------------+ 
| Grants for sam@localhost                                       | 
+----------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO `sam`@`localhost`                        | 
| GRANT SELECT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` | 
+----------------------------------------------------------------+ 
2 rows in set (0.00 sec) 
 
mysql> exit 
Bye 
[root@linux-base ~]# mysql -usam -psam123 
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 64 
Server version: 8.0.12 MySQL Community Server - GPL 
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 
 
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 | 
| test               | 
+--------------------+ 
2 rows in set (0.00 sec) 
 
mysql> use test; 
Database changed 
mysql> desc priv_test; 
+-------+---------+------+-----+---------+-------+ 
| Field | Type    | Null | Key | Default | Extra | 
+-------+---------+------+-----+---------+-------+ 
| id    | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+ 
1 row in set (0.00 sec) 
 
mysql> select * from priv_test; 
ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test' 
mysql> select id from priv_test;  
+------+ 
| id   | 
+------+ 
|    1 | 
+------+ 
1 row in set (0.00 sec) 

既然可以为单独的列授予select权限,那么DML操作呢?例如insert能不能用以下语法对单独的列授权呢?
grant insert(id) on test.priv_test to sam@‘localhost’;

mysql> show grants for sam@'localhost'; 
+----------------------------------------------------------------+ 
| Grants for sam@localhost                                       | 
+----------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO `sam`@`localhost`                        | 
| GRANT SELECT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` | 
+----------------------------------------------------------------+ 
2 rows in set (0.01 sec) 
 
mysql> grant insert(id) on test.priv_test to sam@'localhost'; 
Query OK, 0 rows affected (0.15 sec) 
 
mysql> show grants for sam@'localhost'; 
+-------------------------------------------------------------------------------+ 
| Grants for sam@localhost                                                      | 
+-------------------------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO `sam`@`localhost`                                       | 
| GRANT SELECT (`id`), INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` | 
+-------------------------------------------------------------------------------+ 
2 rows in set (0.00 sec) 
 
mysql> exit 
Bye 
[root@linux-base ~]# mysql -usam -psam123 
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 70 
Server version: 8.0.12 MySQL Community Server - GPL 
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 
 
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> use test; 
Database changed 
mysql> insert into priv_test(id) values (1); 
Query OK, 1 row affected (0.14 sec) 
 
mysql> insert into priv_test(id2) values (1);  
ERROR 1143 (42000): INSERT command denied to user 'sam'@'localhost' for column 'id2' in table 'priv_test' 
mysql> select id from priv_test; 
+------+ 
| id   | 
+------+ 
|    1 | 
+------+ 
1 row in set (0.00 sec) 
 
mysql> select * from priv_test; 
ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test' 

确实可以,不错不错。那么,是否会存在一种情况:对一个列有insert的权限,但没有select的权限?那么插入数据之后,还能不能看由自己插入的东西呢?

mysql> select * from priv_test; 
+------+------+ 
| id   | id2  | 
+------+------+ 
|    1 |    1 | 
+------+------+ 
1 row in set (0.03 sec) 
 
mysql> revoke SELECT (`id`) ON `test`.`priv_test` from `sam`@`localhost`; 
Query OK, 0 rows affected (0.10 sec) 
 
mysql> show grants for sam@'localhost'; 
+----------------------------------------------------------------+ 
| Grants for sam@localhost                                       | 
+----------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO `sam`@`localhost`                        | 
| GRANT INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` | 
+----------------------------------------------------------------+ 
2 rows in set (0.00 sec) 
 
mysql> exit 
Bye 
[root@linux-base ~]# mysql -usam -psam123 
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 72 
Server version: 8.0.12 MySQL Community Server - GPL 
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 
 
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> use test; 
Database changed 
mysql> insert into priv_test(id) values (2); 
Query OK, 1 row affected (0.14 sec) 
 
mysql> select id from priv_test; 
ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test' 

可以看到,有insert没select,就会出现能插入,但无法查看数据。在权限授予上需要注意。
另外即使删除了表,用户拥有的权限也不会自动回收的,这也是一个安全隐患的地方。

mysql> show grants for sam@'localhost'; 
+----------------------------------------------------------------+ 
| Grants for sam@localhost                                       | 
+----------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO `sam`@`localhost`                        | 
| GRANT INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` | 
+----------------------------------------------------------------+ 
2 rows in set (0.00 sec) 
 
mysql> drop table test.priv_test; 
Query OK, 0 rows affected (0.14 sec) 
 
mysql> desc test.priv_test; 
ERROR 1146 (42S02): Table 'test.priv_test' doesn't exist 
mysql> show grants for sam@'localhost'; 
+----------------------------------------------------------------+ 
| Grants for sam@localhost                                       | 
+----------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO `sam`@`localhost`                        | 
| GRANT INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` | 
+----------------------------------------------------------------+ 
2 rows in set (0.00 sec) 

评论关闭
IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

MYSQL锁机制详解