Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL: Limiting number of results a MYSQL account can get

I want to make sure a specific MySQL account can’t dump the whole database, or any table, in any way – be it using MYSQLDUMP or SELECT *, etc.

I’d be happy to limit all queries from that account to 1000 results.

This is incase the account is compromised.

Any options?

Tags:

2

Answers


  1. 0

    There’s no way to prevent this. If a user has SELECT privilege on a table, they can dump all the rows.

    There are options like sql_select_limit, but any client can override this per session or per query.

    If you need to restrict how users can dump data, then don’t give them direct access. Create a service that dumps data by request, and then you can implement code in the service to govern how much they can dump.

    Even if you could restrict the user to dump 1000 rows, how does that prevent illicit access? They could just dump the first 1000 rows, then the next 1000 rows using LIMIT...OFFSET, and so on.

    Login or Signup to reply.
  2. 0
    1. Set minimal privileges for this account.
    2. Create stored procedure which selects rows from provided SQL code using dynamic sql with SQL SECURITY DEFINER. The output must be saved into an intermediate table. Then another select returns 1000 rows from this table. Finally the table is dropped. Do not forget to check for injection.
    3. Grant EXECUTE privilege for this SP.

    Of course MySQLDump execution must be not allowed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search