Shulou(Shulou.com)05/31 Report--

What is the principle of Mysql user authentication? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

I. the principle of user authentication

Basically, the way we implement authentication in an application is to create a user table that contains at least two fields, username and password.

Password is basically encrypted and stored. As a database, there are many restrictions on users, not just username and password as I said.

It's that simple. First of all, let's talk about access control roughly.

In information system, access control is divided into discretionary access control (DAC) and mandatory access control (MAC). When it comes to DBMS, discretionary access control is familiar to us.

GRANT,REVOKE, most databases support self-service access control. Mandatory access control is LABEL in ORACLE, and only a few systems support MAC.

Strictly speaking, login does not belong to the access control mechanism, but should belong to user identification and authentication. In Mysql, the relevant interfaces of login and DAC are implemented in

In sql_acl.cc (in fact, login is a kind of right that users have, just like CREATE SESSION in ORACLE, but login is not just a right.

Limit, and many other attributes), as you can see from the file name, ACL is ACCESS CONTROL LIST, access control list, which implements access control

The basic method. The following figure shows the entire access control process for Mysql.

The information of the user management module in Mysql is stored in the system table mysql.User, which stores not only the basic information of authorized users, but also some permissions.

information. First of all, let's take a look at the structure of the table.

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | Host | char (60) | NO | PRI | |

| | User | char (16) | NO | PRI | |

| | Password | char (41) | NO | |

| | Select_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Insert_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Update_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Delete_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Drop_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Reload_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Shutdown_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Process_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | File_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Grant_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | References_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Index_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Alter_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Show_db_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Super_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_tmp_table_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Lock_tables_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Execute_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Repl_slave_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Repl_client_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_view_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Show_view_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_routine_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Alter_routine_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_user_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Event_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Trigger_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | ssl_type | enum ('', 'ANY','X509','SPECIFIED') | NO |

| | ssl_cipher | blob | NO | | NULL |

| | x509_issuer | blob | NO | | NULL |

| | x509_subject | blob | NO | | NULL |

| | max_questions | int (11) unsigned | NO | | 0 | |

| | max_updates | int (11) unsigned | NO | | 0 | |

| | max_connections | int (11) unsigned | NO | | 0 | |

| | max_user_connections | int (11) unsigned | NO | | 0 | |

+-+ +

39 rows in set (0.01 sec)

This table contains 39 fields, and for us to log in, we should mainly use the first three fields, namely Host,User,Password.

Mysql > select Host,User,Password from user

+-+

| | Host | User | Password | |

+-+

| | localhost | root |

| | 127.0.0.1 | root |

| | localhost |

+-+

3 rows in set (0.00 sec)

This is different from the way we expected to only need a user name and password, there is an extra Host field, what does this field do?! It turns out that Mysql login authentication not only needs to verify the user name and password, but also needs to verify the connected host address, which is also to improve security. So if I want a user to log in at any address, don't I have to set up a lot of addresses? Mysql provides wildcards, and you can set the Host field to *, which means that any Host can be matched. If you look at the meaning of these three lines, the passwords of these three lines are empty. For root users, there is no need to enter a password, and the address of the client is local. The user name of the third line is empty, and the Host is localhost, which means that any local user can log in, even a user who does not exist can log in successfully, but it is limited to login and cannot be operated without other relevant permissions.

Second, tracking

In Connection Manager, it is mentioned that the login_connection function is used to check related information such as user name and password, and its source code is as follows (key function code)

Will be shaded):

Static bool login_connection (THD * thd)

{

NET * net= & thd- > net

Int error

DBUG_ENTER ("login_connection")

DBUG_PRINT ("info", ("login_connection called by thread lu")

Thd- > thread_id))

/ * Use "connect_timeout" value during connection phase * /

My_net_set_read_timeout (net, connect_timeout)

My_net_set_write_timeout (net, connect_timeout)

Error= check_connection (thd); / / here is the specific function for verification

Net_end_statement (thd)

If (error)

{/ / Wrong permissions

# ifdef _ _ NT__

If (vio_type (net- > vio) = = VIO_TYPE_NAMEDPIPE)

My_sleep (1000); / * must wait after eof () * /

# endif

Statistic_increment (aborted_connects,&LOCK_status)

DBUG_RETURN (1)

}

/ * Connect completed, set read/write timeouts back to default * /

My_net_set_read_timeout (net, thd- > variables.net_read_timeout)

My_net_set_write_timeout (net, thd- > variables.net_write_timeout)

DBUG_RETURN (0)

}

The main function of this function is to call the function check_connection for user authentication. Because the function check_connection is too long, it is simplified as follows:

Static int check_connection (THD * thd)

{

Uint connect_errors= 0

NET * net= & thd- > net

Ulong pkt_len= 0

Char * end

DBUG_PRINT ("info"

("New connection received on s", vio_description (net- > vio)

# ifdef SIGNAL_WITH_VIO_CLOSE

Thd- > set_active_vio (net- > vio)

# endif

If (! thd- > main_security_ctx.host) / / If TCP/IP connection

{

Char ip [30]

If (vio_peer_addr (net- > vio, ip, & thd- > peer_port))

{

My_error (ER_BAD_HOST_ERROR, MYF (0), thd- > main_security_ctx.host_or_ip)

Return 1

}

If (! (thd- > main_security_ctx.ip= my_strdup (ip,MYF (MY_WME)

Return 1; / * The error is set by my_strdup (). , /

Thd- > main_security_ctx.host_or_ip= thd- > main_security_ctx.ip

Vio_in_addr (net- > vio,&thd- > remote.sin_addr)

If (! (specialflag & SPECIAL_NO_RESOLVE))

{

Vio_in_addr (net- > vio,&thd- > remote.sin_addr)

Thd- > main_security_ctx.host=

Ip_to_hostname (& thd- > remote.sin_addr, & connect_errors)

/ * Cut very long hostnames to avoid possible overflows * /

If (thd- > main_security_ctx.host)

{

If (thd- > main_security_ctx.host! = my_localhost)

Thd- > main_security_ ctx.host [strlen (thd- > main_security_ctx.host)

HOSTNAME_LENGTH)] = 0

Thd- > main_security_ctx.host_or_ip= thd- > main_security_ctx.host

}

If (connect_errors > max_connect_errors)

{

My_error (ER_HOST_IS_BLOCKED, MYF (0), thd- > main_security_ctx.host_or_ip)

Return 1

}

}

...

If (acl_check_host (thd- > main_security_ctx.host, thd- > main_security_ctx.ip)) / / verify whether the hostname or IP exists here

{

My_error (ER_HOST_NOT_PRIVILEGED, MYF (0))

Thd- > main_security_ctx.host_or_ip)

Return 1

}

}

Else / * Hostname given means that the connection was on a socket * /

{

...

}

Vio_keepalive (net- > vio, TRUE)

...

Char * user= end

Char * passwd= strend (user) + 1

Uint user_len= passwd-user-1

Char * db= passwd

Char db_ buffs [name _ LEN + 1]; / / buffer to store db in utf8

Char user_ buffs [Username _ LENGTH + 1]; / / buffer to store user in utf8

Uint dummy_errors

Uint passwd_len= thd- > client_capabilities & CLIENT_SECURE_CONNECTION?

(uchar) (* passwd++): strlen (passwd)

Db= thd- > client_capabilities & CLIENT_CONNECT_WITH_DB?

Db + passwd_len + 1: 0

Uint db_len= db? Strlen (db): 0

If (passwd + passwd_len + db_len > (char *) net- > read_pos + pkt_len)

{

Inc_host_errors (& thd- > remote.sin_addr)

My_error (ER_HANDSHAKE_ERROR, MYF (0), thd- > main_security_ctx.host_or_ip)

Return 1

}

...

/ * If username starts and ends in "'", chop them off * /

If (user_len > 1 & & user [0] = ='\'& & user [user _ len-1] = ='\')

{

User [user _ len-1] = 0

User++

User_len-= 2

}

If (thd- > main_security_ctx.user)

X_free (thd- > main_security_ctx.user)

If (! (thd- > main_security_ctx.user= my_strdup (user, MYF (MY_WME)

Return 1; / * The error is set by my_strdup (). , /

Return check_user (thd, COM_CONNECT, passwd, passwd_len, db, TRUE); / / verify username and password

}

The above source code mainly does the following things:

Get the IP and hostname of the client

The acl_check_host function verifies whether there is a corresponding IP or HOST in the USER table, if there is no direct error report

Get user name and password

The check_user function verifies the user name and password (the default is ODBC if no user name is entered), and returns if there is no matching error in the system table.

Obtain the user's permission list and verify whether the relevant attributes of the user are legal, such as whether the number of connections exceeds the upper limit, whether the connection times out, and whether the operation exceeds the limit. If it is illegal, an error is returned.

As there are many things involved in the process of authentication, I can't track them one by one. I can only get a general idea of the implementation process and focus on it.

Tracking, if you are interested in children's shoes, follow them in detail.

Digression:

The permission system tables in Mysql are loaded into memory when the system starts (of course, the same is true of User tables). In general, there is no need for frequent authorization and recycling.

Operation, in this case, the permission table remains basically unchanged, and the advantage of loading it into memory when the system is started is to quickly judge the permissions and reduce the disk Imax O

You know ^ _ ^. There are advantages and disadvantages, that is, in frequent authorization and recycling related operations, the permission table needs to be reloaded into memory, Mysql in order to avoid this situation

It has been made clear in the manual that authorization and recycling will only be reflected on disk, and the data dictionary information in memory will not be changed. If you want to take effect immediately, you need to call

FLUSH PRIVILEGES system function, the work of this system function should be the RELOAD of the permission system table.

After reading the above, have you mastered the principle and method of Mysql user authentication? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.