Mysql – Does MySQL limit the number of users (not connections)

MySQL

I have always believed that you can create as many users as you like. I have been creating a user per app with limited database/table access for only what that app requires.

Recently I was told there is a limit. While I have never heard of one, or reached one, I still wanted to ask the community.

Note: I am not referring to max connections, but the number of users.

Best Answer

User accounts are again stored in tables in terms of rows and columns and also its respected permissions under mysql database wherein it has primary key as host and username. So, I don't think so you have any limitations. But you have something called max_connections that has to be set to a value, say it has 100000 as max that you can set. Then keeping each user connection in mind then we can go upto 99999 users connected to it parallely.

My question to you is.

  1. Why do you need all different users to be connected to the mysql connection pool?
  2. It is highly impossible to make a note of users permissions and its passwords.
  3. If you need those many users for application purpose, you may try doing it in regular innodb tables and have list of users under one group and tag them to a role. Just grant multiple roles to the users who needs the access.
Related Question