The simple format of MySQL command granting user permission can be summarized as follows:
grant permission on database object to user

1, grant ordinary data users have 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, use a MySQL command instead:

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

2, grant database developers, create tables, indexes, views, stored procedures, functions and other permissions

grant has permission to create, modify and delete MySQL data table structure.

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

grant operation MySQL foreign key permission:

grant references on testdb.*to developer@'192.168.0.%';

grant operation MySQL temporary table permission:

grant create temporary tableson testdb.*to developer@'192.168.0.%';

grant operation MySQL index permission:

grant index on testdb.*to developer@'192.168.0.%';

grant permission to operate MySQL view and view view view source code:

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

grant operation MySQL stored procedure and function permissions:

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

3, grant general DBA's permission to manage a MySQL database

grant all privileges on testdbto dba@'localhost';

The keyword "privileges" can be omitted.

4, grant advanced DBA manages the permissions of all databases in MySQL:

grant all on *.*to dba@'localhost';

5, MySQL grant permissions can act on multiple levels

1. grant works on the entire MySQL server:

grant select on *.*to dba@localhost;
-- dba Can query MySQL Tables in all databases in.
grant all  on *.*to dba@localhost;
-- dba Can manage MySQL All databases in

1. grant works on a single database:

grant select on testdb.*to dba@localhost;
-- dba Can query testdb Table in.

1. grant works on a single data table:

grant select,insert,update,delete on testdb.ordersto dba@localhost;

Here, when you authorize multiple tables for a user, you can execute the above statement multiple times. For example:

grant select(user_id,username)on smp.users to mo_user@'%' identified by '123345';
grant select on smp.mo_sms to mo_user@'%' identified by '123345';

1. grant acts on columns in the table:

grant select(id, se, rank)on testdb.apache_log to dba@localhost;

1. grant acts on stored procedures and functions:

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

6, View MySQL user permissions

View the current user's (own) permissions:

show grants;

View other MySQL user permissions:

show grants for dba@localhost;

7, Revoke the permissions that have been granted to MySQL users.

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

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

8, Notes on MySQL grant and revoke user permissions

1. grant, revoke user permission, the user must reconnect to the MySQL database before the permission can take effect.

2. If you want users to have authorization rights, you can grant these permissions to other users. The option "grant option" is required“

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

This feature is generally not used. In practice, database permissions are best managed by DBA s.

Supplement: there are five mysql authorization tables: user, db, host and tables_priv and columns_priv.

The contents of the authorization form are used for the following purposes:

user table

The user table lists the users who can connect to the server and their passwords, and it specifies what global (super user) permissions they have. Any permissions enabled in the user table are global and apply to all databases. For example, if you enable DELETE permission, the users listed here can DELETE records from any table, so think carefully before you do so.

db table

The db table lists databases that users have access to. The permissions specified here apply to all tables in a database.

host table

The combination of host table and db table can control the access rights of a specific host to the database at a better level, which may be better than using db alone. This table is not affected by GRANT and REVOKE statements, so you may find that you don't use it at all.

tables_priv table tables_ The priv table specifies table level permissions. A permission specified here applies to all columns of a table.

columns_priv table columns_ The priv table specifies column level permissions. The permissions specified here apply to specific columns of a table.

reference resources: https://blog.csdn.net/weixin_36360005/article/details/113204326