Allow one to block an account by using GRANT max_user_connections = -1

One can set @@global.max_user_connections to -1 to block anyone, except SUPER user, to login.
If max_user_connection is 0, one can't change it without a restart (needed to get user connections counting to work correctly)


mysql-test/r/system_mysql_db.result:
  Changed max_user_connections to handle negative numbers.
mysql-test/r/user_limits-2.result:
  New test case that one can't change max_user_connection if it was 0
mysql-test/r/user_limits.result:
  Fixed wrong error messages.
mysql-test/r/variables.result:
  Store / restore max_user_connections (needed as there is now a --master.opt file that changes it)
mysql-test/t/subselect_mat_cost-master.opt:
  Enable slow query log (as this test found some errors in slow query logging)
mysql-test/t/user_limits-2.test:
  New test case that one can't change max_user_connection if it was 0
mysql-test/t/user_limits-master.opt:
  Set max_user_connections (as one can't change it if it was 0)
mysql-test/t/user_limits.test:
  Test max_user_connections -1
mysql-test/t/variables-master.opt:
  Set max_user_connections (as one can't change it if it was 0)
mysql-test/t/variables.test:
  Set/restore max_user_connections
scripts/Makefile.am:
  Add a text message to mysql_fix_privilege_tables.sql that it's automaticly generated
scripts/mysql_system_tables.sql:
  Change max_user_connections to signed
scripts/mysql_system_tables_fix.sql:
  Change max_user_connections to signed
sql/item_func.cc:
  Change SHOW_INT to be signed.
  (Needed for max_user_connections and it's probably a bug that it was not originally signed)
sql/log.cc:
  Remove some code that was not needed (All these variables are reset at start of query)
sql/mysql_priv.h:
  Made max_user_connections signed.
  Added max_user_connections_checking
sql/mysqld.cc:
  Added max_user_connections_checking so that we know if max_user_connections was 0 at startup
  (Which means that we will not do connection counting for accounts that don't have user resource limits)
  Set thd->start_utime at same time as thr_create_utime. (Before start_utime could be < thr_create_utime which lead to wrong query counting)
sql/set_var.cc:
  Don't allow one to change 'max_user_connections' if it was 0 at startup.
sql/sql_acl.cc:
  Change user_connection counting to be negative.
sql/sql_connect.cc:
  If max_user_connections is < 0 then only SUPER user can login.
  Fixed wrong variable names for error messages.
  Fixed wrong initial value for questions.
  Set thd->start_utime and thd->thr_create_utime at startup.  Needed to get time_out_user_resource_limits() to work.
sql/sql_show.cc:
  SHOW_INT is now negative
sql/sql_yacc.yy:
  Support negative values for MAX_USER_CONNECTIONS
sql/structs.h:
  Make user connect counting work with signed numbers.

1 files changed, 32 insertions, 5 deletions

diff --git a/mysql-test/r/user_limits.result b/mysql-test/r/user_limits.result
index a94eb4616d1..776a25d55e6 100644
--- a/mysql-test/r/user_limits.result
+++ b/mysql-test/r/user_limits.result
@@ -1,3 +1,4 @@
+set @my_max_user_connections= @@global.max_user_connections;
 drop table if exists t1;
 create table t1 (i int);
 delete from mysql.user where user like 'mysqltest\_%';
@@ -12,9 +13,9 @@ i
 select * from t1;
 i
 select * from t1;
-ERROR 42000: User 'mysqltest_1' has exceeded the 'max_questions' resource (current value: 2)
+ERROR 42000: User 'mysqltest_1' has exceeded the 'max_queries_per_hour' resource (current value: 2)
 select * from t1;
-ERROR 42000: User 'mysqltest_1' has exceeded the 'max_questions' resource (current value: 2)
+ERROR 42000: User 'mysqltest_1' has exceeded the 'max_queries_per_hour' resource (current value: 2)
 drop user mysqltest_1@localhost;
 grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 2;
 flush user_resources;
@@ -27,11 +28,11 @@ i
 delete from t1;
 delete from t1;
 delete from t1;
-ERROR 42000: User 'mysqltest_1' has exceeded the 'max_updates' resource (current value: 2)
+ERROR 42000: User 'mysqltest_1' has exceeded the 'max_updates_per_hour' resource (current value: 2)
 select * from t1;
 i
 delete from t1;
-ERROR 42000: User 'mysqltest_1' has exceeded the 'max_updates' resource (current value: 2)
+ERROR 42000: User 'mysqltest_1' has exceeded the 'max_updates_per_hour' resource (current value: 2)
 select * from t1;
 i
 drop user mysqltest_1@localhost;
@@ -65,10 +66,20 @@ select * from t1;
 i
 connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
 ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: 3)
+grant usage on *.* to mysqltest_1@localhost with max_user_connections -1;
+show grants for mysqltest_1@localhost;
+Grants for mysqltest_1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_USER_CONNECTIONS -1
+flush user_resources;
+show grants for mysqltest_1@localhost;
+Grants for mysqltest_1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_USER_CONNECTIONS -1
+connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
+ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: -1)
 drop user mysqltest_1@localhost;
 select @@session.max_user_connections, @@global.max_user_connections;
 @@session.max_user_connections	@@global.max_user_connections
-0	0
+1000	1000
 set session max_user_connections= 2;
 ERROR HY000: Variable 'max_user_connections' is a GLOBAL variable and should be set with SET GLOBAL
 set global max_user_connections= 2;
@@ -92,5 +103,21 @@ select @@session.max_user_connections, @@global.max_user_connections;
 connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
 ERROR 42000: User 'mysqltest_1' has exceeded the 'max_user_connections' resource (current value: 3)
 set global max_user_connections= 0;
+grant usage on *.* to mysqltest_1@localhost with max_user_connections 0;
+set global max_user_connections=-1;
+show variables like "max_user_user_connections";
+Variable_name	Value
+select @@max_user_connections;
+@@max_user_connections
+-1
+select @@global.max_user_connections;
+@@global.max_user_connections
+-1
+connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
+ERROR 42000: User mysqltest_1 already has more than 'max_user_connections' active connections
+set global max_user_connections=1;
+connect(localhost,mysqltest_1,,test,MYSQL_PORT,MYSQL_SOCK);
+ERROR 42000: User mysqltest_1 already has more than 'max_user_connections' active connections
 drop user mysqltest_1@localhost;
 drop table t1;
+set global max_user_connections= @my_max_user_connections;