2

I wrote a web application that is using AD authentication (Windows) and has its own authorization module (RBAC-like). Back-end is Microsoft SQL Server.

A DBA on my team is not happy with us using a service account to talk to the database, he would rather have the actual user impersonated using EXECUTE AS at the database level. He would then use actual SQL roles mapped to lock down the access per user. To me it sounds it would be a bad idea as now I have to manage 100s of users at the database level and it just makes the attack surface bigger.

What other arguments for or against this setup exist?

Luc
  • 31,973
  • 8
  • 71
  • 135
NotADba
  • 21
  • 1

2 Answers2

1

Here are a few reasons why using EXECUTE as might be a worse idea:

  • If the web application has SQL injection flaws the attacker could alter their privileges.
  • If the web application has a vulnerability that leads to attacker controlled code on the server (file upload, command injection, etc) then the attacker can execute queries with the privileges of the connection user.

Recommending security solutions without understanding who or what you are protecting against tends to result in ineffective security controls.

wireghoul
  • 5,745
  • 2
  • 17
  • 26
0

Using database users in addition to the application-level authentication can be good defense-in-depth. I used this before, creating one database user for each user role as extra protection. It sounds like you will need application-level authentication anyway: how else will the application know whether to show administrative controls to different users?

If you would, instead, just show all controls to all users, and let the database deny executing certain actions to some users, that sounds like a configuration mistake waiting to happen.

It might also be tempting to let users run their own queries because the database should limit what they can access and modify. Having this type of code execution on the database system indeed increases the attack surface by a lot, as you identified. Various bugs in database servers are only accessible if you can execute a query to exploit it. Database connections (if users would get direct access) are also often not the safest, potentially only doing a protected login procedure but having no protection on the rest of the connection. There's a lot to consider when going down this path.

What would be interesting is to update the question with the DBA's argument(s). As it stands, hearing only your side, it sounds like the DBA is -obviously- a database person, where the database system is his/her hammer and authentication looks like a nail to them. Basically as wireghoul said: "[not] understanding who or what you are protecting against tends to result in ineffective security controls". This could be a concrete thing you can ask the DBA: what does their preferred method protect against that yours doesn't?

Luc
  • 31,973
  • 8
  • 71
  • 135