mysql授予列权限_MySQL列权限授予及注意事项

对于权限的授予，库和表级别的都比较常见，但具体到表的某个列的访问权限怎么授予呢？建立视图？是否也能够按照独立的列授予insert|delete|update的权限呢？

其实，MySQL对列的受权，是有专门的语法实现的，例如授予test库priv_test表的id列的select权限给sam用户，能够用如下语法实现：

grant select(id) on test.priv_test to sam@‘localhost’;mysql

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