What permissions are there in mysql

Mysql permissions: 1. Global permissions, applicable to all databases in the server, stored in "mysql.user"; 2. Database permissions, applicable to all targets in the database, stored in "mysql. db" and "mysql.host"; 3. Table permissions, applicable to all columns in the table; 4. Column permissions, etc.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What permissions are there in mysql

Specific classification of permissions

1. Global level

Global permissions apply to a given server all databases in . 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_priv 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_priv table. When using REVOKE, you must specify the same columns as the authorized columns.

5. Subprogram level

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

Additional knowledge:

1 , Global level test

Create a test account test and grant global level permissions. As shown below:

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

Use the following two methods to query the permissions granted to test. As shown below:

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

2. Database level test

Create a test account test and grant database level permissions. As shown below:

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; --可以看到无任何授权。
mysql> show grants for test;
mysql> select * from mysql.db where user='test'G;

3. Table-level test

Create a test account test and grant it to the table-level permissions. As shown below:

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 test

Create a test account test and grant column-level permissions. As shown below:

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. Subprogram level test

Create a test account test and grant subprogram level permissions. As shown below:

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';

Summary

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

The database is a general data processing system for a unit or an application field. It stores a collection of relevant data belonging to enterprises and business departments, groups and individuals. The data in the database is established from a global perspective and organized, described and stored according to a certain data model. Its structure is based on the natural connection between data, which can provide all necessary access paths, and the data is no longer targeted at a certain application, but is oriented to the entire organization and has overall structural characteristics.

Recommended learning: mysql video tutorial

The above is the detailed content of What permissions are there in mysql. For more information, please follow other related articles on the PHP Chinese website!