How to implement user management in MySQL _ MySQL

MySQL has an advanced but non-standard security authorization system. mastering its authorization mechanism is the first step to start operating MySQL databases. for a person familiar with basic SQL operations, it is also a hard part of all MySQL knowledge. This article explores the operating mechanism of its authorization system and hopes that you can better operate and use this excellent MySQL with an advanced but non-standard security/authorization system, understanding its authorization mechanism is the first step that must be taken to start operating MySQL databases. for a person familiar with basic SQL operations, it is also a hard-to-understand part of all MySQL knowledge. This article explores the operating mechanism of its authorization system and hopes that you can better operate and use this excellent database system.

This article mainly references part of chapter 6 of the user manual attached to MySQL installation. If you have any questions, please contact me (lidong@wh.027.net)

1. what is the main role of the authorization mechanism?

The basic role of the authorization mechanism is to grant users on a host the select, insert, update, and detete permissions on a database. Its additional features also include whether to allow anonymous use of databases and some specific MysQL functions, such as load data infile. Note that the username in MySQL has nothing to do with the username in Unix. Although many client programs allow you to log on with the current user name, the most standard practice is to use the -- user option.
　
2. how does the authorization mechanism work?

In MySQL, the combination of hosts and users is regarded as a unique identifier. For example, the user lee on host 1 and host 2 is actually different, and their permission to use MySQL can also be different. The core issue of the entire authorization mechanism is to grant a user logging on from a host the permission to use a database. You can use the script mysqlaccess to test the database operation permissions of users on a host. All authorization information is stored in the user, host, and db tables of the database mysql. We can connect to this database through mysql instructions, and display the content in each data table through select * from user (or db, host. The permissions granted in the user table are the basic authorization of the entire authorization mechanism. that is to say, the user definition is applicable to any user host unless otherwise defined in the db table, for users, it is best to authorize a database. The main purpose of the host table is to maintain a list of "secure" servers. When considering the permissions of a user/host on a database, we also need to study the matching search mechanism of the authorization mechanism:

Second, you can encrypt the password of a user in the authorization mechanism, and the password must be encrypted. the encryption method is password ('password'). If you enter the password directly, the database cannot be accessed. The permissions granted in the user table are the basic authorization of the entire authorization mechanism. that is to say, the user definition is applicable to any user host unless otherwise defined in the db table, for users, it is best to authorize a database. The main purpose of the host table is to maintain a list of "secure" servers. When considering the permissions of a user/host on a database, we also need to study
The following is the matching search mechanism of the authorization mechanism:

First, we need to introduce the concept of a wildcard. the wildcard includes "%", which is an arbitrary (host, user, or database). If a record is empty, it also represents any meaning. Second, you can encrypt the password of a user in the authorization mechanism, and the password must be encrypted. the encryption method is password ('password'). If you enter the password directly, the database cannot be accessed.
From the display of these three tables, we can see that each record in these three tables contains a description of a user's authorization, data tables of several related authorization mechanisms in the MySQL database are searched in the order of user, db, and host. That is to say, we will first retrieve the user data table and find the first matched record. we will call the first matched record in the user data table Priv. then we will search the db table and obtain the corresponding authorization. If the host field in the corresponding record of the db data table is null and the host in the Priv record is also included in the host field of the host table, for a user, you can add some "Y" permission settings to the host table in the user table. If the host field in the db table is not empty, the user/host authorization will not be affected.
After understanding this, we need to discuss the priority of record search in each data table, that is, how to determine the first matching record, this is not determined by the natural sequence of records in the data table. The priority of each record in each data table is listed as follows:
(1) user table: determined based on the sequence of first host and then user. The search rules are as follows: records that do not contain the unified character, including the unified character, and empty records. In the same host, the rules are arranged according to the user.
(2) db table: the retrieval order is determined by the host field: records that do not contain the unified character, records that contain the unified character, and null records.
(3) host table: the retrieval order is determined by the host field: records that do not contain the unified character, records that contain the unified character, and null records. We use the following example to describe the matching search rules: Remember that if you change these data tables, you must use mysqladmin reload to make them take effect.
The following shows how the system performs a search:
---------------------
| Host | User |...
---------------------
| % | Root |...
| % | Jeffrey |...
| Localhost | root |...
| Localhost |...
---------------------
The search order should be:
Localhost/root
Localhost/any
Any/jeffrey
Any/root
In this way, if the localhost user jeffrey wants to connect to the database, therefore, its authorization should be based on the permissions specified by localhost/"arbitrary" rather than the permissions specified by "arbitrary"/jeffrey. Please note that, this is because the improper configuration may make it impossible for you to use the database system normally.
Now let's look at an example of adding a user: you need to add a user named "custom", which is from the host 'localhost', 'server. domain 'and 'whitehouse. gov 'The connection to the database, his password is "stupid", for the database 'bankaccount' he only wants to access from "localhost, the "customer" database should be accessed by the above three hosts. Perform the following SQL statement
.

Shell> mysql.
Mysql> insert into user (host, user, password)
Values ('localhost', 'custom', password ('stupid '));
Mysql> insert into user (host, user, password)
Values ('server. domain ', 'custom', password ('stupid '));
Mysql> insert into user (host, user, password)
Values ('whitehouse. gov ', 'custom', password ('stupid '));

Mysql> insert into db
(Host, db, user, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv)
Values
('Localhost', 'bankaccount', 'custom', 'y ');
Mysql> insert into db
(Host, db, user, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv)
Values
('%', 'Customer', 'customer', 'y ');

3. authorize data tables

To authorize a data table, perform select, insert, update, and delete operations on the table, and create and drop operations on the table and database. Other authorizations include load data infile and select into outfile and management commands: shutdown, reload, refresh, and process. The structure of the three authorization DATA tables is as follows:
User table
Field type default value
Host char (60) PRI ""
User char (16) PRI ""
Password char (16 )-""
Select_priv enum ('N', 'y')-N
Insert_priv enum ('N', 'y')-N
Update_priv enum ('N', 'y')-N
Delete_priv enum ('N', 'y')-N
Create_priv enum ('N', 'y')-N
Drop_priv enum ('N', 'y')-N
Reload_priv enum ('N', 'y')-N
Shutdown_priv enum ('N', 'y')-N
Process_priv enum ('N', 'y')-N
File_priv enum ('N', 'y')-N

Db table
Field type default value
Host char (60) PRI ""
Db char (64) PRI ""
User char (16) PRI ""
Select_priv enum ('N', 'y')-N
Insert_priv enum ('N', 'y')-N
Update_priv enum ('N', 'y')-N
Delete_priv enum ('N', 'y')-N
Create_priv enum ('N', 'y')-N
Drop_priv enum ('N', 'y')-N

The host table is used only when the host is null in the db data item.
Field type default value
Host char (60) PRI ""
Db char (64) PRI ""
Select_priv enum ('N', 'y')-N
Insert_priv enum ('N', 'y')-N
Update_priv enum ('N', 'y')-N
Delete_priv enum ('N', 'y')-N
Create_priv enum ('N', 'y')-N
Drop_priv enum ('N', 'y')-N

You can use a wildcard in a data table.

4. the most common causes of Access denied errors

(1) whether you have used the mysql_install_db script to create the mySQL authorization table. you can use mysql-u root for testing. if it is correct, no error will occur. Alternatively, whether you have a file named user. ISD, which is usually located in install_dir/var/mysql/user. ISD.
(2) when you first use mysql-u root mysql to access the database or perform operations as root.
(3) have I used mysqladmin reload to update my authorization?
(4) for testing purposes, you should use the -- without-grant-tables option to start the mysqld service. you can change the content of the authorization table at this time, you can also use mysqlaccess to check whether your authorization is in place.
(5) If the password is not set using password ("password"), an error occurs. when using the-p option, note that there is no space between-ppassword.

5. how to make MySQL more secure?

(1) use a password for each MySQL User. Remember, if you do not set a password, others can
Mysql -- user other_user database is used to access your database. when you use MySQL for detection, the system will also give you a warning.
(2) do not start the MySQL service as root. MySQL can be started by any user. You can start the database service by adding a new user. This will not have any impact on the system, because MySQL Users and Unix users are fundamentally different.
(3) do not grant permissions such as 'process _ priv' and 'File _ priv' to anyone.
(4) If you do not trust your DNS, you should replace the host name with an IP address. In any case, be careful with the hostname with a peat character. The following options affect your system security:
-- Secure
As the name suggests, the system root can be secured because it can check the consistency of IP addresses. (The actual connection IP address and the resolved IP address), but this makes it difficult for mySQL to be accessed by people outside the firewall when working on the firewall.
-- Skip-grant-tables
Generally, do not use this option. this option can be used by anyone to access your system without restrictions.
-- Skip-name-resolve
Host name resolution is not performed. All host names in the authorization data table must be IP addresses or 'localhost '.
-- Skip-networking
Network connections are not allowed. All connections must pass through Unix Socket.