MySQL：如何做到行级安全（如 Oracle 的 Virtual Private Database）？

MySQL: how to do row-level security (like Oracle's Virtual Private Database)?

提问by StackOverflowNewbie

Say that I have vendors selling various products. So, at a basic level, I will have the following tables: vendor, product, vendor_product.

假设我有销售各种产品的供应商。因此，在基本层面上，我将拥有以下表格：vendor, product, vendor_product。

If vendor-1 adds Widget 1to the producttable, I want only vendor-1 to see that information (because that information is "owned" by vendor-1). Same goes for vendor-2. Say vendor-2 adds Widget 2, only vendor-2 should see that information.

如果 vendor-1 添加Widget 1到product表中，我只希望 vendor-1 看到该信息（因为该信息由 vendor-1 “拥有”）。供应商 2 也是如此。假设 vendor-2 添加Widget 2，只有 vendor-2 应该看到该信息。

If vendor-1 tries to add Widget 2, which was already entered by vendor-2, a duplicate entry for Widget 2should not be made in the producttable. This means that, somehow, I need to know that vendor-2 now also "owns" Widget 2.

如果 vendor-1 尝试添加Widget 2已由 vendor-2 输入的 ，则Widget 2不应在product表中创建重复条目。这意味着，不知何故，我需要知道 vendor-2 现在也“拥有” Widget 2.

A problem with having multiple "owners" of a piece of information is how to deal owners editing/deleting the data. Perhaps vendor-1 no longer wants Widget 2to be available to him/her, but that doesn't necessarily apply for vendor-2.

一条信息有多个“所有者”的问题是如何处理所有者编辑/删除数据。也许 vendor-1 不再希望Widget 2对他/她可用，但这不一定适用于 vendor-2。

Finally, I want the ability to flag(?) certain records as "yes, I have reviewed this data and it is correct" such that it then becomes available to all the vendors. Say I flag Widget 1as good data, that product should now be seen by all vendors.

最后，我希望能够将某些记录标记（？）为“是的，我已经了这些数据并且它是正确的”，以便所有供应商都可以使用它。假设我标记Widget 1为好数据，该产品现在应该被所有供应商看到。

It seems that the solution is row level security. The problem is that I'm not too familiar with its concepts or how to implement it in MySQL. Any help is highly appreciated. Thanks.

似乎解决方案是行级安全性。问题是我不太熟悉它的概念或如何在 MySQL 中实现它。任何帮助都受到高度赞赏。谢谢。

NOTE: this problem is somewhat discussed here: Database Design: use composite key as FK, flag data for sharing?. When I asked the question, I wasn't sure how to phrase the question very well. Hopefully, I explained my problem better this time.

注意：这里稍微讨论了这个问题：数据库设计：使用复合键作为 FK，标记数据进行共享？. 当我问这个问题时，我不确定如何很好地表达这个问题。希望这次我能更好地解释我的问题。

回答by squawknull

Mysql doesn't natively support row level security on tables. However, you can sort of implement it with views. So, just create a view on your table that exposes only the rows you want a given client to see. Then, only provide that client access to those views, and not the underlying tables.

Mysql 本身不支持表的行级安全性。但是，您可以使用视图来实现它。因此，只需在您的表上创建一个视图，该视图仅公开您希望给定客户端看到的行。然后，只为该客户端提供对这些视图的访问，而不是底层表。

See http://www.sqlmaestro.com/resources/all/row_level_security_mysql/

见http://www.sqlmaestro.com/resources/all/row_level_security_mysql/

回答by squawknull

You already suggested a vendor, product and vendor_product mapping table. You want vendors to share the same product if they both want to use it, but you don't want duplicate products. Right?

您已经建议了供应商、产品和供应商_产品映射表。如果供应商都想使用它，您希望供应商共享相同的产品，但您不希望出现重复的产品。对？

If so, then define a unique index/constraint on the natural key that identifies a product (product name?).

如果是，则在标识产品（产品名称？）的自然键上定义唯一索引/约束。

If a vendor adds a product, and it doesn't exist, insert it into the product table, and map it to that vendor via the vendor_product table.

如果供应商添加了产品，但该产品不存在，则将其插入产品表中，并通过 vendor_product 表将其映射到该供应商。

If the product already exists, but is mapped to another vendor, do not insert anything into the product table, and add another mapping row mapping the new vendor to the existing product (so that now the product is mapped to two vendors).

如果该产品已存在，但映射到另一个供应商，则不要在产品表中插入任何内容，并添加另一个映射行，将新供应商映射到现有产品（这样现在该产品映射到两个供应商）。

Finally, when a vendor removes a product, instead of actually removing it, just delete the vendor_product reference mapping the two. Finally, if no other vendors are still referencing a product, you can remove the product. Alternatively, you could run a script periodically that deletes all products that no longer have vendors referencing them.

最后，当供应商移除产品时，而不是实际移除它，只需删除映射这两者的 vendor_product 引用即可。最后，如果没有其他供应商仍在引用产品，您可以删除该产品。或者，您可以定期运行一个脚本来删除所有不再有供应商引用它们的产品。

Finally, have a flag on the product table that says that you've reviewed the product, and then use something like this to query for products viewable by a given vendor (we'll say vendor id 7):

最后，在产品表上有一个标志，表明您已经了该产品，然后使用类似的内容来查询给定供应商可见的产品（我们将说供应商 ID 7）：

select product.*
from product
left join vendor_map
on vendor_map.product_id = product.product_id
where vendor_map.vendor_id = 7
or product.reviewed = 1;

Finally, if a product is owned by multiple vendors, then you can either disallow edits or perhaps "split" the single product into a new unique product when one of the owning vendors tries to edit it, and allow them to edit their own copy of the product. They would likely need to modify the product name though, unless you come up with some other natural key to base your unique constraint on.

最后，如果一个产品由多个供应商所有，那么当拥有供应商之一试图编辑它时，您可以禁止编辑或可能将单个产品“拆分”为一个新的独特产品，并允许他们编辑自己的副本产品。不过，他们可能需要修改产品名称，除非您想出其他一些自然键来作为唯一约束的基础。

回答by ChrisWue

This sounds to me that you want to normalize your data. What you have is a 1 (product) to many (vendors) relationship. That the relationship is 1:1 for most cases and only 1:n for some doesn't really matter I would say - in general terms it's still 1:n and therefor you should design your database this way. The basic layout would probably be this:

在我看来，这听起来您想要规范化您的数据。您拥有的是 1（产品）对多（供应商）的关系。在大多数情况下，关系是 1:1，对于某些情况只有 1:n，我会说这并不重要 - 一般而言，它仍然是 1:n，因此您应该以这种方式设计您的数据库。基本布局可能是这样的：

Vendor Table
VendorId    VendorName    OtherVendorRelatedInformation

WidgetTable
WidgetId    WidgetName    WidgetFlag     CreatorVendor   OtherWidgetInformation

WidgetOwnerships
VendorId    WidgetId      OwnershipStatus     OtherInformation

Update: The question of who is allowed to do what is a business problem so you need to have all the rules laid out. In the above structure you can flag which vendor created the widget. And in the ownership you can flag what the status of the ownership is, for example

更新：谁可以做的问题是业务问题，因此您需要制定所有规则。在上面的结构中，您可以标记哪个供应商创建了小部件。在所有权中，您可以标记所有权的状态，例如

• CreatorFullOwnership
• SharedOwnership
• ...

• 创作者完全所有权
• 共享所有权
• ...

You would have to make up the flags based on your business rules and then design the business logic and data access part accordingly.

您必须根据您的业务规则组成标志，然后相应地设计业务逻辑和数据访问部分。