Detailed explanation of the process of querying user permissions using mysql statements

Detailed explanation of the process of querying user permissions using mysql statements

In MySQL, how do you view the permissions a user has been granted? The permissions granted to users may be divided into global-level permissions, database-level permissions, table-level permissions, column-level permissions, and subroutine-level permissions. The specific categories are as follows:

Global Level

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

Database level

Database privileges apply to all objects 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.* grant and revoke database privileges only.

Table Level

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

Column level

Column permissions apply to a single column in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE, you must specify the same columns as those being granted.

Subroutine level

CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT privileges apply to stored routines. These privileges can be granted at the global level or at the database level. Also, except CREATE ROUTINE, these privileges can be granted at the subprogram level and are stored in the mysql.procs_priv table.

1: Then let's create a test account test and grant global-level permissions . As shown below:

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 use the following two methods to query the permissions granted to test. As shown below:

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: test
Password: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
Select_priv: Y
Insert_priv: Y
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
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.04 sec)
ERROR: 
No query specified
mysql>

2: Then let's create a test account test and grant database-level permissions. As shown below:

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: MyDB
User: test
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.04 sec)
ERROR: 
No query specified
mysql> 
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: Then let's create a test account test and grant table-level permissions. As shown below:

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>

4: Then let's create a test account test and grant column-level permissions. As shown below:

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>

5: Then let's create a test account test and grant it subprogram-level permissions. As shown below:

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 | 0000-00-00 00:00:00 |
+------+------+------+--------------+--------------+----------------+-----------+--------------------+
1 row in set (0.00 sec)
mysql>

Therefore, if you need to view the permissions granted to a user, you need to view the granted permissions from these five levels. From top to bottom or from small to high, check the permissions granted to each level one by one.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • SQL query statement to find the number of consecutive login days of a user
  • MySql sets the specified user database view query permissions
  • Detailed explanation of the idea of ​​Hive-SQL querying continuously active logged-in users

<<:  Vue integrates Tencent Map to implement API (with DEMO)

>>:  Mount the disk in a directory under Ubuntu 18.04

Recommend

Detailed explanation of MySQL trigger trigger example

Table of contents What is a trigger Create a trig...

Implementation steps of encapsulating components based on React

Table of contents Preface How does antd encapsula...

JavaScript exquisite snake implementation process

Table of contents 1. Create HTML structure 2. Cre...

Analysis of the Nesting Rules of XHTML Tags

In the XHTML language, we all know that the ul ta...

Solution to the error message "java.sql.SQLException: Incorrect string value:'\xF0\x9F\x92\xA9\x0D\x0A...'" when storing emoticons in MySQL

Preface This article mainly introduces the soluti...

Configure VIM as a C++ development editor in Ubuntu

1. Copy the configuration file to the user enviro...

How to use union all in MySQL to get the union sort

Sometimes in a project, due to some irreversible ...

Several ways to manually implement HMR in webpack

Table of contents 1. Introduction 2. GitHub 3. Ba...

Problems encountered when installing mysql8.0.15 winx64 on Win10 and connecting to the server

1. Download, install and configure mysql-8.0.15 1...

Commonly used JavaScript array methods

Table of contents 1. filter() 2. forEach() 3. som...

Global call implementation of Vue2.x Picker on mobile terminal

Table of contents What is the Picker component Pr...

What are the core modules of node.js

Table of contents Global Object Global objects an...

Solution for Nginx installation without generating sbin directory

Error description: 1. After installing Nginx (1.1...

Solution to SNMP4J server connection timeout problem

Our network management center serves as the manag...

WeChat applet custom tabBar step record

Table of contents 1. Introduction 2. Customize ta...