MySQL authorization table usage example

We often encounter MySQL authorization tables when using the MySQL database. The following is an example of how to use the MySQL authorization table for your reference.

MySQL authorization table example

Grant is used to add and create permissions for users, and revoke is used to delete user permissions.
Here are some examples of using grant to add users and create permissions:

Mysql> grant all privileges on *. * to test @ localhost identified by 'test' with grant option;
Add a Super User test with all local permissions. The password is test. *. * In the ON Clause indicates "all databases, All Tables ". With grant option indicates that it has the grant permission.

Mysql> grant select, insert, update, delete, create, drop privileges on test. * to test1 @ '192. 168.1.0/255.255.255.0 'identified by 'test ';
This statement adds a test1 user with the password test, but it can only be connected from the class C subnet 192.168.1. For the test database, select, insert, update, delete, create, drop operation permission.
You do not need to manually refresh the authorization table to create permissions using the grant statement because it has been automatically refreshed.
To create permissions for users, you can also directly modify the authorization table:

Mysql> insert into user
Values ("localhost", "test", password ("test"), "Y ", "Y", "Y ");
Mysql> flush privileges;
These two statements have the same effect as the grant statement above, and a local test Super User is added. We can see that it is much more convenient to use grant, and there is no need to flush privileges.
M
Ysql> insert into user (host, user, password) values ("192.168.1.0/255.255.255.0", "test1", PASSWORD ("test ")); mysql> insert into db values ("192.168.1.0/255.255.255.0", "test", "test1", "Y ", "Y", "Y", "N", "N") mysql> flush privileges;
These three statements have the same effect as the grant statement in the above two statements. They also add a connection that can only be connected from Class C subnet 192.168.1, and have the select, insert, update, delete, create, test1 user with the drop operation permission, and the password is test. To cancel a user's permissions, use the revoke statement. The revoke syntax is very similar to the grant statement. Except to replace from with without the identified by and with grant option clauses, the following example uses revoke to delete user permissions:

Mysql> revoke all on test. * from test1 @ '192. 168.1.0/255.255.255.0 ';
This revoke revokes the permissions created by grant in the second sentence above, but the test1 user is not deleted and must be manually deleted from the user table:
Mysql> delete from user where user = 'test1 ';
Mysql> flush privileges;
In this way, the test1 user is completely deleted.
These are just simple usage of the MySQL authorization table. For more information, see the manual provided by MySQL.

Advantages and disadvantages of MySQL independent tablespace

Case sensitivity of mysql Databases

Restoration of MySQL MyISAM Table Structure

MySQL multi-Table Union query syntax example

Alternative usage of MySQL table alias