0

I am building a simple web app where one set of users have varying admin privileges who can write to database and the other set can only view data.

I am used to securing APIs with JWT or session tokens, but my boss is 50-year-old DBA who thinks it's not secure. He is saying I should implement security within database itself and create all users with appropriate roles in database. The application would then connect to the database using the credentials of each user and perform operations they are allowed. He is also asking me to do all data validations in database using triggers rather than dealing with it on application layer.

His reasoning is that if the application gets compromised in some way the data would still be consistent and secure. I can't really argue with it because it is true.

I understand his reasoning, but I am used to building apps with ORMs which uses one role to get all data. Is it even possible to do it with common Orms like Prisma, hibernate or EF core? I think it could even conflict with a lot of caching mechanisms ORM tools employ.

I don't really understand how to proceed. I can do it, but it will significantly increase the development and maintenance time.

How should I proceed?

Improve this question
0

3 Answers 3

Reset to default
1

How should I proceed?

You have answered your own question here:

I can do it, but it will significantly increase the development and maintenance time.

Present two options to your boss:

  1. If we use an ORM (or in general, your preferred approach), it will X weeks to develop.
  2. If we put everything in the database (or in general, your boss's preferred approach), it will take Y weeks to develop. Additional features will also take Z% longer to develop.

Your boss can then make the decision.

Improve this answer
3
  • Here is something I don't understand though. If implementing security on application server is not secure, why is that widely accepted practice? I can't even find guides /documentation to change users in popular ORMs like EF Core, Hibernate and Prisma.
    – Hashir
    Commented Nov 22, 2022 at 12:40
  • Don't think of it as "secure" vs "not secure", security is always a trade-off. Having more fine-grained security boundaries in your database potentially makes things more secure, but it comes at a cost. Only you/your company can evaluate whether the benefits in your case outweigh the costs - that will depend on your business model and the specific threats you are trying to protect against.
    – Philip Kendall
    Commented Nov 22, 2022 at 12:46
  • 1
    @Hashir security is ALWAYS relative. In a high sec environment you'd do security both on the application server, in the database, and at infrastructure level. You CAN do proper security at either, it's better to do it everywhere so as to minimise the risk of every conceivable intrusion failing.
    – jwenting
    Commented Nov 22, 2022 at 14:14
1

create all users with appropriate roles ... application would then connect ... using the credentials of each user

You boss should know better than to [want to] try and manage credentials for any significant number of Users. Just the hassle of resetting the passwords whenever they forget them should have soured them on this idea years ago.

The Application should have a single, dedicated account with all the privileges it requires to do whatever it needs to do. That includes being able to reset a users "password" on their behalf (without this, the user can't reset their forgotten, database password because they can't log into the database through the application because they've forgotten their password!)

However, anyone accessing the database directly (e.g. Developers and Testers) needs to be treated in exactly the way your boss describes. This ensures you can have proper Approvals processes for gaining access, plus regular Auditing and removal process and maintains individual accountability over actions (i.e. the database knows who did what).

... all data validations in database using triggers ...

OK, it keeps the database "clean", but:

  • It makes development much slower.
    Not all Developers are sufficiently database "savvy" to make this work [well] and it requires knowledge of multiple languages (your coding language, plus procedural SQL)
  • It makes for a lousy User Experience.
    Your Users only find out anything's wrong when you hit "Save".

Play to your strengths.
Let the database do what it does well - individual column constraints, foreign keys and the like.
Put the more "fluid" / "complex" / "problematic" stuff in the application layer, where you can deal with it more effectively.

Improve this answer
2
  • Thanks! That is a lot of practical problems that can occur if I use database for persisting all user's information.
    – Hashir
    Commented Nov 22, 2022 at 16:34
  • Oh, you probably still need to "persist" all the Users' data, just not in the /Database's/ "User" table(s)! Instead, you manage them like any other Application data.
    – Phill W.
    Commented Nov 23, 2022 at 13:35
0

Essentially no web company does it in the database; many use databases which don't even support the concept (common in NoSQL), and having to re-create the database connection on authentication has a performance cost.

There's an additional problem: your DBA is assuming that all the data is specific to only one user, and quite often this isn't the case. Things like administrative settings, adverts, and so on aren't owned by one user. If you need to write to one of those .. do you make it writable by all users?

Improve this answer
2
  • 1
    any company who knows their security will have database level security in place. Sadly most "web companies" don't have a clue about security.
    – jwenting
    Commented Nov 22, 2022 at 14:15
  • Au contraire, the DBA is assuming the database will be accesed by other means other than thru the app. The DBA is assuming there will be programmers, back-end testers, junior DBAs, interface-scripts, ad-hoc reporting, BI, or ETL tools, and perhaps other apps with a different front-end and or middle-tier accessing the database. Both the OP and the DBA have valid arguments and they should meet in the middle.
    – Tulains Córdova
    Commented Nov 22, 2022 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.