MySQL Authorization command grant usage method detailed

MySQL gives user permission commands can be simply summed up as:

Grant permission on database object to user

Example one:
Grant normal data users, the right to query, insert, UPDATE, and delete all table data in the database, which means that (all IP) common_user users from any domain have the ability to query, insert, UPDATE, delete.

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, you can use one of the following simple commands:

Grant SELECT, INSERT, UPDATE, delete on testdb.* to common_user@ '% '

Example two:
Grant Database Developer, create tables, indexes, views, stored procedures, functions ... and other permissions.
Grant creates, modifies, and deletes MySQL data table structure permissions.

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 operates MySQL foreign key permissions.

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

Grant operates the MySQL temporary table permission.

Grant create temporary tables on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL indexing permissions.

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

Grant operates the MySQL view and views the view source code permissions.

Grant CREATE view on testdb.* to developer@ ' 192.168.0.% ';
Grant Show view on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL stored procedures, function permissions.

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, can drop a procedure
Grant execute on testdb.* to developer@ ' 192.168.0.% ';

Example three: Grant Normal DBA manages the permissions of a MySQL database.

Grant all privileges on TestDB to dba@ ' localhost '
Among them, the keyword "privileges" can be omitted.

Example four: Grant Advanced DBA manages permissions for all databases in MySQL.

Grant all on *.* to dba@ ' localhost '

Instance five: MySQL grant permissions, which can be used on multiple levels.
1. Grant functions on the entire MySQL server:

Grant SELECT on *.* to Dba@localhost; --The DBA can query the tables in all databases in MySQL.
Grant all on *.* to Dba@localhost; --DBA can manage all databases in MySQL

2. Grant functions on a single database:

Grant SELECT on testdb.* to Dba@localhost; --DBAs can query the tables in TestDB.

3. Grant functions on a single datasheet:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to Dba@localhost;

Here, when you authorize multiple tables for a user, you can execute the above statements more than once. 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 ';

4. Grant acts on the columns in the table:
1
Grant Select (ID, SE, rank) on Testdb.apache_log to Dba@localhost;

5. Grant functions on stored procedures, functions:

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

Example six: View MySQL user Rights
View current user (Own) permissions:

Show grants;

To view additional MySQL user rights:
1
Show grants for Dba@localhost;

Instance VII: Revoke the permissions that have been given to the MySQL user.
Revoke is similar to Grant's syntax, simply replace the keyword "to" with "from":
1
2
Grant all on *.* to Dba@localhost;
Revoke all in *.* from Dba@localhost;

Example VIII: MySQL GRANT, REVOKE user rights considerations
1. Grant, revoke user rights, the user can only reconnect to the MySQL database, permissions to take effect.

2. If you want to allow authorized users to grant these permissions to other users, you need option "grant option"
1
Grant SELECT on testdb.* to dba@localhost with GRANT option;
This feature is generally not used. In practice, database permissions are best managed by DBAs.

*************************************************************************************************
This error is encountered with the SELECT command denied to user ' username ' ' @ ' host name ' for table ', the solution is to have the following table name authorized, that is, to authorize the core database as well.
I encountered the select command denied to user ' I ' @ '% ' for table ' proc ', which occurs when the stored procedure is invoked, as long as it is necessary to authorize the specified database, what stored procedures, functions, etc. Who knows also to put the database MySQL proc table authorization

*************************************************************************************************

The MySQL authorization form has 5 tables: User, DB, host, Tables_priv, and Columns_priv.
The contents of the authorization table are used for the following purposes:
User table
The user table lists the users and their passwords that can connect to the server, and it specifies what global (superuser) permissions they have. Any permissions that are enabled in the user table are global permissions and apply to all databases. For example, if you have the Delete permission enabled, the users listed here can delete records from any table, so think carefully before you do so.

DB table
The DB table lists the databases, and the user has permission to access them. The permissions specified here apply to all tables in a database.

Host table
The host table is used in conjunction with the DB table to control the access of a particular host to the database at a better level, which may be better than using DB alone. This table is not affected by the grant and REVOKE statements, so you may find that you are not using it at all.

Tables_priv table
The Tables_priv table specifies table-level permissions, and one of the permissions specified here applies to all columns of a table.

The

Columns_priv table
Columns_priv table Specifies column-level permissions. The permissions specified here apply to a specific column of a table.