From c5bfcc9d7f3c77def9a8df65da11a6b55c2edea5 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 15 May 2002 13:50:38 +0300 Subject: Features made for Schlund plus several bug fixes. Read a manual for more detail --- Docs/manual.texi | 67 ++++++++++++++++++++ include/mysql_com.h | 1 + sql/lex.h | 3 + sql/sql_acl.cc | 108 ++++++++++++++++++++++++-------- sql/sql_acl.h | 4 +- sql/sql_class.cc | 2 +- sql/sql_class.h | 2 +- sql/sql_db.cc | 20 ++++-- sql/sql_lex.h | 3 +- sql/sql_parse.cc | 175 +++++++++++++++++++++++++++++++++------------------- sql/sql_select.cc | 9 +-- sql/sql_yacc.yy | 21 ++++++- sql/structs.h | 13 ++-- 13 files changed, 316 insertions(+), 112 deletions(-) diff --git a/Docs/manual.texi b/Docs/manual.texi index cb01f82a291..6161b0997ea 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -48920,6 +48920,73 @@ Our TODO section contains what we plan to have in 4.0. @xref{TODO MySQL 4.0}. @appendixsubsec Changes in release 4.0.2 @itemize @bullet +@item +Fixed bug in DROP DATABASE with symlink +@item +Fixed bug in EXPLAIN with LIMIT offset != 0 +@item + +New feature : + +Management of user resources + +So far, the only available method of limiting user usage of MySQL +server resources has been setting max_user_connections startup +variable to some non-zero value at MySQL startup. But this method is +strictly a global one and does not allow management of individual +users, which could be of paricular interest to Interent Service +Providers. + +Therefore, management of three resources is introduced on the +individual user level : + +* number of all queries per hour +* number of all updates per hour +* number of connections made per hour + +Small clarification : By the updates in the above sense is considered +any command that changes any table or database. Queries in the above +context comprehend all commands that could be run by user. User in the +above context comprehends a single entry in user table, which is +uniquely identified by user and host columns. + +All users are by default not limited in using the above resources, +unless the limits are GRANTed to them. These limits can be granted +ONLY by global GRANT (*.*) and with a following syntax : + +GRANT ... WITH MAX_QUERIES_PER_HOUR = N1 MAX_UPDATES_PER_HOUR = N2 +MAX_CONNECTIONS_PER_HOUR = N3; + +It is not required that all three resources are specified. One or two +can be specified also. N1,N2 and N3 are intergers and should limit +number of times user can execute any command, update command or can +login that many times per hour. + +If user reaches any of the above limits withing one hour, his +connection will be broken or refused and the appropriate error message +shall be issued. + +Current values of particular user resources can be flushed (set to +zero) by issuing a grant statement with any of the above limiting +clauses, including a GRANT statement with current value(s) of tha +resource(s). + +Also, current values for all users will be flushed if privileges are +reloaded or if a new flush command is issued : + +flush user_resources. + +Also, current values for all users will be flushed with mysqladmin +reload command. + +This new feature is enabled as soon as single user is GRANTed with +some of the limiting GRANT clauses. + +As a prerequisite for enabling this features, user table in mysql +database must have the additional columns, just as defined in table +creation scripts mysql_install_db and mysql_install_db.sh in scripts/ +directory. + @item New configure option --without-query-cache. @item diff --git a/include/mysql_com.h b/include/mysql_com.h index 0e54c0e992b..c30eb30f779 100644 --- a/include/mysql_com.h +++ b/include/mysql_com.h @@ -81,6 +81,7 @@ enum enum_server_command {COM_SLEEP,COM_QUIT,COM_INIT_DB,COM_QUERY, #define REFRESH_QUERY_CACHE 65536 #define REFRESH_QUERY_CACHE_FREE 0x20000L /* pack query cache */ #define REFRESH_DES_KEY_FILE 0x40000L +#define REFRESH_USER_RESOURCES 0x80000L #define CLIENT_LONG_PASSWORD 1 /* new more secure passwords */ #define CLIENT_FOUND_ROWS 2 /* Found instead of affected rows */ diff --git a/sql/lex.h b/sql/lex.h index 8c7beb64b9b..e03c4db6479 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -229,6 +229,8 @@ static SYMBOL symbols[] = { { "MASTER_USER", SYM(MASTER_USER_SYM),0,0}, { "MAX_ROWS", SYM(MAX_ROWS),0,0}, { "MAX_QUERIES_PER_HOUR", SYM(MAX_QUERIES_PER_HOUR), 0,0}, + { "MAX_UPDATES_PER_HOUR", SYM(MAX_UPDATES_PER_HOUR), 0,0}, + { "MAX_CONNECTIONS_PER_HOUR", SYM(MAX_CONNECTIONS_PER_HOUR), 0,0}, { "MATCH", SYM(MATCH),0,0}, { "MEDIUMBLOB", SYM(MEDIUMBLOB),0,0}, { "MEDIUMTEXT", SYM(MEDIUMTEXT),0,0}, @@ -290,6 +292,7 @@ static SYMBOL symbols[] = { { "REPEATABLE", SYM(REPEATABLE_SYM),0,0}, { "REQUIRE", SYM(REQUIRE_SYM),0,0}, { "RESET", SYM(RESET_SYM),0,0}, + { "USER_RESOURCES", SYM(RESOURCES),0,0}, { "RESTORE", SYM(RESTORE_SYM),0,0}, { "RESTRICT", SYM(RESTRICT),0,0}, { "RETURNS", SYM(UDF_RETURNS_SYM),0,0}, diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 104b431bdbb..4893028526f 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -58,7 +58,8 @@ class ACL_USER :public ACL_ACCESS { public: acl_host_and_ip host; - uint hostname_length, questions, updates; + uint hostname_length; + USER_RESOURCES user_resource; char *user,*password; ulong salt[2]; #ifdef HAVE_OPENSSL @@ -110,6 +111,32 @@ static void update_hostname(acl_host_and_ip *host, const char *hostname); static bool compare_hostname(const acl_host_and_ip *host, const char *hostname, const char *ip); +extern char uc_update_queries[SQLCOM_END]; + +static void init_update_queries(void) +{ + uc_update_queries[SQLCOM_CREATE_TABLE]=1; + uc_update_queries[SQLCOM_CREATE_INDEX]=1; + uc_update_queries[SQLCOM_ALTER_TABLE]=1; + uc_update_queries[SQLCOM_UPDATE]=1; + uc_update_queries[SQLCOM_INSERT]=1; + uc_update_queries[SQLCOM_INSERT_SELECT]=1; + uc_update_queries[SQLCOM_DELETE]=1; + uc_update_queries[SQLCOM_TRUNCATE]=1; + uc_update_queries[SQLCOM_DROP_TABLE]=1; + uc_update_queries[SQLCOM_LOAD]=1; + uc_update_queries[SQLCOM_CREATE_DB]=1; + uc_update_queries[SQLCOM_DROP_DB]=1; + uc_update_queries[SQLCOM_REPLACE]=1; + uc_update_queries[SQLCOM_REPLACE_SELECT]=1; + uc_update_queries[SQLCOM_RENAME_TABLE]=1; + uc_update_queries[SQLCOM_BACKUP_TABLE]=1; + uc_update_queries[SQLCOM_RESTORE_TABLE]=1; + uc_update_queries[SQLCOM_DELETE_MULTI]=1; + uc_update_queries[SQLCOM_DROP_INDEX]=1; + uc_update_queries[SQLCOM_MULTI_UPDATE]=1; +} + int acl_init(bool dont_read_acl_tables) { THD *thd; @@ -247,14 +274,16 @@ int acl_init(bool dont_read_acl_tables) { /* Table has new MySQL usage limits */ char *ptr = get_field(&mem, table, 21); - user.questions=atoi(ptr); + user.user_resource.questions=atoi(ptr); ptr = get_field(&mem, table, 22); - user.updates=atoi(ptr); - if (user.questions) + user.user_resource.updates=atoi(ptr); + ptr = get_field(&mem, table, 23); + user.user_resource.connections=atoi(ptr); + if (user.user_resource.questions || user.user_resource.updates || user.user_resource.connections) mqh_used=1; } else - user.questions=user.updates=0; + bzero(&(user.user_resource),sizeof(user.user_resource)); #ifndef TO_BE_REMOVED if (table->fields <= 13) { // Without grant @@ -299,6 +328,7 @@ int acl_init(bool dont_read_acl_tables) init_check_host(); mysql_unlock_tables(thd, lock); + init_update_queries(); thd->version--; // Force close to free memory close_thread_tables(thd); delete thd; @@ -442,13 +472,13 @@ static int acl_compare(ACL_ACCESS *a,ACL_ACCESS *b) uint acl_getroot(THD *thd, const char *host, const char *ip, const char *user, const char *password,const char *message,char **priv_user, - bool old_ver, uint *max_questions) + bool old_ver, USER_RESOURCES *mqh) { uint user_access=NO_ACCESS; *priv_user=(char*) user; char *ptr=0; - *max_questions=0; + bzero(mqh,sizeof(USER_RESOURCES)); if (!initialized) return (uint) ~NO_ACCESS; // If no data allow anything /* purecov: tested */ VOID(pthread_mutex_lock(&acl_cache->lock)); @@ -556,7 +586,7 @@ uint acl_getroot(THD *thd, const char *host, const char *ip, const char *user, #else /* HAVE_OPENSSL */ user_access=acl_user->access; #endif /* HAVE_OPENSSL */ - *max_questions=acl_user->questions; + *mqh=acl_user->user_resource; if (!acl_user->user) *priv_user=(char*) ""; // Change to anonymous user /* purecov: inspected */ break; @@ -590,7 +620,7 @@ static void acl_update_user(const char *user, const char *host, const char *ssl_cipher, const char *x509_issuer, const char *x509_subject, - unsigned int mqh, + USER_RESOURCES *mqh, uint privileges) { for (uint i=0 ; i < acl_users.elements ; i++) @@ -604,8 +634,8 @@ static void acl_update_user(const char *user, const char *host, acl_user->host.hostname && !strcmp(host,acl_user->host.hostname)) { acl_user->access=privileges; - acl_user->questions=mqh; -#ifdef HAVE_OPENSSL + acl_user->user_resource=*mqh; +#ifdef HAVE_OPENSSL acl_user->ssl_type=ssl_type; acl_user->ssl_cipher=ssl_cipher; acl_user->x509_issuer=x509_issuer; @@ -634,7 +664,7 @@ static void acl_insert_user(const char *user, const char *host, const char *ssl_cipher, const char *x509_issuer, const char *x509_subject, - unsigned int mqh, + USER_RESOURCES *mqh, uint privileges) { ACL_USER acl_user; @@ -642,7 +672,7 @@ static void acl_insert_user(const char *user, const char *host, update_hostname(&acl_user.host,strdup_root(&mem,host)); acl_user.password=0; acl_user.access=privileges; - acl_user.questions=mqh; + acl_user.user_resource = *mqh; acl_user.sort=get_sort(2,acl_user.host.hostname,acl_user.user); acl_user.hostname_length=(uint) strlen(acl_user.host.hostname); #ifdef HAVE_OPENSSL @@ -1151,7 +1181,14 @@ static int replace_user_table(THD *thd, TABLE *table, const LEX_USER &combo, DBUG_ENTER("replace_user_table"); if (combo.password.str && combo.password.str[0]) + { + if (combo.password.length <= HASH_PASSWORD_LENGTH) + { + send_error(&thd->net, ER_PASSWORD_NO_MATCH); + DBUG_RETURN(1); + } password=combo.password.str; + } else { password=empty_string; @@ -1233,10 +1270,16 @@ static int replace_user_table(THD *thd, TABLE *table, const LEX_USER &combo, } } #endif /* HAVE_OPENSSL */ - if (table->fields >= 23 && thd->lex.mqh) + if (table->fields >= 23) { - table->field[21]->store((longlong) thd->lex.mqh); - mqh_used=1; + USER_RESOURCES mqh = thd->lex.mqh; + if (mqh.questions) + table->field[21]->store((longlong) mqh.questions); + if (mqh.updates) + table->field[22]->store((longlong) mqh.updates); + if (mqh.connections) + table->field[23]->store((longlong) mqh.connections); + mqh_used = mqh_used || mqh.questions || mqh.updates || mqh.connections; } if (old_row_exists) { @@ -1276,7 +1319,7 @@ end: thd->lex.ssl_cipher, thd->lex.x509_issuer, thd->lex.x509_subject, - thd->lex.mqh, + &thd->lex.mqh, rights); else acl_insert_user(combo.user.str,combo.host.str,password, @@ -1284,7 +1327,7 @@ end: thd->lex.ssl_cipher, thd->lex.x509_issuer, thd->lex.x509_subject, - thd->lex.mqh, + &thd->lex.mqh, rights); } table->file->index_end(); @@ -2691,11 +2734,25 @@ int mysql_show_grants(THD *thd,LEX_USER *lex_user) #endif /* HAVE_OPENSSL */ if (want_access & GRANT_ACL) global.append(" WITH GRANT OPTION",18); - else if (acl_user->questions) + if (acl_user->user_resource.questions) { char buff[65], *p; // just as in int2str global.append(" WITH MAX_QUERIES_PER_HOUR = ",29); - p=int2str(acl_user->questions,buff,10); + p=int2str(acl_user->user_resource.questions,buff,10); + global.append(buff,p-buff); + } + if (acl_user->user_resource.updates) + { + char buff[65], *p; // just as in int2str + global.append(" WITH MAX_UPDATES_PER_HOUR = ",29); + p=int2str(acl_user->user_resource.updates,buff,10); + global.append(buff,p-buff); + } + if (acl_user->user_resource.connections) + { + char buff[65], *p; // just as in int2str + global.append(" WITH MAX_CONNECTIONS_PER_HOUR = ",33); + p=int2str(acl_user->user_resource.connections,buff,10); global.append(buff,p-buff); } thd->packet.length(0); @@ -2860,16 +2917,17 @@ int mysql_show_grants(THD *thd,LEX_USER *lex_user) } -uint get_mqh(const char *user, const char *host) +void get_mqh(const char *user, const char *host, USER_CONN *uc) { - if (!initialized) return 0; - ACL_USER *acl_user; - acl_user= find_acl_user(host,user); - return (acl_user) ? acl_user->questions : 0; + if (initialized && (acl_user= find_acl_user(host,user))) + uc->user_resources= acl_user->user_resource; + else + bzero((char*) &uc->user_resources, sizeof(uc->user_resources)); } + /***************************************************************************** ** Instantiate used templates *****************************************************************************/ diff --git a/sql/sql_acl.h b/sql/sql_acl.h index f118ac17789..9ac3bc6ed74 100644 --- a/sql/sql_acl.h +++ b/sql/sql_acl.h @@ -61,7 +61,7 @@ uint acl_get(const char *host, const char *ip, const char *bin_ip, const char *user, const char *db); uint acl_getroot(THD *thd, const char *host, const char *ip, const char *user, const char *password,const char *scramble,char **priv_user, - bool old_ver, uint *max); + bool old_ver, USER_RESOURCES *max); bool acl_check_host(const char *host, const char *ip); bool change_password(THD *thd, const char *host, const char *user, char *password); @@ -82,4 +82,4 @@ bool check_grant_db(THD *thd,const char *db); uint get_table_grant(THD *thd, TABLE_LIST *table); uint get_column_grant(THD *thd, TABLE_LIST *table, Field *field); int mysql_show_grants(THD *thd, LEX_USER *user); -uint get_mqh(const char *user, const char *host); +void get_mqh(const char *user, const char *host, USER_CONN *uc); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 03bb8ae2c97..c332181b410 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -147,7 +147,7 @@ THD::THD():user_time(0),fatal_error(0),last_insert_id_used(0), /* Initialize sub structures */ bzero((char*) &mem_root,sizeof(mem_root)); bzero((char*) &transaction.mem_root,sizeof(transaction.mem_root)); - user_connect=(UC *)0; + user_connect=(USER_CONN *)0; hash_init(&user_vars, USER_VARS_HASH_SIZE, 0, 0, (hash_get_key) get_var_key, (void (*)(void*)) free_var,0); diff --git a/sql/sql_class.h b/sql/sql_class.h index e755deeea61..31da5c53bf8 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -382,7 +382,7 @@ public: ha_rows select_limit,offset_limit,default_select_limit,cuted_fields, max_join_size, sent_row_count, examined_row_count; table_map used_tables; - UC *user_connect; + USER_CONN *user_connect; ulong query_id,version, inactive_timeout,options,thread_id; long dbug_thread_id; pthread_t real_id; diff --git a/sql/sql_db.cc b/sql/sql_db.cc index 9f521ac5ffd..9198cb4ba82 100644 --- a/sql/sql_db.cc +++ b/sql/sql_db.cc @@ -283,13 +283,20 @@ static long mysql_rm_known_files(THD *thd, MY_DIR *dirp, const char *db, */ if (!found_other_files) { - char tmp_path[FN_REFLEN]; + char tmp_path[FN_REFLEN], *pos; char *path=unpack_filename(tmp_path,org_path); #ifdef HAVE_READLINK - int linkcount = readlink(path,filePath,sizeof(filePath)-1); - if (linkcount > 0) // If the path was a symbolic link + int error; + + /* Remove end FN_LIBCHAR as this causes problem on Linux in readlink */ + pos=strend(path); + if (pos > path && pos[-1] == FN_LIBCHAR) + *--pos=0; + + if ((error=my_readlink(filePath, path, MYF(MY_WME))) < 0) + DBUG_RETURN(-1); + if (!error) { - *(filePath + linkcount) = '\0'; if (my_delete(path,MYF(!level ? MY_WME : 0))) { /* Don't give errors if we can't delete 'RAID' directory */ @@ -297,11 +304,12 @@ static long mysql_rm_known_files(THD *thd, MY_DIR *dirp, const char *db, DBUG_RETURN(deleted); DBUG_RETURN(-1); } - path=filePath; + /* Delete directory symbolic link pointed at */ + path= filePath; } #endif /* Remove last FN_LIBCHAR to not cause a problem on OS/2 */ - char *pos=strend(path); + pos=strend(path); if (pos > path && pos[-1] == FN_LIBCHAR) *--pos=0; /* Don't give errors if we can't delete 'RAID' directory */ diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 6961ab8c712..8713e8f0be7 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -176,6 +176,7 @@ typedef struct st_lex { HA_CHECK_OPT check_opt; // check/repair options HA_CREATE_INFO create_info; LEX_MASTER_INFO mi; // used by CHANGE MASTER + USER_RESOURCES mqh; ulong thread_id,type; enum_sql_command sql_command; enum lex_states next_state; @@ -184,7 +185,7 @@ typedef struct st_lex { enum enum_ha_read_modes ha_read_mode; enum ha_rkey_function ha_rkey_mode; enum enum_enable_or_disable alter_keys_onoff; - uint grant,grant_tot_col,which_columns, union_option, mqh; + uint grant,grant_tot_col,which_columns, union_option; thr_lock_type lock_option; bool drop_primary,drop_if_exists,local_file; bool in_comment,ignore_space,verbose,simple_alter, option_type; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index dc89888a1a5..af68c1bb9f3 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -59,9 +59,9 @@ extern "C" pthread_mutex_t THR_LOCK_keycache; extern "C" int gethostname(char *name, int namelen); #endif -static int check_for_max_user_connections(UC *uc); +static int check_for_max_user_connections(USER_CONN *uc); static bool check_mqh(THD *thd); -static void decrease_user_connections(UC *uc); +static void decrease_user_connections(USER_CONN *uc); static bool check_db_used(THD *thd,TABLE_LIST *tables); static bool check_merge_table_access(THD *thd, char *db, TABLE_LIST *tables); static bool check_dup(const char *db, const char *name, TABLE_LIST *tables); @@ -126,18 +126,19 @@ extern pthread_mutex_t LOCK_user_conn; static int get_or_create_user_conn(THD *thd, const char *user, const char *host, - uint max_questions) + USER_RESOURCES *mqh) { int return_val=0; - uint temp_len; + uint temp_len, user_len, host_len; char temp_user[USERNAME_LENGTH+HOSTNAME_LENGTH+2]; struct user_conn *uc; DBUG_ASSERT(user != 0); DBUG_ASSERT(host != 0); - temp_len= (uint) (strxnmov(temp_user, sizeof(temp_user)-1, user, "@", host, - NullS) - temp_user); + user_len=strlen(user); + host_len=strlen(host); + temp_len= (strmov(strmov(temp_user, user)+1, host) - temp_user)+1; (void) pthread_mutex_lock(&LOCK_user_conn); if (!(uc = (struct user_conn *) hash_search(&hash_user_connections, (byte*) temp_user, temp_len))) @@ -153,10 +154,14 @@ static int get_or_create_user_conn(THD *thd, const char *user, } uc->user=(char*) (uc+1); memcpy(uc->user,temp_user,temp_len+1); + uc->user_len= user_len; + uc->host=uc->user + uc->user_len + 1; uc->len = temp_len; - uc->connections = 1; - uc->questions=0; - uc->max_questions=max_questions; + uc->connections = 1; + uc->questions=uc->updates=uc->conn_per_hour=0; + uc->user_resources=*mqh; + if (mqh->connections > max_user_connections) + uc->user_resources.connections = max_user_connections; uc->intime=thd->thr_create_time; if (hash_insert(&hash_user_connections, (byte*) uc)) { @@ -184,9 +189,9 @@ static bool check_user(THD *thd,enum_server_command command, const char *user, const char *passwd, const char *db, bool check_count) { NET *net= &thd->net; - uint max_questions=0; thd->db=0; thd->db_length=0; + USER_RESOURCES ur; if (!(thd->user = my_strdup(user, MYF(0)))) { @@ -197,7 +202,7 @@ static bool check_user(THD *thd,enum_server_command command, const char *user, passwd, thd->scramble, &thd->priv_user, protocol_version == 9 || !(thd->client_capabilities & - CLIENT_LONG_PASSWORD),&max_questions); + CLIENT_LONG_PASSWORD),&ur); DBUG_PRINT("info", ("Capabilities: %d packet_length: %d Host: '%s' User: '%s' Using password: %s Access: %u db: '%s'", thd->client_capabilities, thd->max_packet_length, @@ -237,9 +242,9 @@ static bool check_user(THD *thd,enum_server_command command, const char *user, db ? db : (char*) ""); thd->db_access=0; /* Don't allow user to connect if he has done too many queries */ - if ((max_questions || max_user_connections) && get_or_create_user_conn(thd,user,thd->host_or_ip,max_questions)) + if ((ur.questions || ur.updates || ur.connections) && get_or_create_user_conn(thd,user,thd->host_or_ip,&ur)) return -1; - if (max_user_connections && thd->user_connect && + if (thd->user_connect && thd->user_connect->user_resources.connections && check_for_max_user_connections(thd->user_connect)) return -1; if (db && db[0]) @@ -279,32 +284,43 @@ void init_max_user_conn(void) } -static int check_for_max_user_connections(UC *uc) +static int check_for_max_user_connections(USER_CONN *uc) { int error=0; DBUG_ENTER("check_for_max_user_connections"); - if (max_user_connections <= (uint) uc->connections) + if (max_user_connections && ( max_user_connections <= (uint) uc->connections)) { net_printf(&(current_thd->net),ER_TOO_MANY_USER_CONNECTIONS, uc->user); error=1; goto end; } uc->connections++; - +if (uc->user_resources.connections && uc->conn_per_hour++ >= uc->user_resources.connections) + { + net_printf(¤t_thd->net, ER_USER_LIMIT_REACHED, uc->user, "max_connections", + (long) uc->user_resources.connections); + error=1; + goto end; + } end: DBUG_RETURN(error); } -static void decrease_user_connections(UC *uc) +static void decrease_user_connections(USER_CONN *uc) { - if (!max_user_connections) +/* if (!max_user_connections) return; +*/ DBUG_ENTER("decrease_user_connections"); - - if (!--uc->connections && !mqh_used) + if (mqh_used) + { + if (uc->conn_per_hour) + uc->conn_per_hour--; + } + else if (!--uc->connections) { /* Last connection for user; Delete it */ (void) pthread_mutex_lock(&LOCK_user_conn); @@ -325,70 +341,92 @@ void free_max_user_conn(void) Check if maximum queries per hour limit has been reached returns 0 if OK. - In theory we would need a mutex in the UC structure for this to be 100 % + In theory we would need a mutex in the USER_CONN structure for this to be 100 % safe, but as the worst scenario is that we would miss counting a couple of queries, this isn't critical. */ +char uc_update_queries[SQLCOM_END]; + static bool check_mqh(THD *thd) { bool error=0; DBUG_ENTER("check_mqh"); - UC *uc=thd->user_connect; + USER_CONN *uc=thd->user_connect; DBUG_ASSERT(uc != 0); + uint check_command = thd->lex.sql_command; + - bool my_start = thd->start_time != 0; - time_t check_time = (my_start) ? thd->start_time : time(NULL); - if (check_time - uc->intime >= 3600) + if (check_command < (uint) SQLCOM_END) { - (void) pthread_mutex_lock(&LOCK_user_conn); - uc->questions=1; - uc->intime=check_time; - (void) pthread_mutex_unlock(&LOCK_user_conn); + if (uc->user_resources.updates && uc_update_queries[check_command] && + ++(uc->updates) > uc->user_resources.updates) + { + net_printf(&thd->net, ER_USER_LIMIT_REACHED, uc->user, "max_updates", + (long) uc->user_resources.updates); + error=1; + goto end; + } } - else if (uc->max_questions && ++(uc->questions) > uc->max_questions) + else { - net_printf(&thd->net, ER_USER_LIMIT_REACHED, uc->user, "max_questions", - (long) uc->max_questions); - error=1; - goto end; + bool my_start = thd->start_time != 0; + time_t check_time = (my_start) ? thd->start_time : time(NULL); + + if (check_time - uc->intime >= 3600) + { + (void) pthread_mutex_lock(&LOCK_user_conn); + uc->questions=1; + uc->updates=0; + uc->conn_per_hour=0; + uc->intime=check_time; + (void) pthread_mutex_unlock(&LOCK_user_conn); + } + else if (uc->user_resources.questions && ++(uc->questions) > uc->user_resources.questions) + { + net_printf(&thd->net, ER_USER_LIMIT_REACHED, uc->user, "max_questions", + (long) uc->user_resources.questions); + error=1; + goto end; + } } - end: DBUG_RETURN(error); } -static void reset_mqh(THD *thd, LEX_USER *lu, uint mq) +static void reset_mqh(THD *thd, LEX_USER *lu, USER_RESOURCES *mqh, bool get_them=false) { (void) pthread_mutex_lock(&LOCK_user_conn); if (lu) // for GRANT { - UC *uc; + USER_CONN *uc; uint temp_len=lu->user.length+lu->host.length+2; char temp_user[USERNAME_LENGTH+HOSTNAME_LENGTH+2]; memcpy(temp_user,lu->user.str,lu->user.length); memcpy(temp_user+lu->user.length+1,lu->host.str,lu->host.length); - temp_user[lu->user.length]=temp_user[temp_len-1]=0; + temp_user[lu->user.length]='\0'; temp_user[temp_len-1]=0; if ((uc = (struct user_conn *) hash_search(&hash_user_connections, - (byte*) temp_user, temp_len))) + (byte*) temp_user, temp_len-1))) { uc->questions=0; - uc->max_questions=mq; + uc->user_resources=*mqh; + uc->updates=0; + uc->conn_per_hour=0; } } - else // for FLUSH PRIVILEGES + else // for FLUSH PRIVILEGES and FLUSH USER_RESOURCES { for (uint idx=0;idx < hash_user_connections.records; idx++) { - char user[USERNAME_LENGTH+1]; - char *where; - UC *uc=(struct user_conn *) hash_element(&hash_user_connections, idx); - where=strchr(uc->user,'@'); - strmake(user,uc->user,where - uc->user); - uc->max_questions=get_mqh(user,where+1); + USER_CONN *uc=(struct user_conn *) hash_element(&hash_user_connections, idx); + if (get_them) + get_mqh(uc->user,uc->host,uc); + uc->questions=0; + uc->updates=0; + uc->conn_per_hour=0; } } (void) pthread_mutex_unlock(&LOCK_user_conn); @@ -708,7 +746,7 @@ pthread_handler_decl(handle_bootstrap,arg) thd->query= thd->memdup_w_gap(buff, length+1, thd->db_length+1); thd->query[length] = '\0'; thd->query_id=query_id++; - if (thd->user_connect && check_mqh(thd)) + if (mqh_used && thd->user_connect && check_mqh(thd)) { thd->net.error = 0; close_thread_tables(thd); // Free tables @@ -895,7 +933,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd, char *save_user= thd->user; char *save_priv_user= thd->priv_user; char *save_db= thd->db; - UC *save_uc= thd->user_connect; + USER_CONN *save_uc= thd->user_connect; if ((uint) ((uchar*) db - net->read_pos) > packet_length) { // Check if protocol is ok @@ -948,7 +986,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd, my_pthread_setprio(pthread_self(),QUERY_PRIOR); mysql_log.write(thd,command,"%s",thd->query); DBUG_PRINT("query",("%s",thd->query)); - if (thd->user_connect && check_mqh(thd)) + if (mqh_used && thd->user_connect && check_mqh(thd)) { error = TRUE; // Abort client net->error = 0; // Don't give abort message @@ -1073,8 +1111,6 @@ bool dispatch_command(enum enum_server_command command, THD *thd, send_error(net,0); else send_eof(net); - if (mqh_used) - reset_mqh(thd,(LEX_USER *) NULL, 0); break; } case COM_SHUTDOWN: @@ -2316,12 +2352,12 @@ mysql_execute_command(void) Query_log_event qinfo(thd, thd->query); mysql_bin_log.write(&qinfo); } - if (mqh_used && lex->mqh) + if (mqh_used && (lex->mqh.questions || lex->mqh.updates || lex->mqh.connections) && lex->sql_command == SQLCOM_GRANT) { List_iterator str_list(lex->users_list); LEX_USER *user; while ((user=str_list++)) - reset_mqh(thd,user,lex->mqh); + reset_mqh(thd,user,&(lex->mqh)); } } } @@ -2727,8 +2763,15 @@ mysql_parse(THD *thd,char *inBuf,uint length) LEX *lex=lex_start(thd, (uchar*) inBuf, length); if (!yyparse() && ! thd->fatal_error) { - mysql_execute_command(); - query_cache_end_of_result(&thd->net); + if (mqh_used && thd->user_connect && check_mqh(thd)) + { + thd->net.error = 0; + } + else + { + mysql_execute_command(); + query_cache_end_of_result(&thd->net); + } } else query_cache_abort(&thd->net); @@ -3272,6 +3315,8 @@ bool reload_acl_and_cache(THD *thd, ulong options, TABLE_LIST *tables) { acl_reload(); grant_reload(); + if (mqh_used) + reset_mqh(thd,(LEX_USER *) NULL, 0, true); } if (options & REFRESH_LOG) { @@ -3318,14 +3363,16 @@ bool reload_acl_and_cache(THD *thd, ulong options, TABLE_LIST *tables) result=load_des_key_file(des_key_file); } #endif - if (options & REFRESH_SLAVE) - { - LOCK_ACTIVE_MI; - if (reset_slave(active_mi)) - result=1; - UNLOCK_ACTIVE_MI; - } - return result; + if (options & REFRESH_SLAVE) + { + LOCK_ACTIVE_MI; + if (reset_slave(active_mi)) + result=1; + UNLOCK_ACTIVE_MI; + } + if (options & REFRESH_USER_RESOURCES) + reset_mqh(thd,(LEX_USER *) NULL, 0); + return result; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 18f256d9edb..94d89ffe05e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -501,8 +501,8 @@ mysql_select(THD *thd,TABLE_LIST *tables,List &fields,COND *conds, select_distinct=0; } else if (select_distinct && join.tables - join.const_tables == 1 && - (thd->select_limit == HA_POS_ERROR || - (join.select_options & OPTION_FOUND_ROWS) || + ((thd->select_limit == HA_POS_ERROR || + (join.select_options & OPTION_FOUND_ROWS)) && order && !(skip_sort_order= test_if_skip_sort_order(&join.join_tab[join.const_tables], @@ -2363,7 +2363,7 @@ make_simple_join(JOIN *join,TABLE *tmp_table) join->send_records=(ha_rows) 0; join->group=0; join->do_send_rows = 1; - join->row_limit=HA_POS_ERROR; + join->row_limit=join->thd->select_limit; join_tab->cache.buff=0; /* No cacheing */ join_tab->table=tmp_table; @@ -4899,7 +4899,7 @@ end_send(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), JOIN_TAB *jt=join->join_tab; if ((join->tables == 1) && !join->tmp_table && !join->sort_and_group && !join->send_group_parts && !join->having && !jt->select_cond && - !(jt->table->file->table_flags() & HA_NOT_EXACT_COUNT)) + !(jt->table->file->table_flags() & HA_NOT_EXACT_COUNT) && (jt->records < INT_MAX32)) { /* Join over all rows in table; Return number of found rows */ join->select_options ^= OPTION_FOUND_ROWS; @@ -6990,6 +6990,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, /* Don't log this into the slow query log */ select_lex->options&= ~(QUERY_NO_INDEX_USED | QUERY_NO_GOOD_INDEX_USED); + thd->offset_limit=0; if (thd->lex.select == select_lex) { field_list.push_back(new Item_empty_string("table",NAME_LEN)); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 8012768e508..ea1a227750e 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -245,7 +245,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token RELAY_LOG_POS_SYM %token MATCH %token MAX_ROWS +%token MAX_CONNECTIONS_PER_HOUR %token MAX_QUERIES_PER_HOUR +%token MAX_UPDATES_PER_HOUR %token MEDIUM_SYM %token MERGE_SYM %token MIN_ROWS @@ -289,6 +291,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token RENAME %token REPEATABLE_SYM %token REQUIRE_SYM +%token RESOURCES %token RESTORE_SYM %token RESTRICT %token REVOKE @@ -2714,6 +2717,7 @@ flush_option: | SLAVE { Lex->type|= REFRESH_SLAVE; } | MASTER_SYM { Lex->type|= REFRESH_MASTER; } | DES_KEY_FILE { Lex->type|= REFRESH_DES_KEY_FILE; } + | RESOURCES { Lex->type|= REFRESH_USER_RESOURCES; } opt_table_list: /* empty */ {} @@ -3040,7 +3044,9 @@ keyword: | MASTER_USER_SYM {} | MASTER_PASSWORD_SYM {} | MASTER_CONNECT_RETRY_SYM {} + | MAX_CONNECTIONS_PER_HOUR {} | MAX_QUERIES_PER_HOUR {} + | MAX_UPDATES_PER_HOUR {} | MEDIUM_SYM {} | MERGE_SYM {} | MINUTE_SYM {} @@ -3074,6 +3080,7 @@ keyword: | REPAIR {} | REPEATABLE_SYM {} | RESET_SYM {} + | RESOURCES {} | RESTORE_SYM {} | ROLLBACK_SYM {} | ROWS_SYM {} @@ -3443,7 +3450,7 @@ grant: lex->select->db=0; lex->ssl_type=SSL_TYPE_NONE; lex->ssl_cipher=lex->x509_subject=lex->x509_issuer=0; - lex->mqh=0; + bzero(&(lex->mqh),sizeof(lex->mqh)); } grant_privileges ON opt_table TO_SYM user_list require_clause grant_options @@ -3643,9 +3650,17 @@ grant_option_list: grant_option: GRANT OPTION { Lex->grant |= GRANT_ACL;} - | MAX_QUERIES_PER_HOUR EQ NUM + | MAX_QUERIES_PER_HOUR EQ ULONG_NUM { - Lex->mqh=atoi($3.str); + Lex->mqh.questions=$3; + } + | MAX_UPDATES_PER_HOUR EQ ULONG_NUM + { + Lex->mqh.updates=$3; + } + | MAX_CONNECTIONS_PER_HOUR EQ ULONG_NUM + { + Lex->mqh.connections=$3; } begin: diff --git a/sql/structs.h b/sql/structs.h index 2250ea784f2..75280b34715 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -162,13 +162,16 @@ typedef struct st_lex_user { } LEX_USER; +typedef struct user_resources { + uint questions, updates, connections; +} USER_RESOURCES; + typedef struct user_conn { - char *user; - uint len, connections, questions, max_questions; + char *user, *host; + uint len, connections, conn_per_hour, updates, questions, user_len; + USER_RESOURCES user_resources; time_t intime; -} UC; - - +} USER_CONN; /* Bits in form->update */ #define REG_MAKE_DUPP 1 /* Make a copy of record when read */ #define REG_NEW_RECORD 2 /* Write a new record if not found */ -- cgit v1.2.1