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

Question

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

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.

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.

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.

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.

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.

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.

Solution 1

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.

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

Solution 2

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.

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.

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

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.

Comments

[StackOverflowNewbie]

Will views solve the following problems: 1.) ensuring no duplicate data exist, and 2.) allow me to flag certain data so that all vendors can use it?

[StackOverflowNewbie]

the solution doesn't answer the problem. The problem with WidgetOwnerships is that problem of editing/deleting I mentioned. Who really "owns" a widget and has the power to edit/delete it?

[squawknull]

You could certainly setup the view such that it would include the data that all vendors need to access.

[squawknull]

To ensure no duplicate data exists, create a unique index on the underlying table if a combination of columns must always be unique. If your use case is more complex, then you can use a trigger.

[squawknull]

To be honest though, it's perhaps overcomplicating things to try to solve this ONLY on the Mysql side in your case. If each vendor's dataset were really private, and there were not cases of data shared between them, then I'd say this would be pretty easy to make work. But, since you're kind of commingling vendor private and vendor shared data, my personal approach would probably be to handle vendor data isolation in the application layer, rather than in the database layer.

[StackOverflowNewbie]

The co-mingling of private and vendor shared data makes life easier for the individual vendors. If one vendor has already taken the time to enter the data, why not let another use it? It's the same data anyway. I'm not sure how to solve this at the application layer if the DB layer isn't correct in the first place.

[squawknull]

You'll need a DB structure to support it, but you don't need the DB layer to support it, as you have much more flexibility in code. I think you have more or less what you need already in the tables above at the db layer in terms of tables. I'll submit a separate answer so that you can comment and/or vote on that one if it's what you like.

[StackOverflowNewbie]

@squawknull - Just to clarify, if vendor-1 and vendor-2 both have Widget 1 -- and vendor-1 decides to change it to Widget-1, then that essentially becomes a new product (since Widget 1 <> Widget-1), right? (I think this is what you meant by "split".)

[StackOverflowNewbie]

@squawknull - This solution seems to make sense to me. I'm trying to think of cases where it might fail. I'm not familiar with row level security. Is your solution similar to it?

[squawknull]

Sorry for the slow response. I had to sleep... :) Yes, you got what I meant by "split", basically, if somebody changes a product that is being shared by two vendors, you would "split" the product into two distinct products that can go their own ways. If you want a unique constraint on the product name though, you'll have to force them to change the product name to make it unique and distinct when this happens, though.

[squawknull]

This isn't really row-level security. RLS is more along the lines of having multiple database users that can only see the data that is owned by them. It's typically managed based upon the login to the database. If you have one shared stack of software that will be used for all clients, this can become unwieldy (I've been forced to do this before). So, I'd generally say that for this sort of thing, it's far more flexible to handle the security in the application tier, but you have to make sure it is robust.

[squawknull]

This may not work if you will be providing clients with direct login privileges to the database (with no Ruby, PHP, or other software in between, they would be using a Mysql client such as SquirrelSQL or Navicat directly). That said, I would try to give them their own databases if I had to do that, but it would be my last resort to do this at all.

[StackOverflowNewbie]

There will be an application tier. So, maybe this works. Thanks!