Mysql source code learning-principle and implementation of user authentication _ MySQL

Mysql source code learning-principles and implementation of user authentication bitsCN.com

The previous sections have tracked Connection Manager and Thread Manager. in the Connection process, there is also an identity authentication process, which is familiar to everyone.

In the process of verifying the user name and password, identity authentication is often involved when we build a system. Today, let's take a look at how Mysql enters

Line verification. (Login, not login ^_^)

I. user authentication principles

We basically create a user table in the application for verification, which contains at least two fields: username and password,

Password is encrypted for storage. As a database, there are many restrictions on users, not just username and password.

This is easy. First, let's give a rough look at access control.

In information systems, access control is divided into independent access control (DAC) and mandatory access control (MAC ). As for DBMS, autonomous access control is what we are familiar.

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

Strictly speaking, logon is not an access control mechanism, but a user identification and authentication mechanism. In Mysql, the logon and DAC interfaces are implemented in

SQL _acl.cc (in fact, login is a kind of user's permissions, just like CREATE SESSION in ORACLE, but login is not just a kind of permission

Limits, but also contains many other attributes), as you can see from the file name, the ACL is the access control list, the access control list, which implements access control.

Basic method. Is the entire access control process of Mysql.

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

Information. First, let's take a look at the structure of this table.

+ ----------------------- + ----------------------------------- + ------ + ----- + --------- + ------- +

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

+ ----------------------- + ----------------------------------- + ------ + ----- + --------- + ------- +

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

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

| Password | char (41) | NO |

| Select_priv | enum ('N', 'y') | NO | N |

| Insert_priv | enum ('N', 'y') | NO | N |

| Update_priv | enum ('N', 'y') | NO | N |

| Delete_priv | enum ('N', 'y') | NO | N |

| Create_priv | enum ('N', 'y') | NO | N |

| Drop_priv | enum ('N', 'y') | NO | N |

| Reload_priv | enum ('N', 'y') | NO | N |

| Shutdown_priv | enum ('N', 'y') | NO | N |

| Process_priv | enum ('N', 'y') | NO | N |

| File_priv | enum ('N', 'y') | NO | N |

| Grant_priv | enum ('N', 'y') | NO | N |

| References_priv | enum ('N', 'y') | NO | N |

| Index_priv | enum ('N', 'y') | NO | N |

| Alter_priv | enum ('N', 'y') | NO | N |

| Show_db_priv | enum ('N', 'y') | NO | N |

| Super_priv | enum ('N', 'y') | NO | N |

| Create_tmp_table_priv | enum ('N', 'y') | NO | N |

| Lock_tables_priv | enum ('N', 'y') | NO | N |

| Execute_priv | enum ('N', 'y') | NO | N |

| Repl_slave_priv | enum ('N', 'y') | NO | N |

| Repl_client_priv | enum ('N', 'y') | NO | N |

| Create_view_priv | enum ('N', 'y') | NO | N |

| Show_view_priv | enum ('N', 'y') | NO | N |

| Create_routine_priv | enum ('N', 'y') | NO | N |

| Alter_routine_priv | enum ('N', 'y') | NO | N |

| Create_user_priv | enum ('N', 'y') | NO | N |

| Event_priv | enum ('N', 'y') | NO | N |

| Trigger_priv | enum ('N', '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. for login, we should mainly use the first three fields, Host, User, and 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)

　　

Here is a difference between the username and password as we expected. we have a Host field. what is the role of this field ?! In the past, Mysql login authentication not only required to verify the user name and password, but also to verify the connection host address, which is also to improve security. If I want a user to log on at any address, isn't it necessary to set many addresses? Mysql provides wildcards. you can set the Host field to *, which means that any Host can be matched. For more information, see the meanings of the three lines. the passwords of these three lines are empty. For root users, no password is required. the client address is the local machine. The username in the third row is blank and the Host is localhost. This indicates that any local user can log on. even a non-existent user can log on successfully, but only logon is allowed, you are not authorized to perform other operations.

II. source code tracking

In Connection Manager, the login_connection function is used to check the user name, password, and other information. the source code is as follows (the key function code

Will be colored ):

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); // the specific function is verified here

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 );

}

　　

This function is mainly used to call the check_connection function for user authentication. because the check_connection function 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 already ed on % s", vi_description (net-> vio )));

# Ifdef signal_with_vie_close

Thd-> set_active_vio (net-> vio );

# Endif

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

{

Char ip [30];

If (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, & thd-> remote. sin_addr );

If (! (Specialflag & SPECIAL_NO_RESOLVE ))

{

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 [min (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) // check whether the host name or IP address exists.

{

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 */

{

...

}

Vi_keepalive (net-> vio, TRUE );

...

Char * user = end;

Char * passwd = strend (user) + 1;

Uint user_len = passwd-user-1;

Char * db = passwd;

Char db_buff [NAME_LEN + 1]; // buffer to store db in utf8

Char user_buff [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 the user name and password

}

　　

The source code above mainly involves the following:

Obtain the client IP address and host name

The acl_check_host function verifies whether the corresponding IP address or HOST exists in the USER table. If no IP address exists, an error is reported.

Get user name and password

The check_user function verifies the user name and password (ODBC is used by default if no user name is entered). If no matching error exists in the system table, an error is returned.

Obtain the user's permission list and verify whether the user's related attributes are valid, such as whether the maximum number of connections is exceeded, whether the connection times out, and whether the operation has exceeded the limit. Otherwise, an error is returned.

As there are many things involved in the process of one authentication, I can't trace them one by one. I can only get to know the implementation process and focus on it.

Tracking. follow up the shoes you are interested in.

Digress:

In Mysql, the permission system table is loaded into the memory when the system is started (The User table is also used). In general, frequent authorization and revocation are not required.

Operation. in this case, the permission table remains unchanged. the advantage of loading the table into the memory when the system starts is to quickly perform permission judgment and reduce disk I/O,

You know ^_^. The advantage is that the permission table needs to be re-loaded into the memory when frequent authorization and revocation operations are performed. to avoid this situation, Mysql,

As you have already mentioned in the manual, authorization and reclaim will only be reflected in the disk, and the data dictionary information in the memory will not change. to take effect immediately, you need to call

Flush privileges system function, which is used to RELOAD the permission system table.

Next, I will go to a substantive introduction. by tracking a table creation statement, I will learn how Mysql stores table metadata, that is, analysis of frm format files.

Script

PS. Recently, my work has been idle, but I lost my way. I want to see the implementation of the OS for a while. I want to see the reverse, and the computer composition principle for a while.

No, the computer is amazing, and I am confused...

Excerpted with no code in mind

BitsCN.com