Detailed parsing of MySQLgrant syntax

The following is a detailed explanation of the MySQLgrant syntax. If you are interested in the actual operations related to the MySQLgrant syntax, you can click and view the following articles. We all know that the simple format of commands for granting user permissions to MySQL database can be summarized as: grant permissions on database objects to users 1. grant Common Data

The following is a detailed explanation of MySQL grant syntax. If you are interested in the actual operations related to MySQL grant syntax, you can click and view the following articles. We all know that the simple format of commands for granting user permissions to MySQL database can be summarized as: grant permissions on database objects to users 1. grant Common Data

The following is a detailed explanation of MySQL grant syntax. If you are interested in the actual operations related to MySQL grant syntax, you can click and view the following articles. We all know that the simple format of commands for granting user permissions to MySQL databases can be summarized as follows:

Grant permission on database objects to users

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

 
 

  
  
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:

 
 

  
  
grant select, insert, update, delete on testdb.* to common_user@'%' 
 
 

2. grant database developers to create tables, indexes, views, stored procedures, and functions... .

Grant permissions to create, modify, and delete MySQL Data Table structures.

 
 

  
  
grant create on testdb.* to developer@'192.168.0.%'; 
  
  
grant alter on testdb.* to developer@'192.168.0.%'; 
  
  
grant drop on testdb.* to developer@'192.168.0.%'; 
 
 

MySQLgrant is used to operate MySQL Foreign keys.

Grant references on testdb. * to developer @ '192. 192.% ';

Grant the permission to operate MySQL temporary tables.

Grant create temporary tables on testdb. *

Grant the permission to operate MySQL indexes.

Grant index on testdb. *

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

 
 

  
  
grant create view on testdb.* to developer@'192.168.0.%'; 
  
  
grant show view on testdb.* to developer@'192.168.0.%'; 
 
 

Grant permissions to operate MySQL stored procedures and functions.

 
 

  
  
grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status 
  
  
grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure 
  
  
grant execute on testdb.* to developer@'192.168.0.%'; 
 
 

3. grant common DBA permission to manage a MySQL database.

MySQLgrant all privileges on testdb

The keyword "privileges" can be omitted.

 
 

  
  
grant execute on procedure testdb.pr_add to 'dba'@'localhost' 
  
  
grant execute on function testdb.fn_add to 'dba'@'localhost' 
  
  
grant all on *.* to dba@'localhost' 
 
 

5. MySQL grant permissions can be applied to multiple levels.

1. grant applies to the entire MySQL Server:

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:

Grant select on testdb. * to dba @ localhost; -- dba can query tables in testdb.

3. grant applies to a single data table:

Grant select, insert, update, delete on testdb. orders

4. grant applies to columns in the table:

Grant select (id, se, rank) on testdb. apache_log

5. MySQL grant applies to stored procedures and functions:

 
 

  
  
grant execute on procedure testdb.pr_add to 'dba'@'localhost' 
  
  
grant execute on function testdb.fn_add to 'dba'@'localhost' 
 
 

Vi. View MySQL user permissions

View Current user (own) permissions:

Show grants;

View other MySQL user permissions:

Show grants for dba @ localhost;

7. revoke permissions granted to MySQL users.

The syntax of revoke is similar to that of grant. You only need to replace the keyword "to" with "from:

 
 

  
  
grant all on *.* to dba@localhost; 
  
  
revoke all on *.* from dba@localhost; 
 
 

VIII. Considerations for MySQL grant and revoke User Permissions

1. After the grant and revoke permissions are granted, the permissions can only take effect after the user reconnects to the MySQL database.

2. If you want to authorize the user, you can also grant these permissions to other users. You need to select "grant option".

Grant select on testdb. * to dba @ localhost with grant option;

This feature is generally unavailable. In practice, it is best for DBAs to manage database permissions in a unified manner.