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

How to understand the MySQL database layer 5 permissions, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Overview

In MySQL, how do you see what permissions a user is granted? The permissions granted to users may be divided into global level permissions, database level permissions, surface level permissions, column level permissions, and subroutine level permissions. Here is a demonstration with an experiment:

Specific classification of permissions

1. 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.

2. 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.

3. Table 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.

4. 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.

5. 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. Global level testing

Create a test account test and grant permissions at the global level. As follows:

Mysql > set global validate_password_policy=0; mysql > grant select,insert on *. * to test@'%' identified by 'test'; mysql > flush privileges

Query the permissions granted to test in two ways. As follows:

Mysql > show grants for test; mysql > select * from mysql.user where user='test'G

2. Database level testing

Create a test account test and grant permissions at the database level. As follows:

Mysql > drop user test; mysql > grant select,insert,update,delete on jpcpdb.* to test@'%' identified by 'test@123'; mysql > select * from mysql.user where user='test'G;-you can see that there is no authorization. Mysql > show grants for test; mysql > select * from mysql.db where user='test'G

3. Table-level testing

Create a test account test and grant surface-level permissions. As follows:

Mysql > drop user test; mysql > flush privileges; mysql > grant all on jpcpdb.user to test@'%' identified by 'test@123'; mysql > show grants for test; mysql > select * from mysql.tables_privG

4. Column level testing

Create a test account, test, and grant column-level permissions. As follows:

Mysql > drop user test; mysql > flush privileges; mysql > grant select (id, name) on jpcpdb.user to test@'%' identified by 'test@123'; mysql > flush privileges; mysql > select * from mysql.columns_priv; mysql > show grants for test

5. Subroutine level testing

Create a test account test to grant permissions at the subroutine level. As follows:

Mysql > DROP PROCEDURE IF EXISTS PRC_TEST; mysql > DELIMITER / / mysql > CREATE PROCEDURE PRC_TEST ()-> BEGIN-> SELECT * FROM user;-> END / / mysql > DELIMITER; mysql > grant execute on procedure jpcpdb.PRC_TEST to test@'%' identified by 'test@123'; mysql > flush privileges; mysql > show grants for test; mysql > select * from mysql.procs_priv where User='test'

If you need to see the permissions granted by the user, you need to view the granted permissions from these five levels. Check the permissions granted at each level from top to bottom or from small to top.

After reading the above, do you know how to understand the layer 5 permissions of the MySQL database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.