限制某些用户只能访问公共表中的数据 [英] Restricting certain users to only their data in a common table

问题描述

我正在构建一个被很多公司使用的 web 应用程序，我想让他们访问原始 MySQL 数据库 - 但显然他们只需要能够访问自己的数据.和只读访问 - 这仅用于备份和分析.

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.

如果所有表都有一个 company_id 列，并且我们为每个公司创建一个 MySQL 用户帐户，你会怎么做?甚至可以允许某人运行任意查询，但对特定列值设置顶级限制吗?

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 possible 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.

推荐答案

感谢 Frank Schmitt 这个答案，但他似乎不想写一篇，所以我可以接受:(

Credit to Frank Schmitt for this answer, but he doesn't seem to want to write one up so I can accept it :(

在视图内部，您可以使用 user() 函数来过滤数据(这样，每个数据库表只需要一个视图，而不是每个数据库表和客户一个视图).

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).

这篇关于限制某些用户只能访问公共表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！