MySQL查询用户权限的方法总结
介绍两种查看MySQL用户权限的两种方法 1、 使用MySQL grants命令 mysql show grants for username@localhost;+---------------------------------------------------------------------+| Grants for root@localhost |+-----------------
介绍两种查看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>
我们来创建一个测试账号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>
到此这篇关于MySQL查询用户权限的方法总结的文章就介绍到这了,更多相关两种MySQL查询用户权限的方法内容请搜索每日运维以前的文章或继续浏览下面的相关文章希望大家以后多多支持每日运维！
