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:
CREATE VIEW MY_INVENTORY AS SELECT I.ProductID, I.StoreID, I.Price FROM Inventory I WHERE I.StoreID = id
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”.