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

This article will explain in detail how to query user permissions in mysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Global level

Global permissions apply to all databases in a given server. These permissions are stored in the mysql.user table. GRANT ALL ON *. * and REVOKE ALL ON *. * only grant and revoke global permissions.

Database level

Database permissions apply to all targets in a given database. These permissions are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* only grant and revoke database permissions.

Surface level

Table permissions apply to all columns in a given table. These permissions are stored in the mysql.tables_ private table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name only grant and revoke table permissions.

Column level

Column permissions apply to a single column in a given table. These permissions are stored in the mysql.columns_ private table. When using REVOKE, you must specify the same column as the authorized column.

Subroutine level

CREATE ROUTINE, ALTER ROUTINE, EXECUTE and GRANT permissions apply to stored subroutines. These permissions can be granted at the global and database levels. Moreover, in addition to CREATE ROUTINE, these permissions can be granted at the subroutine level and stored in the mysql.procs_ private table.

1: so let's create a test account test and grant permissions at the global level. As follows:

Mysql > grant select,insert on *. * 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 >

Then you can query the permissions granted to test in two ways. As follows:

Mysql > show grants for test +-+ | Grants for test@% | +- -+ | GRANT SELECT INSERT ON *. * TO 'test'@'%' IDENTIFIED BY PASSWORD' * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' | +- -+ 1 row in set (0.00 sec) mysql > select * from mysql.user where user='test'\ G * * 1. Row * * Host:% User: testPassword: * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29Select_priv: YInsert_priv: YUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NProcess_priv: NFile_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter _ priv: NShow_db_priv: NSuper_priv: NCreate_tmp_table_priv: NLock_tables_priv: NExecute_priv: NRepl_slave_priv: NRepl_client_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NCreate_user_priv: NEvent_priv: NTrigger_priv: NCreate_tablespace_priv: Nssl_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.04 sec) ERROR: No query specifiedmysql >

2: so let's create a test account test and grant permissions at the database level. As follows:

Mysql > drop user test;Query OK, 0 rows affected (0.00 sec) mysql > grant select,insert,update,delete on MyDB.* to test@'%' identified by 'test';Query OK, 0 rows affected (0.01 sec) mysql > mysql > select * from mysql.user where user='test'\ G;-- you can see that there is no authorization. Mysql > select * from mysql.db where user='test'\ G * * 1. Row * * Host:% Db: MyDBUser: testSelect_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: NDrop_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NCreate_tmp_table_priv: NLock_tables_priv: NCreate _ view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NExecute_priv: NEvent_priv: NTrigger_priv: N1 row in set (0.04 sec) ERROR: No query specifiedmysql > mysql > show grants for test +-+ | Grants for test@% | +- -+ | GRANT USAGE ON *. * TO 'test'@'%' IDENTIFIED BY PASSWORD' * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' | GRANT SELECT INSERT, UPDATE, DELETE ON `MyDB`. * TO 'test'@'%' | +-+ 2 rows in set (0.00 sec) mysql >

3: so let's create a test account test and grant surface-level permissions. As follows:

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 >

4: so let's create a test account test to grant column-level permissions. As follows:

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 >

5: so let's create a test account test to grant permissions at the subroutine level. As follows:

Mysql > DROP PROCEDURE IF EXISTS PRC_TEST;Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER / / mysql > CREATE PROCEDURE PRC_TEST ()-> BEGIN- > SELECT * FROM kkk;- > END / / Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER; mysql > grant execute on procedure MyDB.PRC_TEST 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 > show grants for test +-+ | Grants for test@% | +- -+ | GRANT USAGE ON *. * TO 'test'@'%' IDENTIFIED BY PASSWORD' * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' | GRANT EXECUTE ON PROCEDURE `MyDB`.`prc _ test`TO 'test'@'%' | +-+ 2 rows in set (0.00) Sec) mysql > select * from mysql.procs_priv where User='test' +-+ | Host | Db | User | Routine_name | Routine_type | Grantor | Proc _ priv | Timestamp | +-+-+ |% | MyDB | test | PRC_TEST | PROCEDURE | root @ localhost | Execute | 0-00-0000: 00:00 | +-+-+ 1 row in set (0000 sec) mysql >

On how to query user permissions in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.