Usage of MYSQLGrant and Revoke user permission management

This article introduces in detail the usage of MYSQLGrant and Revoke user permission management. If you need it, refer to it.

This article provides a detailed description of MYSQL Grant and Revoke user permission management usage. For more information, see.

MySQL can assign strict and complex permissions to different users. Most of these operations can be implemented using the SQL command Grant (assign permission) and Revoke (Revoke permission. Grant can assign the specified permission to a specific user. If the user does not exist, a user is created.

Common formats of Grant:

Grant permission 1, permission 2 ,... Permission n on database name. Table name to user name @ user address identified by 'Connection password ';

Permission 1, permission 2 ,... Permission n indicates 14 permissions, including select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, and file.
When permission 1, permission 2 ,... Permission n is replaced by all privileges or all, indicating that all permissions are granted to the user.
When the database name. Table name is replaced by *. *, it grants the user the permission to operate all the tables in the database on the server.
The user address can be localhost, IP address, machine name, or domain name. You can also use '%' to connect from any address.
The 'Connection password' cannot be blank; otherwise, creation fails.

Priveleges (permission) is important ).
Description
SELECT Table, columns allow users to SELECT rows (Records) from the table)
INSERT table, columns allow users to INSERT new rows in the table
UPDATE table, columns allow users to modify values in rows in the existing table
DELETE table allows you to DELETE rows in an existing table
The INDEX table allows you to create and drag a specific table INDEX.
Alter table allows you to change the structure of an existing table. For example, you can add a column, rename a column or table, and modify the Data Type of a column.
CREATE Database. Tables allow users to CREATE new databases or tables. If a specific database or table is specified in GRANT, they can only create the database or table, that is, they must first Delete (Drop) it.
Drop database, table allows users to drag (delete) database or table

Administrator permission description
Create temporary tables allows administrators to use the TEMPORARY keyword in the create table statement.
FILE allows you to read data from a FILE to a table or from a table to a FILE.
Lock tables allow the use of the lock tables statement
PROCESS allows the Administrator to view the server processes of all users
RELOAD allows the Administrator to RELOAD the authorization table, clear the authorization, host, log and table
Replication client allows the use of show status on the REPLICATION host (Master) and Slave (Slave)
Replication slave allows replication slave servers to connect to the master server
Show databases allows you to use the show databases statement to view the list of all DATABASES. Without this permission, users can only see the databases they can see.
SHUTDOWN allows the Administrator to shut down the MySQL server
SUPER allows the Administrator to close threads belonging to any user

Special Permission description
ALL (or all previleges) grant ALL Permissions
USAGE does not grant permissions. This creates a user and allows him to log on, but does not allow other operations, such as update/select.

Instance:

For example:

The Code is as follows:
Mysql> grant select, insert, update, delete on test. user to mql @ localhost identified by '000000 ′;

Grant the local user mql the select, insert, update, and delete permissions on the user table of the database test, and set the password to 123456. If the mql user does not exist, the user will be automatically created. The specific permissions can be viewed in the mysql. db table. You can also directly update the table to modify the permissions.

The Code is as follows:
Mysql> grant all privileges on test. * to mql @ localhost identified by '000000 ′;

Grant the local user mql the permission to perform all operations on all tables in the database test and set the password to 123456.

The Code is as follows:
Mysql> grant all privileges on *. * to mql @ localhost identified by '000000 ′;

Grant the local user mql the permission to perform all operations on all tables in all databases, and set the password to 123456.

The Code is as follows:
Mysql> grant all privileges on *. * to mql2@61.127.46.128 identified by '000000 ′;

User mql2 from 61.127.46.128 is assigned the permission to perform all operations on all tables of all databases, and the password is set to 123456.

REVOKE
REVOKE is opposite to GRANT in syntax format:
REVOKE privileges ON database name [. Table name] FROM user_name

For example:
Create user Bob with the password "bob", but do not grant him any permissions:

The Code is as follows:
GRANT usage on * to Bob identified by 'bob ';

Grant Bob the query and insert permissions in the books database:

The Code is as follows:
GRANT select, insert on books. * to Bob;

Cancel all Bob's permissions in the books database:

The Code is as follows:
REVOKE all on books. * from Bob;

Note: REVOKE all... Only revoking User Permissions does not delete users. In MySQL, User information is stored in mysql. User. MySQL can use drop user to completely delete a USER. Its usage is as follows:

The Code is as follows:
Drop user user_name;

For example, to delete user Bob, you can use:

The Code is as follows:
Drop user Bob;