How to prevent user to access other users' data?

Question

PROBLEM

• User authenticated into the application
• Simple database schema: User ---> Document ---> Item
• API to access to Document Items

If the logged user knows the id of items that belong to some other user, he can access to it. I would like to prevent this behavior.

SOLUTION

The first solution I found is to add a userid field to every records in every table to check at every query if the record belong to the logged user.

This is a good solution? Do you know some better design pattern to prevent the user to access other users' data?

Thanks

Answer 1

1. If the documents belong to a user, adjust your queries so that only items that belong to the user's documents are retrieved. No need to add userIDs to the items themselves.
2. If you need to expose IDs to the users, make those IDs GUIDs, instead of consecutive numbers. While not a perfect solution, it makes it much harder to guess the IDs of other users' items,
3. If you're using Oracle, there's VPD, Virtual Private Database. You can use that to restrict access for users.