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

WeChat applet custom scroll-view example code

Mini Program Custom Scroll-View Scroll Bar Withou...

jQuery manipulates cookies

Copy code The code is as follows: jQuery.cookie =...

Tutorial on how to install htop on CentOS 8

If you are looking to monitor your system interac...

MySQL Series 12 Backup and Recovery

Table of contents Tutorial Series 1. Backup strat...

An example of implementing a simple finger click animation with CSS3 Animation

This article mainly introduces an example of impl...

Disadvantages and reasonable use of MySQL database index

Table of contents Proper use of indexes 1. Disadv...

Docker builds python Flask+ nginx+uwsgi container

Install Nginx First pull the centos image docker ...

Detailed explanation of Xshell common problems and related configurations

This article introduces common problems of Xshell...

Solution for Docker Swarm external verification load balancing not taking effect

Problem Description I created three virtual machi...

CentOS 7 method to modify the gateway and configure the IP example

When installing the centos7 version, choose to co...

A brief discussion on whether MySQL can have a function similar to Oracle's nvl

Use ifnull instead of isnull isnull is used to de...

Detailed explanation of keepAlive use cases in Vue

In development, it is often necessary to cache th...

Linux IO multiplexing epoll network programming

Preface This chapter uses basic Linux functions a...

JavaScript to implement image preloading and lazy loading

This article shares the specific code for impleme...

How to expand Linux swap memory

Swap memory mainly means that when the physical m...