MySQL Permissions control detailed

Overview

MySQL privilege control can be controlled in different contexts and at different operational levels, including the following
* * administrative permissions allow users to manage the operation of MySQL server. These permissions control are global, not for a particular database
* * database permissions correspond to a database and objects inside. These permissions are used to control a particular database or global, and they can be applied to all databases
* * the permissions of database objects correspond to database tables, indexes, views, and stored procedures, and can be authorized to control an object within the database.

Where permissions are stored

* * the account permission information is stored in the MySQL database User,db,tables_priv, columns_priv, Procs_priv table.
* * read this information into memory when MySQL starts, or re-read to memory when the permission changes are in effect

MySQL Account management

The MySQL account name consists of a user name and a host name. This allows the same user to control permissions separately on different hosts. This section describes how to assign account names to include specific values and blur
Matching rules, which are used in SQL statements, create user, Grant, set Password:
-' user_name ' @ ' host_name ' is the grammatical rule of the account name
-Only account with username is equivalent to ' username ' @ '% '
-If the user name is a valid character, the user name and host name do not have to be quoted. When your user name contains '-' characters, you need to enclose them in quotation marks. or a host name.
The wildcard character needs to be generated.
-quotation marks can be in single quotation marks or double quotes.
-the host and user names must be enclosed in quotation marks, if necessary.
MySQL uses two columns in the MySQL database to store the user name and host name:
-The user table is recorded per account, and the second table indicates what global permissions the account has.
-The other authorization table represents the database owned by the account and the objects within the database. These tables have both a user name and a host column. The account information of each line and the account information of user table is the game.
The user name and host name can contain special characters or wildcards, and the following are the rules:
The user name is either a non-empty string literal must match the username named in the connection string, or an empty string will match the user name. An empty user name is an anonymous user.
The hostname can also be in many forms, or a wildcard character:
-the host name can be an IP address or a localhost-like string.
-% and the like in SQL have the same effect. For example '%.mysql.com ' will match a.mysql.com.

Permission Control Phase 1: Connection check

When you try to connect to a MySQL server, the server accepts or rejects the connection based on the following two-point condition:
-Your identity and the correct password
-Is your account locked?
The server verifies that the password is correct before verifying that it is locked. Any one step fails and the server rejects the connection. If through teaching, the server accepts the connection and then enters the second phase
And wait for the request.
The authentication check line examines the three columns of the user table, Host,user,password. The lock state is recorded in the account_locked column of the user table. Account lockout status can be obtained by alter user
Statement to change.
Your identity consists of two parts, the client host you are connecting to, and your MySQL user name. If the user name is not empty, then a full character match is required, and if the user name is empty, the match
Any user. If the user table matches to an empty user name, the user is considered an anonymous user, and the second is not actually provided by the customer. This means that an empty user name is used for further inspection.
which is Phase 2.
The password can be empty, this is not a wildcard and does not imply matching any password. It means that the user must be connected without a password. If the service is authorized by a customer to use a plug-in, this
The authentication system implemented by the plugin may not have a password column. In this case, the external password can also be used to authenticate the MySQL server.
Non-null passwords are stored in encrypted storage, and MySQL does not store any plaintext passwords. Also, the password provided by the user is encrypted. This encrypted password is later in the connection process to check that the password is
No, it's correct.
From the MySQL point of view, the encrypted password is the real password, so do not tell anyone this encrypted password.
If multiple rows in the user table are matched, the server must decide which one to use, and the rule is this:
-any time the server reads the user table into memory, it will sort.
-Server check servers are checked in row order
-The server uses the first matching row
The rules for server ordering are literal in front, wildcard in the back.

Permission Control Phase 2: Request checksum

When the connection is verified, after you have established a connection with the server, the server enters the second stage of permission control. Each of your requests, the server determines what you want to do, and then checks if you have
Permissions to do. At this point, the Privilege column of the grant table should be out. These columns can be derived from User,db,tables_priv,columns_priv,procs_priv. No more talking about the tables.

When to go to the authority change to take effect

When the server starts, the data for the grant table is read into memory. If you use the Account management statement to update the permissions control, the server will know the changes and then read them again. If you
To update these tables directly with the SQL statements, your changes will not take effect until you restart the server or you tell the server to rebuild the cache.
You can notify the server to rebuild the cache by performing a refresh permission action. The following three statements can achieve the effect:
-Flush_privileges
-Mysqladmin_flush-privileges
-Mysqladmin_reload
If the startup parameter is –skip-grant-tables, then the server no longer verifies any permissions and is not secure.

MySQL Permissions control detailed