MySQL permission assignment _ MySQL

The MySQL permission assignment system involves two concepts:

1: Authentication-> determine whether the user is allowed to connect to the database server

2: authorization-> determine whether the user has sufficient permissions to execute query requests.

Obviously, we can see from the above that, if the authentication fails, which authorization cannot be performed. Here we need to pay attention to two tables, namely the user table and the db table in the MySQL database.

The user table is unique to some extent, because it is the only table that works in the authentication and authorization phases of permission requests, it is also the only permission table for storing MySQL server-related permissions. In the authentication phase, it is only responsible for authorizing the user to access the MySQL server, and determining the maximum number of connections and the maximum number of concurrent connections per hour. in the authorization phase, the user determines whether the user authorized to access the server is granted the global permission to operate the database, and determines the maximum number of queries and updates per hour for the user.

Db tables are used to grant permissions to each user for each database. You can view the db fields.

User and permission management commands:

Create user: used to create a new user account (this command is available from version 5.0). no permissions are assigned when this user is created, after creation, you must use the grant command to assign the corresponding permissions to the user.

Eg: create user guest @ localhost identified by '201312 ';

Grant select on mydb. * to guest @ localhost;

Drop user: delete a user account (note that only accounts without any permissions can be deleted before version 4.1.1, and any accounts can be deleted after version 5.0.2)

Eg: drop user guest;

Rename user: rename a user account.

Grant: used to manage access permissions, that is, granting permissions to user accounts. Of course, it can also create a new user account.

Eg: grant select, insert, update, delete on new_db. * to guest @ '%' identified by '123 ';

Grant permission on database. table to user @ access mode identified by password

Grant select on mydb. * to guest @ localhost identified by '20140901 ';

BTW: If you need an account with a blank password or no password, you must first run the Create User command and then run

Grant. Perform the following operations:

Grant all privileges on mydb. * to visitor @ '%'; the visitor is not created in the user table of the database.

The 1133 error "Can't find any matching row in the user table" will occur ". Grant can only create

An account with a password.

Revoke: delete an account. For more information, see the MySQL documentation.