Usage of the Authorization (grant) and REVOKE authorization (REVOKE) commands in MySQL

The MySQL permissions system revolves around two concepts:
Authentication, determining whether a user is allowed to connect to a database server

Authorization, determines whether the user has sufficient permissions to execute the query request, and so on.

If the authentication is unsuccessful, then the authorization must not be carried out.

revoke is similar to Grant's syntax, just to replace the keyword "to" with "from"

Table grant and REVOKE administration permissions

Permissions	Describe
All privileges	Affects all permissions except with GRANT option
Alter	affect the use of the ALTER TABLE command
ALTER ROUTINE	Affects the ability to create stored routines
CREATE	affect the use of the CREATE TABLE command
CREATE ROUTINE	Ability to influence change and discard stored routines
CREATE Temporary TABLES	affect the use of the Create temporary table command
CREATE USER	Ability to create, discard, rename, and revoke user permissions
CREATE VIEW	affect the use of the CREATE VIEW command
DELETE	affect the use of the Delete command
DROP	affect the use of the drop TABLE command
EXECUTE	Ability to affect user run stored procedures
EVENT	Ability to influence execution events (starting with MySQL5.1.6)
FILE	Affects the use of SELECT INTO outfile and load DATA infile
GRANT OPTION	Ability to influence user delegated permissions
INDEX	affect the use of the CREATE INDEX and DROP INDEX commands
INSERT	affect the use of the Insert command
LOCK TABLES	affect the use of the lock Tables command
PROCESS	affect the use of the show Processlist command
REFERENCES	Placeholders for future MySQL features
RELOAD	affect the use of the flush command set
REPLICATION CLIENT	Ability to influence user queries from server and home server locations

Continued

Permissions	Describe
REPLICATION SLAVE	Permissions required to replicate from the server
SELECT	affect the use of the Select command
SHOW DATABASES	affect the use of the show Databases command
SHOW VIEW	affect the use of the show CREATE view command
SHUTDOWN	Affects the use of SHUTDOWN commands
SUPER	Affects the use of administrator-level commands such as change, master, KILL thread, mysqladmin debug, PURGE MASTER logs, and set GLOBAL
TRIGGER	Ability to influence execution of triggers (starting from MySQL5.1.6)
UPDATE	affect the use of the update command
USAGE	Connect only, do not grant permissions

1>. Change the table method. Your account is not allowed to log on from a remote MySQL server, only on localhost.
Workaround:
On the computer at localhost, after logging in to MySQL, change the "host" entry in the "User" table in the "MySQL" Database, and rename "%" from "localhost".
(1). mysql-u root-pvmwaremysql>use MySQL;
(2). mysql>update User Set host = '% ' where user = ' root ';
(3). Mysql>select host, user from user;

2>. Authorization law. For example, if you want to myuser use MyPassword to connect to a MySQL server from any host.
(1). Grant all privileges on *. myuser ' @ '% ' identified by ' MyPassword ' with GRANT OPTION;
(2). FLUSH privileges;
If you want to allow users to connect to the MySQL server from a host myuser IP 192.168.1.6 and use MyPassword as the password
(1). Grant all privileges on *. myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION;
(2). FLUSH privileges;
If you want to allow users to connect to the MySQL server's DK database myuser from the IP-192.168.1.6 host, and use MyPassword as the password
(1). Grant all privileges the dk.* to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION;
(3). FLUSH privileges;
Note: You must flush privileges after authorization, otherwise it will not take effect immediately.

Another method:

3> run on the machine where MySQL is installed:
1, d:mysqlbin>mysql-h localhost-u root//This should allow access to the MySQL server
2, Mysql>grant all privileges on * * to ' root ' @ '% ' with GRANT OPTION//give any host access to data
3. Mysql>flush Privileges//modification takes effect
4. Mysql>exit//exit MySQL server
This allows you to log on as root on any other host.

4> View MySQL user Rights
To view the current user (own) permissions:
Show grants;
To view additional MySQL user rights:
Show grants for [email protected];

5> Revoke permissions that have been given to MySQL user rights
Revoke is similar to Grant's syntax, just replace the keyword "to" with "from":
Grant all on * * to [email protected];
Revoke all on * * from [email protected];

6>. MySQL Grant, REVOKE user rights considerations
1. Grant, after revoke user rights, the user has to reconnect to the MySQL database for the permission to take effect.
2. If you want to allow authorized users, you can also grant these permissions to other users, you need the option "grant option"
Grant Select on testdb.* to [e-mail protected] with GRANT option;
This feature is generally not available. In practice, database permissions are best managed centrally by DBAs.

7>. User and Rights Management commands:
Create User: For creating a new account (which starts with the 5.0 version), no permissions are assigned when the user is created,
You need to assign the appropriate permissions to the change user after creation through the grant command.
eg:create user [email protected] identified by ' 123456 ';
Grant Select on mydb.* to [email protected];
Drop User: Delete a Subscriber account (note that only accounts without any permissions can be deleted before the 4.1.1 version, and any accounts may be deleted after 5.0.2)
Eg:drop user Guest;
Rename User: Can be implemented to rename a Subscriber account.
Grant: Used to administer access, that is, to authorize user accounts. Of course it can also create a new user account.
Eg:grant Select, INSERT, UPDATE, delete on new_db.* to [email protected] '% ' identified by ' 88888888 ';
Grant permissions on the database. Table to user @ Access mode identified by password
Grant Select on mydb.* to [e-mail protected] identified by ' 123456 ';
BTW: If you need a blank password or a password-free account, you must first use the Create User command, and then
Grant to assign permissions. If you do the following:
Grant all privileges the mydb.* to [email protected] '% '; The database user table is not created first visitor
User, a 1133 error will occur "can ' t find any matching row in the user table". Grant can only create
An account with a password.
Revoke: Delete an account and see the MySQL documentation for details.

8>.mysql can give you a user grant one or more of the permissions, such as Select,insert,update,delete, mainly using the grant command, the usage format is:
Grant permissions on database object to user
Grant normal data user, to query, insert, UPDATE, delete all table data in the database permissions.
Grant Select on testdb.* to [email protected] '% '
Grant insert on testdb.* to [email protected] '% '
Grant update on testdb.* to [email protected] '% '
Grant Delete on testdb.* to [email protected] '% '
Alternatively, replace it with a MySQL command:
Grant SELECT, INSERT, UPDATE, delete on testdb.* to [email protected] '% '

9>.grant database developers, creating tables, indexes, views, stored procedures, functions ... and other permissions.
Grant creates, modifies, and deletes MySQL data table structure permissions.
Grant create on testdb.* to [email protected] ' 192.168.0.% ';
Grant ALTER on testdb.* to [email protected] ' 192.168.0.% ';
Grant drop on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL foreign key permissions.
Grant references on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL temp table permissions.
Grant create temporary tables on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL index permissions.
Grant index on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates the MySQL view, viewing the view source code permissions.
Grant CREATE view on testdb.* to [email protected] ' 192.168.0.% ';
Grant Show view on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL stored procedures, function permissions.
Grant create routine on testdb.* to [email protected] ' 192.168.0.% '; --now, can show procedure status
Grant alter routine on TESTDB.* to [email protected] ' 192.168.0.% '; --now, can drop a procedure
Grant execute on testdb.* to [email protected] ' 192.168.0.% ';

10>.grant the normal DBA to manage permissions for a MySQL database.
Grant all privileges on TestDB to [email protected] ' localhost '
Where the keyword "privileges" can be omitted.

The 11>.grant advanced DBA manages permissions for all databases in MySQL.
Grant all on * * to [email protected] ' localhost '

12>. MySQL grant permissions can be used on multiple levels, respectively.
1. Grant acts on the entire MySQL server:
Grant SELECT On *. * to [email protected]; --DBAs can query tables in all databases in MySQL.
Grant all on * * to [email protected]; --DBA can manage all databases in MySQL
2. Grant acts on a single database:
Grant Select on testdb.* to [email protected]; --DBAs can query the tables in TestDB.
3. Grant acts on a single data table:
Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected];
4. Grant acts on the columns in the table:
Grant Select (ID, SE, rank) on testdb.apache_log to [email protected];
5. Grant acts on stored procedures, functions:
Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '
Grant execute on function testdb.fn_add to ' dba ' @ ' localhost '
Note: Be sure to refresh the service after modifying the permissions, or restart the service, refresh the service by: Flush privileges

The simple format that MySQL gives to user rights commands can be summarized as:
Grant permissions on database object to user

One, grant general data users, query, insert, UPDATE, delete all table data in the database permissions
Grant Select on testdb.* to [email protected] '% '
Grant insert on testdb.* to [email protected] '% '
Grant update on testdb.* to [email protected] '% '
Grant Delete on testdb.* to [email protected] '% '
Alternatively, replace it with a MySQL command:
Grant SELECT, INSERT, UPDATE, delete on testdb.* to [email protected] '% '

II. 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 [email protected] ' 192.168.0.% ';
Grant ALTER on testdb.* to [email protected] ' 192.168.0.% ';
Grant drop on testdb.* to [email protected] ' 192.168.0.% ';
Grant Operation MySQL FOREIGN key permissions:
Grant references on testdb.* to [email protected] ' 192.168.0.% ';
Grant Operation MySQL Temp table permissions:
Grant create temporary tables on testdb.* to [email protected] ' 192.168.0.% ';
Grant Operation MySQL Index permissions:
Grant index on testdb.* to [email protected] ' 192.168.0.% ';
Grant Operation MySQL View, view source code permissions:
Grant CREATE view on testdb.* to [email protected] ' 192.168.0.% ';
Grant Show view on testdb.* to [email protected] ' 192.168.0.% ';
Grant operates MySQL stored procedures, function permissions:
Grant create routine on testdb.* to [email protected] ' 192.168.0.% '; --now, can show procedure status
Grant alter routine on TESTDB.* to [email protected] ' 192.168.0.% '; --now, can drop a procedure
Grant Executeon testdb.* to [email protected] ' 192.168.0.% ';

Iii. Grant General DBA manages permissions for a MySQL database
Grant all privileges the TestDB to [email protected] ' localhost ';
Where the keyword "privileges" can be omitted.

Iv. Grant Advanced DBA manages permissions for all databases in MySQL
Grant all on * * to [email protected] ' localhost '

MySQL grant permissions can be used on multiple levels, respectively
1. Grant acts on the entire MySQL server:
Grant SELECT On *. * to [email protected]; --DBAs can query tables in all databases in MySQL
Grant all on * * to [email protected]; --DBA can manage all databases in MySQL
2. Grant acts on a single database:
Grant Select on testdb.* to [email protected]; --DBAs can query the tables in TestDB.
3. Grant acts on a single data table:
Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected];
Here, you can execute the above statement multiple times when you grant multiple tables to a user. For example:
Grant Select (user_id,username) on smp.users to [e-mail protected] '% ' identified by ' 123345 ';
Grant Select on smp.mo_sms to [email protected] '% ' identified by ' 123345 ';
4. Grant acts on the columns in the table:
Grant Select (ID, SE, rank) on testdb.apache_log to [email protected];
5. Grant acts on stored procedures, functions:
Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '
Grant execute on function testdb.fn_add to ' dba ' @ ' localhost '

Vi. Viewing MySQL user rights
To view the current user (own) permissions:
Show grants;
To view additional MySQL user rights:
Show grants for [email protected];

Vii. revoke permissions that have been granted to MySQL users
Revoke is similar to Grant's syntax, just replace the keyword "to" with "from":
Grant all on * * to [email protected];
Revoke all on * * from [email protected];

Viii. MySQL Grant, REVOKE user rights considerations
1. Grant, after revoke user rights, the user has to reconnect to the MySQL database for the permission to take effect.
2. If you want to allow authorized users, you can also grant these permissions to other users, you need the option "grant option"
Grant Select on testdb.* to [e-mail protected] with GRANT option;
This feature is generally not available. In practice, database permissions are best managed centrally by DBAs.

Ix. Supplementary
There are 5 tables in the MySQL authorization table: User, DB, host, Tables_priv, and Columns_priv, and the contents of the authorization table are used as follows:

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 on the user table are global permissions and apply to all databases. For example, if you have 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 access to the database for a particular host 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.

This article from "Sukhoi" blog, declined reprint!

Usage of the Authorization (grant) and REVOKE authorization (REVOKE) commands in MySQL