A concern has come up recently about employees who have read access to our internal databases being able to download large amounts of data for nefarious purposes.
My question is whether it is possible to restrict the number of rows that a user can return in a query? None of the users who have read access to the db server ever need to return more than a few dozen rows at a time.
Alternatively, how do others here deal with security concerns about the people in their organization who have direct access to run queries on their databases?
1 Answer 1
Always just give the grants necessary. If you want to restrict on the row number, you have to create views like
CREATE VIEW view_name AS
SELECT whatever FROM a_table ORDER BY whatever LIMIT 10;
Then just grant select on this view, not the table the view is reading from. Read more about views here. With DEFINER and SQL SECURITY you can also select on things, that the user usually has no right to. Whatever you need.
As a last note, I included ORDER BY in the query, because a LIMIT without ORDER BY usually doesn't make much sense, cause there's no order in a relational database unless you specify it with said ORDER BY.