Is reusing primary key values insecure? : <p>I'm considering a design of primary key index that will reuse primary key values from deleted rows (within seconds in the most extreme case.) This is the most efficient design in this case.</p>

<p>However, it opens up a potential for bugs and security flaws. A client may still have an old key value for the deleted record. A request against that key may return a row owned by the same user our a different user. Depending how well the application is written, chaos could ensue.</p>

<p>It should never be a security flaw, if the application grants access to a row owned by another user, the application is incorrect under any circumstances. However this might make the flaw more visible or easier to exploit. The bigger issue in my mind is that the row might belong to the same user and the application could behave in unexpected ways because it's not the row the client thinks it is.</p>

<p>I could mitigate it a little at a cost of one byte, by adding a reuse counter to the row, incremented when a primary key value is reused after deletion. I would make that counter part of the primary key. Now you could only mistakenly access a row if the reuse counter matches, only once in 256 reuses. The bug is still possible, and it's not a replacement for proper checking if a user should have access to the row, but at least it would defeat accidentally accessing the wrong row.</p>

<p>Thoughts?</p>

<p>This is not for a typical SQL database, it's for a custom database, but the principles are orthogonal.</p>
