MySQL查询用户权限的方法总结

介绍两种查看MySQL 用户权限的两种方法

1、使用MySQL grants命令

									mysql> show grants for username@localhost;

									+---------------------------------------------------------------------+

									| Grants for root@localhost |

									+---------------------------------------------------------------------+

									| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

									+---------------------------------------------------------------------+

需要注意的是：

● username和ip的组合需要是在mysql.user表中存在的，具体可以通过 select * from mysql.user 命令查看

● ip地址如果是通配符格式需要加引号，例如：show grants for root@'172.%';

2、使用MySQL select命令

									mysql> select * from mysql.user where user='root' and host='localhost' \G;

									*************************** 1. row ***************************

									Host: localhost

									User: root

									Password: **********************

									Select_priv: Y

									Insert_priv: Y

									Update_priv: Y

									Delete_priv: Y

									Create_priv: Y

									Drop_priv: Y

									Reload_priv: Y

									Shutdown_priv: Y

									Process_priv: Y

									File_priv: Y

									Grant_priv: Y

									References_priv: Y

									Index_priv: Y

									Alter_priv: Y

									Show_db_priv: Y

									Super_priv: Y

									Create_tmp_table_priv: Y

									Lock_tables_priv: Y

									Execute_priv: Y

									Repl_slave_priv: Y

									Repl_client_priv: Y

									Create_view_priv: Y

									Show_view_priv: Y

									Create_routine_priv: Y

									Alter_routine_priv: Y

									Create_user_priv: Y

									Event_priv: Y

									Trigger_priv: Y

									Create_tablespace_priv: Y

									ssl_type:

									ssl_cipher:

									x509_issuer:

									x509_subject:

									max_questions: 0

									max_updates: 0

									max_connections: 0

									max_user_connections: 0

									plugin: mysql_native_password

									authentication_string:

									password_expired: N

									1 row in set (0.01 sec)

知识点扩展：

我们来创建一个测试账号test，授予表层级的权限

									mysql> drop user test;

									Query OK, 0 rows affected (0.00 sec)

									mysql> flush privileges;

									Query OK, 0 rows affected (0.00 sec)

									mysql> grant all on MyDB.kkk to test@'%' identified by 'test';

									Query OK, 0 rows affected (0.01 sec)

									mysql> 

									mysql> show grants for test;

									+-----------------------------------------------------------------------------------------------------+

									| Grants for test@% |

									+-----------------------------------------------------------------------------------------------------+

									| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |

									| GRANT ALL PRIVILEGES ON `MyDB`.`kkk` TO 'test'@'%' |

									+-----------------------------------------------------------------------------------------------------+

									2 rows in set (0.00 sec)

									mysql> select * from mysql.tables_priv\G;

									*************************** 1. row ***************************

									Host: %

									Db: MyDB

									User: test

									Table_name: kkk

									Grantor: root@localhost

									Timestamp: 0000-00-00 00:00:00

									Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger

									Column_priv: 

									1 row in set (0.01 sec)

									ERROR: 

									No query specified

									mysql> <br>

我们来创建一个测试账号test，授予列层级的权限

									mysql> drop user test;

									Query OK, 0 rows affected (0.00 sec)

									mysql> flush privileges;

									Query OK, 0 rows affected (0.00 sec)

									mysql> grant select (id, col1) on MyDB.TEST1 to test@'%' identified by 'test';

									Query OK, 0 rows affected (0.01 sec)

									mysql> flush privileges;

									Query OK, 0 rows affected (0.00 sec)

									mysql> 

									mysql> select * from mysql.columns_priv;

									+------+------+------+------------+-------------+---------------------+-------------+

									| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |

									+------+------+------+------------+-------------+---------------------+-------------+

									| % | MyDB | test | TEST1 | id | 0000-00-00 00:00:00 | Select |

									| % | MyDB | test | TEST1 | col1 | 0000-00-00 00:00:00 | Select |

									+------+------+------+------------+-------------+---------------------+-------------+

									2 rows in set (0.00 sec)

									mysql> show grants for test;

									+-----------------------------------------------------------------------------------------------------+

									| Grants for test@% |

									+-----------------------------------------------------------------------------------------------------+

									| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |

									| GRANT SELECT (id, col1) ON `MyDB`.`TEST1` TO 'test'@'%' |

									+-----------------------------------------------------------------------------------------------------+

									2 rows in set (0.00 sec)

									mysql> <br>

到此这篇关于MySQL查询用户权限的方法总结的文章就介绍到这了,更多相关两种MySQL查询用户权限的方法内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.php.cn/mysql-tutorials-444100.html

MySQL查询用户权限的方法总结

相关文章

热门资讯