How can I force limit the number of rows returned from a query for all users?

Question

We are looking to limit the number of rows returned when any user makes a SELECT query from our database. Of course you can manually limit the number of rows on a returned query, but we want to force-append something like limit 10 on all SELECT commands. Users will only be able to access this database from a bastion host using the MySQL CLI. For example:

SELECT * FROM customer

would return all rows. Without a user specifying, we want to automatically turn the command into something like this so they can only get 10 rows back at the absolute maximum:

SELECT * FROM customer limit 10

Any way of achieving this desired functionality would be fine (whether it's an appended command or not). Is this possible?

Answer 1

If you do not trust your users to be able to use the database responsibly, you probably should not allow them to have direct access to the database via mysql cli. Develop a reporting layer where you can enforce such limitations.

If you insist on using mysql cli, then turn on --safe-updates option and configure --select-limit option.

Alternatively, set the sql_select_limit setting via config or batch file for these users.