怎样在MySQL中查询用户权限
发布时间：2022-02-14 22:35:38 所属栏目：MySql教程 来源：互联网
导读：怎么在MySQL中查询用户权限？针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。 介绍两种查看MySQL用户权限的两种方法 1、 使用MySQL grants命令 mysql show grants for username@localho
|
怎么在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_privG; *************************** 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中查询用户权限问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助。 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
