Zarjio Zarjio - 10 months ago 38
MySQL Question

How do I ensure rows inserted into a SQL view will be elements of that view?

Here is the scenario:

I have a simple 'Inventory' table. This table has three columns: one foreign key that references a product, one foreign key that references a store, and one numeric value for the price. This table doesn't specify the amount of a product available, it just is used to inform users that a store sells a particular product.

This Inventory table is publicly viewable (that is the whole point of the application: users should be able to search for different products among various - potentially unrelated - stores). The stores need to be able to update their own inventories, without affecting the inventories of other stores.

Now, each store has its own user account and view. The views are essentially set up as follows:


Each store has full permission on its own inventory view, so that each store can add items to its inventory, update them, etc.

Here is the snag: Each store can add items to this view with a StoreID that does not match their own StoreID! In this way, they can add items to other stores' inventories (which is certainly a no-no).

I have already created a front-end application for accessing the database, and it is easy enough to programmatically ensure that no store affects another stores inventory, but I want better security than that. How do I go about enforcing this at the database level? Triggers? Constraints? I've looked into both and I'm not exactly sure how to go about this.

One last thing: only the DB root account and individual stores have access to individual stores' views.

Answer Source

I think you can get what you want by creating your view with the WITH CHECK OPTION clause. This will prevent anyone from using the view to insert information that does not match the view's Where clause, so if a store's view is for "Where StoreID = 500", it cannot use that view to insert records for store 501.

According to the MySQL documentation here,

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true. The WITH CHECK OPTION clause was implemented in MySQL 5.0.2.

In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED.

For more information about updatable views and the WITH CHECK OPTION clause, see Section 17.4.3, “Updatable and Insertable Views”.