MySQL:如何做到行级安全(如 Oracle 的 Virtual Private Database)? [英] MySQL: how to do row-level security (like Oracle's Virtual Private Database)?

问题描述

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

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 表，我只希望 vendor-1 看到该信息(因为该信息由供应商拥有"-1).供应商 2 也是如此.假设 vendor-2 添加了 Widget 2，只有 vendor-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.

如果 vendor-1 尝试添加已由 vendor-2 输入的 Widget 2，则 Widget 2 不应在 中重复输入>product 表.这意味着，不知何故，我需要知道 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.

一条信息有多个所有者"的一个问题是如何处理所有者编辑/删除数据.也许 vendor-1 不再希望 Widget 2 对他/她可用，但这不一定适用于 vendor-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 的 Virtual Private Database)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！