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

Implementation of Mysql User Rights Management

1. Introduction to MySQL permissions There are 4 ...

The whole process of IDEA integrating docker to deploy springboot project

Table of contents 1. IDEA downloads the docker pl...

There is an extra blank line after the html page uses include to import the php file

The method found on the Internet works The footer ...

React entry-level detailed notes

Table of contents 1. Basic understanding of React...

Docker+nextcloud to build a personal cloud storage system

1. Docker installation and startup yum install ep...

MySQL decimal unsigned update negative numbers converted to 0

Today, when verifying the concurrency problem of ...

Let's talk briefly about the changes in setup in vue3.0 sfc

Table of contents Preface Standard sfc writing me...

Detailed explanation based on event bubbling, event capture and event delegation

Event bubbling, event capturing, and event delega...

Detailed explanation of slots in Vue

The reuse of code in vue provides us with mixnis....

Cross-domain issues in front-end and back-end separation of Vue+SpringBoot

In the front-end and back-end separation developm...

MySQL Installer Community 5.7.16 installation detailed tutorial

This article records the detailed tutorial of MyS...

Do you know all 24 methods of JavaScript loop traversal?

Table of contents Preface 1. Array traversal meth...

Detailed introduction to the MySQL installation tutorial under Windows

Table of contents 1. Some concepts you need to un...

Database issues and pitfalls in connecting to cloud servers with Navicat Premium15

When using a cloud server, we sometimes connect t...

Introduction to Spark and comparison with Hadoop

Table of contents 1. Spark vs. Hadoop 1.1 Disadva...