What is a good way to create a database for multiple frontend users (one must cannot touch other users' data)?

I usually make

• One User table for User metadata
• Some of other tables have userID (i.e. user specific)
• Some of others have userID[] or pivot table / m2m (i.e. shared between only some users)
• Others doesn't have userID at all (shared between all users)

But is this a good or even a secured way in the first place?

How does User management system work in this case; when there are thousands of similar users with very low privileges?

Comments

[Nathan Kallman]

To give the briefest and high-levelest of fly-bys: there are two basic steps, authentication and authorization.

• Authentication answers the question of who or "are you who you say you are". Just because I claim to be Elon Musk does not mean I will be allowed in the factories or access to his riches. I need proof (a password, 2FA, etc.). Use a standard library for this.
• Authorization answers the question of "are you allowed to do this?". Let's build a twitter-esque app to explore this:

Let's have users and chirps. A "chirp" can be sent to a single user or sent publicly. To represent that let's make the chirps table have just a few things: from_user_id (the one who wrote it), content (aka the "chirp"), and to_user_id (if empty it is public, if not it's a direct message).

If I ask to see chirp 42, the authorization step needs to answer whether I'm allowed to view that chirp or not. It would probably follow just a few rules:

1. Is this chirp public? (aka to_user_id is empty)
2. Was the chirp sent to me? (aka the current_user_id that I've authenticated myself as having is equal to this chirp's to_user_id means that I'm authorized to see this chirp)
3. Was this chirp created by me? (aka current_user_id equals from_user_id)

If any of those things are true, then I am authorized to view the chirp and the app can continue with rendering it all nicely. We can think of similar rules for any other action a user might take: attempting to edit chirp 42, are you from_user_id; attempting to send chirps, have I been blocked or banned; etc.

Often it can be helpful to think of authorization in two steps:

1. Am I allowed to take this action at all (i.e. if chirper is paywalled, have I paid this month?)
2. Am I allowed to take this action against this data (i.e. I can see most other chirps, but this chirp in particular was sent to someone else, so I am not allowed to see it)

[Pacharapol Withayasakpunt]

Thanks. I think I somewhat understand how table schema would work.

But is it possible to do all, or most of the things in PL/SQL or at the database level; just for extra security?

Maybe something like, ALTER TABLE CREATE CHECK CONSTRAINT or CREATE TRIGGER?

[Kevin Hicks]

In my experience, most of what @kallmanation described always happens at the code level above SQL

You may be able to do something with check constraints or triggers, but I think it would be unusual and it might lead to headaches down the road. Right now the focus is on how to secure this data from other users, but eventually, you may need reports, admin tools, etc. that require different people who wouldn't normally have access to be able to access this data.

This could be a constant battle of updating things in the database and trying to ensure each constraint or trigger is still properly locked down while allowing exceptions for some pages, users, etc. that the database may not even be aware of. I'm honestly not sure if this approach would be possible long-term and for most of the needs, I have needed in user management and permission systems before.

I would recommend looking up authorization best practices, packages, and tips for the language and/or framework you are working in. Luckily this type of user management is needed in most apps so there are plenty of packages and guides to help figure out the best thing to do.