MySQL adds database operation permissions to users. SQL statements

Below I will summarize some SQL statements about MySQL giving users database operation permissions, which makes it very convenient for me to authorize mysql users, these statements can only be operated by the root or super administrator.

Syntax:

The Code is as follows:	Copy code
Grant all privileges on 'db _ name'. * TO 'user _ name' @ 'host' with grant option;

Example

Grant normal data users the right to query, insert, update, and delete all table data in the database.

The Code is as follows:	Copy code
Grant select on testdb. * to common_user @ '%' Grant insert on testdb. * to common_user @ '%' Grant update on testdb. * to common_user @ '%' Grant delete on testdb. * to common_user @ '%'

Alternatively, replace the following with a MySQL command:

The Code is as follows:	Copy code
Grant select, insert, update, delete on testdb. * to common_user @ '%'

1. grant applies to the entire MySQL Server:

The Code is as follows:	Copy code
Grant select on *. * to dba @ localhost; -- dba can query tables in all databases in MySQL. Grant all on *. * to dba @ localhost; -- dba can manage all databases in MySQL

 
2. grant applies to a single database:

The Code is as follows:	Copy code
Grant select on testdb. * to dba @ localhost; -- dba can query tables in testdb.

3. grant applies to a single data table:

The Code is as follows:	Copy code
Grant select, insert, update, delete on testdb. orders to dba @ localhost;

4. grant applies to columns in the table:

The Code is as follows:	Copy code
Grant select (id, se, rank) on testdb. apache_log to dba @ localhost;

5. grant applies to stored procedures and functions:

The Code is as follows:	Copy code
Grant execute on procedure testdb. pr_add to 'dba '@ 'localhost' Grant execute on function testdb. fn_add to 'dba '@ 'localhost'

Grant database developers to create tables, indexes, views, stored procedures, and functions... .
Grant permissions to create, modify, and delete MySQL Data Table structures.

The Code is as follows:	Copy code
Grant create on testdb. * to developer @ '192. 192.% '; Grant alter on testdb. * to developer @ '192. 192.% '; Grant drop on testdb. * to developer @ '192. 192.% ';

Grant the MySQL foreign key operation permission.

The Code is as follows:	Copy code
Grant references on testdb. * to developer @ '192. 192.% ';

Grant the permission to operate MySQL temporary tables.

The Code is as follows:	Copy code
Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';

Grant the permission to operate MySQL indexes.

The Code is as follows:	Copy code
Grant index on testdb. * to developer @ '192. 192.% ';

Grant permissions to operate the MySQL view and view the source code.

The Code is as follows:	Copy code
Grant create view on testdb. * to developer @ '192. 192.% '; Grant show view on testdb. * to developer @ '192. 192.% ';

Grant permissions to operate MySQL stored procedures and functions.

The Code is as follows:	Copy code
Grant create routine on testdb. * to developer @ '192. 192.% '; -- now, can show procedure status Grant alter routine on testdb. * to developer @ '192. 192.% '; -- now, you can drop a procedure Grant execute on testdb. * to developer @ '192. 192.% ';