Shulou(Shulou.com)05/31 Report--

How to query user permissions in MySQL? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Introduce two ways to view MySQL user rights

1. Use the MySQL grants command

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

It is important to note that:

The combination of ● username and ip needs to exist in the mysql.user table, which can be seen through the select * from mysql.user command

● ip address should be in quotation marks if it is in wildcard format, for example: show grants for root@'172.%'

2. Use the MySQL select command

Mysql > select * from mysql.user where user='root' and host='localhost'\ G * * 1. Row * * Host: localhostUser: rootPassword: * * Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_ Priv: YFile_priv: YGrant_priv: YReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: YCreate_tablespace_priv: Yssl_type:ssl_cipher:x509_issuer:x509_ Subject:max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string:password_expired: N1 row in set (0.01sec)

Knowledge point expansion:

Let's create a test account test to grant surface-level permissions

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: MyDBUser: testTable_name: kkkGrantor: root@localhostTimestamp: 0000-00-0000: 00:00Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,TriggerColumn_priv: 1 row in set (0.01sec) ERROR: No query specifiedmysql >

Let's create a test account test to grant column-level permissions

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-0000: 00:00 | Select | |% | MyDB | test | TEST1 | col1 | 0000-00-0000: 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 >

This is the answer to the question about how to query user permissions in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.