mysql权限管理

1.查看用户的权限

mysql> show grants for test@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `mysql`.`user` TO 'test'@'%'                                                                 |
+--------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

不加for用户，则显示当前用户的权限：

mysql> show grants ;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

2 rows in set (0.00 sec)

3.回收权限：

mysql> revoke all on *.* from test;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test;
+-----------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `mysql`.`user` TO 'test'@'%'                                                        |
+-----------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

4.给用户所有权限  -->对应的数据字典：mysql.user

grant all on *.* to xxx with grant option;

5.给用户某个数据库的权限  -->对应的数据字典：mysql.db

grant all on test.* to xxxx ;

6.给用户访问某个数据库下的某张表的权限  -->对应的数据字典：mysql.tables_priv

grant all on test.test to xxxx;

7.给用户某个数据库下某张表的A字段的权限   -->对应的数据字典：mysql.columns_priv

grant select(A) on test.test to xxxx;