CODEWITHSUNDEEP
mysql
harsha89
harsha89

Reputation: 357

How to restrict user bandwidth, storage and access per user in mysql

I needs to limit access for mysql database access per user based. So the requirements are like below.

  1. User can have a dedicated storage in MySQL instance and he can't exceed it.
  2. Limit user access for some time duration in the day
  3. Limit number of read write update queries per hour per user
  4. Limit bandwidth per user.

How can I achieve this in a MySQL server?

Upvotes: 1

Views: 2248

Answers (2)

chresse
chresse

Reputation: 5805

1. User can have a dedicated storage in MySQL instance and he can't exceed it

as far as i know there is no direct way to control the storage of a mysql instance per user. you could do it like akuzminsky said or if you have special tables for the user you can use:

ALTER TABLE tablename MAX_ROWS=1000000 AVG_ROW_LENGTH=nnn;

for further information have a look at:

  1. http://dev.mysql.com/doc/refman/5.7/en/limits-frm-file.html
  2. http://dev.mysql.com/doc/refman/5.7/en/table-size-limit.html

2. Limit user access for some time duration in the day

i dont know a direct way. i could imagine a cronjob which runs a similar comand as for question 3., but should only be if you really need it and there is no other solution :)

3. Limit number of read write update queries per hour per user

her you can use some of the following parameters of the GRANT command:

GRANT ALL ON databasename.* TO 'username'@'server'
       WITH MAX_QUERIES_PER_HOUR 20
       MAX_UPDATES_PER_HOUR 10
       MAX_CONNECTIONS_PER_HOUR 5
       MAX_USER_CONNECTIONS 2;

(remember to run a FLUSH PRIVILEGES; after)

for further information have a look at:

http://dev.mysql.com/doc/refman/5.7/en/user-resources.html

4. Limit bandwidth per user

should be a thing between the database- and applicationserver.

Upvotes: 1

akuzminsky
akuzminsky

Reputation: 2257

Here's some ideas.

  1. MySQL doesn't give you means to set quota on disk usage. What you can do is to check how much space each database uses and enforce the quota by other means:

    select sum(data_length+index_length),
from information_schema.tables
WHERE TABLE_SCHEMA = 'user_x_db'

  2. Don't know

  3. Set max_updates in mysql.user
  4. Don't know

Upvotes: 0

Related Questions