Databases
StackOverflow2013
Postsdbo
POHow do I ensure rows inserted into a SQL view will be elements of that view?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I ensure rows inserted into a SQL view will be elements of that view?
    primarykey
    data
    text
    <p>Here is the scenario:</p> <p>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.</p> <p>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.</p> <p>Now, each store has its own user account and view. The views are essentially set up as follows:</p> <pre><code>CREATE VIEW MY_INVENTORY AS SELECT I.ProductID, I.StoreID, I.Price FROM Inventory I WHERE I.StoreID = id </code></pre> <p>Each store has full permission on its own inventory view, so that each store can add items to its inventory, update them, etc.</p> <p>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).</p> <p>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.</p> <p>One last thing: only the DB root account and individual stores have access to individual stores' views.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USZarjio
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I ensure rows inserted into a SQL view will be elements of that view?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do I ensure rows inserted into a SQL view will be elements of that view?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis is business logic, I'd keep it out of your database for simplicity and cleanliness' sake.
      singulars
      1. POHow do I ensure rows inserted into a SQL view will be elements of that view?
      1. USchristofr
    2. COI understand the desire to keep the DB clean and simple, but at the same time, the added security would be a nice benefit.
      singulars
      1. POHow do I ensure rows inserted into a SQL view will be elements of that view?
      1. USZarjio
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload