MySQL：如何做行级安全性（如Oracle的虚拟专用数据库）？ [英] MySQL: how to do row-level security (like Oracle's Virtual Private Database)?

问题描述

说我有卖各种产品的供应商。所以，在基本层面，我将有以下表： vendor ，产品，

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

如果vendor-1将 Widget 1 添加到 product 表，我只想要供应商-1查看该信息（因为该信息由供应商-1拥有）。同样适用于供应商2。如果供应商2添加 Widget 2 ，则只有供应商2应该看到该信息。

If vendor-1 adds Widget 1 to the product table, 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.

要添加 Widget 2 （已由供应商2输入），则不应创建 Widget 2 的重复条目在产品表中。这意味着，不知何故，我需要知道，vendor-2现在也拥有 Widget 2 。

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

具有一条信息的多个所有者的问题是如何处理所有者编辑/删除数据。也许供应商1不再想要 c>小工具2 可用，但这不一定适用于供应商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 2 to be available to him/her, but that doesn't necessarily apply for vendor-2.

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

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 1 as good data, that product should now be seen by all vendors.

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

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.

注意：这个问题稍微讨论一下：数据库设计：使用复合键作为FK，用于共享的标志数据？当我问这个问题，我不知道如何很好地说出问题。希望我这次更好地解释我的问题。

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.

推荐答案

Mysql本身不支持表上的行级安全性。但是，你可以通过视图来实现它。因此，只需在表上创建一个视图，只显示给定客户端要查看的行。

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.

请参阅 http://www.sqlmaestro.com/resources/all/row_level_security_mysql/

这篇关于MySQL：如何做行级安全性（如Oracle的虚拟专用数据库）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！