最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
Mysql学习――用户认证原理与实现
时间:2012-04-16 编辑:简简单单 来源:一聚教程网
验证用户名和密码的过程,我们平时做一个系统的时候,很多时候都会涉及到身份验证。今天我们就来看下Mysql是如何进
代码如下 | 复制代码 |
+-----------------------+-----------------------------------+------+-----+---------+-------+ | 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) |
代码如下 | 复制代码 |
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) |
代码如下 | 复制代码 |
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); //此处是验证的具体函数 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); } |
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[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))//此处验证主机名或IP是否存在 { 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_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);//验证用户名和密码 }
- 获取客户端的IP和主机名
- acl_check_host函数验证USER表中是否存在相应的IP或HOST,如果不存在直接报错
- 获取用户名和密码
- check_user函数验证用户名和密码(不输入用户名默认为ODBC),如果系统表中不存在匹配的报错返回
- 获取用户的权限列表,验证用户的相关属性是否合法,如连接数是否超过上限,连接是否超时,操作是否超过限制等信息,如果不合法,则报错返回。
-
上一个: mysql中百万级数据插入速度测试
相关文章
- MySQL新建用户并授权的教程 09-24
- shell指令结束mysql指定用户的所有进程 08-31
- MySQL创建新用户并授权的示例 08-22
- MySql 5.X创建用户并授权方法 05-16
- MySQL 增加新用户sql语句 12-06
- MySQL用户管理 01-24