Databases
StackOverflow2013
Postsdbo
POBest method to verify multi-level relational dependencies
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest method to verify multi-level relational dependencies
    primarykey
    data
    text
    <p>Suppose you have entities A, B, C and D. </p> <ul> <li>D relates to C</li> <li>C relates to B </li> <li>B relates to A</li> </ul> <p>Furthermore, user is only allowed to <strong>operate on D</strong>, if user <strong>owns A</strong>.</p> <p>At a certain state in an application, you include a link to a page, which accesses D. Thus, you includes D's ID as a GET or POST parameter.</p> <p>If user clicks on the link, the application retrieves D's ID and begins to operate on D.</p> <p>Simple apps use URLs like this [modulo URL-rewriting]:</p> <pre><code>http://www.myServer.com/?action=1234&amp;entity=D&amp;ID=23 </code></pre> <p><strong>How to verify if user is allowed to operate on D?</strong></p> <p><strong>A)</strong> The obvious solution would be this: <strong>Given D, find C, then find B and eventually find A.</strong> If the chain breaks somewhere, access to D would be rejected. Unfortunately, this requires - if trivially implemented - 4 database accesses instead of just the one for A.</p> <p><strong>B)</strong> Another solution would be to keep <strong>D's ID in the current session</strong> in a set of accessible entities to be used by the next page to be rendered.</p> <p><strong>C)</strong> As an alternative, one could <strong>encrypt GET and POST parameters</strong> somehow. On each page request, the first operation would be to decrypt the request's parameters. If the decrypt operation fails, access would be denied.</p> <p><strong>D)</strong> Or, at infinitum, <strong>hash all links in all pages</strong>, keep a map in the session which associates the hashes with the URLs and write only hashes to webpages. </p> <p><strong>E)</strong> Finally, you could keep references to A, B and C in D, references to A and B in C, references to A in B. Thus, at each level, one would be able to <strong>find immediately the root</strong> entity.</p> <p><strong>What's your solution in such a situation? And why?</strong></p> <p>Although I included the PHP tag, I'm don't want to focus this question to a language. I'd be happy to get general proposals. Or solutions, which are already implemented in e.g. <a href="http://en.wikipedia.org/wiki/Object-Relational_Mapping" rel="nofollow">ORM</a> layers.</p> <p><strong>UPDATE-1</strong></p> <p>Finally, I have chosen <strong>D)</strong>.</p> <p><strong>General principle:</strong></p> <p>Ensure, that IDs of somehow subordinate entities always get passed in a secure/trusted way. In such a way, that a third party isn't able to change their values.</p> <p><strong>Details:</strong></p> <p>This option provides many benefits by design:</p> <p>First, IDs or other parameters of the linked pages never reach the browser. Instead of </p> <pre><code>http://www.myServer.com/?action=1234&amp;entity=D&amp;ID=23 </code></pre> <p>most pages gets linked like this</p> <pre><code>http://www.myServer.com/?forwardHash=78sd7sdf98asd7ad5aa76asa4a465 </code></pre> <p><strong>All parameters</strong> of the next page to be executed are fully <strong>kept inside the user's session</strong>.</p> <p>Since all parameters of pages are kept inside the user's session, <strong>far less checking is needed</strong>. Especially, the above mentioned relational dependency checks aren't any longer of use. If something is in the user's session, it has been put from a previously <strong>trusted dialog step</strong>.</p> <p>Moreover, one can even <strong>force the user to only call those link available on the currently rendered page</strong>. Each time they call a link, the app may invalidate all other links of the page. Thus users won't be able to open pages in several windows and think, that they see two different 'state' of the application. If they call a link twice, the app may present an error message.</p> <p>Finally, one may directly establish something I'd call <strong>sub-workflow</strong> dialogs: You would start the dialog by pushing the current page's URL on a <strong>continuation stack in the session</strong> and opening the edit dialog step. User may either orderly end or intentionally cancel the dialog workflow. A <strong>cancel workflow link</strong> may automagically appear as a user option, if the continuation stack isn't empty. </p> <p>By keeping the continuation on a stack in the session, it gets fully isolated from the currently running dialog step. The dialog step not even knows anything about its caller. </p> <p>By wrapping the functionality inside a small manager calls, the sub-process finally calls FlowManager::finishFlow(). This call pops a continuation from the stack and redirects the browser to this page - <strong>effectively returning to the point, where the workflow started</strong>. </p> <p>Since we use a stack of continuations, one could even run <strong>sub-workflows which are subordinate to other sub-workflows</strong>.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSteAp
    1. USSteAp
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBest method to verify multi-level relational dependencies
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POBest method to verify multi-level relational dependencies
      1. USMajor Productions
      1. VTFavorite
    3. VO
      singulars
      1. POBest method to verify multi-level relational dependencies
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAm I right in saying that A/B/C/D is essentially hierarchical data, and you could look through [Bill Karwin's options for hierarchival data](http://www.slideshare.net/billkarwin/models-for-hierarchical-data), finding one that would enable you to get the entire tree (=rights) in one query?
      singulars
      1. POBest method to verify multi-level relational dependencies
      1. USWrikken
    2. COYes. One could see the situation as hierarchical structured data. I'll have a look.
      singulars
      1. POBest method to verify multi-level relational dependencies
      1. USSteAp
    3. COIs it true to say that there could be any number of links in the chain between the entity being edited and the entity owned by the user - it's not limited to the three links in your example?
      singulars
      1. POBest method to verify multi-level relational dependencies
      1. USMike
 

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