Using mysql, how to restrict a user just to get the aggregated information (eg. sums, averages, count) of the table and only if the result of the number of record is greater than the threshold?

For example, if the threshold is 2, the following query should not be responded if only one supplier is living in London.

select count(*) from s where city = "london"

But this should not be responded.

select salary from s where city = "london" 

Because if user knows in advance that there is only one who live in London, he can know his salary. I want to prevent that.

  • It turns out that it is very easy to extract details about individuals using just a few queries and public information about them, because there are so many common properties/relationships for which each person has unique combinations. – philipxy Dec 13 '15 at 0:53
  • yes, I know that, i just want to know how to restrict users to get only aggregated information and only when the number of result record is within the threshold. – Ju Ju Dec 13 '15 at 15:58
  • if there is only a limited set of queries that they are allowed to run you could use a stored procedure that contains the query (pre-written by yourself), and only returns a result if there are more than x rows. – IGGt Dec 14 '15 at 9:29

Plan A: Use GRANT to not let that user see salary.

Plan B: Provide Summary Tables (with counts, totals, etc) of stuff that you are willing to let them see, then restrict (via GRANT) their privileges on a table basis. (Or put the Summary Tables in a different database.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.