DEV Community

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

Collapse
 
patarapolw profile image
Pacharapol Withayasakpunt • Edited

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?

Collapse
 
kevinhickssw profile image
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.