Mysql : Maximum number of connections on a per user basis - Possible?

Question

Is it possible to set a maximum number of open connections that a user (NOT all users combined, just one specific user) may have at a given time in mysql?

Eg:

If I have a user "user1234", and want to only allow him to have a max 10 connections open at any given time.

Thanks.

Answer 1

You can set this per-user on a per-hour basis.

Under the WITH option for the MySQL GRANT command yu gave the following:

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

You can set

• queries per hour
• updates per hour
• inserts per hour
• concurrent connections (not per but at any given time)

This can give you finer per-hour granularity not only in terms of the number of connections made, but what can be executed.

The columns for these user grants are locate in mysql.user:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select column_name from information_schema.columns
    -> where table_schema='mysql'
    -> and table_name='user'
    -> and column_name like 'max%'
    -> order by ordinal_position;
+----------------------+
| column_name          |
+----------------------+
| max_questions        |
| max_updates          |
| max_connections      |
| max_user_connections |
+----------------------+
4 rows in set (0.01 sec)

mysql>

I wrote about these things before

• May 24, 2011 : What are user connections - when are the created and destroyed?
• Jun 16, 2011 : Would it be sensible to create a MySQL user for each user account of a web application?
• Mar 22, 2012 : How can I limit MySQL connections?

Your Original Question

As shown above, you can set user per-hour user limits. However, I came to notice that you want just DB Connections open for any given user.

You may require some special customized authentication that counts how many connections a user has. In MySQL 5.1+ you could possibly script this:

SELECT COUNT(1) ConnectionCount,user
FROM information_schema.processlist
WHERE user <> 'system user'
GROUP BY user;

This would allow you to know the counts for any connected user.

You may want to look into connection pooling and persistent connections to regulate the number of users connected.

Answer 2

grant usage on *.* to user1234@'%' with max_user_connections 10;

Please run the above command.