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

This article introduces the relevant knowledge of "what permissions are there in mysql". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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, and so on.

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

What permissions are available in mysql

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_priv table

Additional knowledge:

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'

This is the end of the content of "what permissions are there in mysql"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.