Restricting certain users to only their data in a common table
I'm building a webapp that's being used by a bunch of companies, and I'd like to give them access to the raw MySQL database - but they need to able to access only their own data, obviously. And read-only access - this is for backup and analytics only.
If all tables have a company_id column, and we make one MySQL user account for each company, how would you go about doing this? Is it even pos开发者_开发技巧sible to allow someone to run arbitrary queries, but place a top level restriction on a particular column value?
If not, any other ideas on how to do this? The only other way I can think of is to create a separate database for each company... not sure I wan't to do that, though.
I'm not sure but I think you can achieve this using Views.
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
From wikipedia: Views can limit the degree of exposure of a table or tables to the outer world
http://en.wikipedia.org/wiki/View_%28database%29
Credit to Frank Schmitt for this answer, but he doesn't seem to want to write one up so I can accept it :(
Inside the view, you could possibly use the user() function to filter the data (this way, you need only one view per DB table, instead of one view per DB table and customer).
精彩评论