Discussion on the usage of Authorization (grant) and revocation authorization (revoke) in Mysql _mysql

The simple format that MySQL gives user permission commands can be summarized as:

Grant permission on database object to user

One, grant ordinary data user, query, insert, UPDATE, delete all table data in the database right

Grant Select "testdb.* to common_user@ '%" 
grant insert on testdb.* to common_user@ '% ' 
grant update on testdb.* t o common_user@ '% ' 

Alternatively, replace it with a MySQL command:

Grant Database Developer, create tables, indexes, views, stored procedures, functions, and so on

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  

Grant operates MySQL foreign key permissions:

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

Grant Operations MySQL temporary table permissions:

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

Grant Operation MySQL Indexing permission:

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

Grant Operations MySQL View, view View source code permissions:

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

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 in testdb.* to developer@ ' 192.168.0.% ';--now, can drop a P Rocedure 
Grant Execute    

third, Grant ordinary DBA management of a MySQL database permissions

Grant all privileges on TestDB to dba@ ' localhost '

Among them, the keyword "privileges" can be omitted.

Grant Advanced DBA manages permissions for all databases in MySQL :

MySQL grant permissions, respectively, can function 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  

2. Grant functions on a single database:

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

4. Grant acts on the columns in the table:

5. Grant functions on stored procedures, functions:

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

Vi. View MySQL User rights

View current user (Own) permissions:

To view additional MySQL user rights:

vii. revoke permissions that have been given to the MySQL user.

Revoke is similar to Grant's syntax, simply replace the keyword "to" with "from":

 Grant all on *.* to  Dba@localhost; 

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"

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

Add:
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.

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

The above article on the MySQL Authorization (grant) and Revocation of authorization (revoke) usage is small series to share all the content, hope to give you a reference, but also hope that we support the cloud habitat community.