Mysql permission Control

Mysql permission Control
Overview

Mysql permission control can be controlled in different contexts and different operation levels. They include the following:
**Management permissions allow users to manage mysql server operations. These permission control policies are global, not for a specific database.
**The database permission corresponds to a database and its objects. These permissions are used to control specific databases or global ones, and they can be applied to all databases.
**Database object permissions correspond to database tables, indexes, views, and stored procedures. Authorization control can be performed on an object in the database.

Permission storage location

**Account permission information is stored in the user, db, tables_priv, columns_priv, procs_priv table of the mysql database.
**Mysql reads the information to the memory when it starts, or re-reads the information to the memory when the permission change takes effect.

Mysql account management

The mysql account name consists of the user name and host name. This allows the same user to control permissions on different hosts separately. This section describes how to assign an account name including specific values and fuzzy values.
Matching rules: These rules are used in SQL statements such as create user, grant, and set password:
-'User _ name' @ 'host _ name' is the syntax rule of the account name.
-An account containing only the user name is equivalent to 'username' @ '%'
-If the user name is a valid character, the user name and host name do not need to be enclosed by quotation marks. When your user name contains characters such as '-', it must be enclosed by quotation marks. Or the host name has
Wildcard.
-The quotation marks can be single or double quotation marks.
-The host and user name must be enclosed in quotation marks, if required.
Mysql uses two columns in the mysql database to store the user name and Host Name:
-Each account in the User table records a row. The sub-table also indicates the global permissions of the account.
-Other authorization tables indicate the database and objects in the database owned by the account. These tables have user names and host columns. The account information in each row and the account information in the user table are refreshing.
The user name and host name can contain special characters or wildcards. The following are the rules:
The user name is either a non-null string that must match the user name specified in the connection string, or an empty string that matches the user name. An empty user name is an anonymous user.
Host names can also be in many forms, or wildcards:
-The host name can be an IP address or a localhost string.
-% Is the same as like in SQL. For example, '% .mysql.com' matches a.mysql.com.

Permission control Phase 1: Connection Verification

When you try to connect to the mysql server, the server accepts or rejects the connection based on the following two conditions:
-Your identity and correct password
-Is your account locked?
The server first checks whether the password is correct and then checks whether the password is locked. If any step fails, the server rejects the connection. Through teaching and research, the server accepts the connection and enters the second stage.
And wait for the request.
The authentication Check line checks the three columns of the user table, host, user, and password. The lock status is recorded in the account_locked column of the user table. The account lock status can be changed through alter user
Statement.
Your identity consists of the client host you connect to and your mysql user name. If the user name is not empty, it must match all characters. If the user name is empty, it matches
Any user. If the user table matches an empty user name, the user is considered to be an anonymous user, and the user is not actually provided by the customer. This means that the empty user name is used for further checks.
That is, stage 2.
The password can be blank. This is not a wildcard and does not mean it matches any password. It means that the user must be connected without a password. If a customer is authorized to use a plug-in from the service
The authentication system implemented by the plug-in may not use a password column. In this case, the external password can also be used to authenticate the mysql server.
Non-empty passwords are encrypted and mysql does not store any plaintext passwords. In addition, the password provided by the user is encrypted. The encrypted password will be checked later during the connection
No.
From the mysql point of view, the encrypted password is the real password, so do not tell anyone about the encrypted password.
If multiple rows in the user table are matched, the server must decide which one to use. The rule is as follows:
-When the server reads the user table to the memory, it will sort it.
-The servers used for Server check are checked in line order.
-The server uses the first matched row
The server sorting rule is literally at the front and unified at the back.

Permission control Phase 2: request verification

After the connection verification is passed and you establish a connection with the server, the server enters the second stage of permission control. For each of your requests, the server determines what you want to do and then checks whether you have
Permission. In this case, the privilege column of the grant table should appear. These columns can be from user, db, tables_priv, columns_priv, and procs_priv. I will not elaborate on these tables any more.

When will the permission change take effect?

When the server is started, the data in the grant table is read into the memory. If you use the account management statement to update the permission control, the server will know the changes and read them again. If you
Directly update these tables using SQL statements. Your changes will not take effect and you know that you restart the server or tell the server to recreate the cache.
You can execute a refresh permission operation to notify the server to recreate the cache. The following three statements can achieve the effect:
-Flush_privileges
Mysqladmin_flush-privileges
-Mysqladmin_reload
If the start parameter is-skip-grant-tables, the server does not verify any permissions and is insecure.