Our application is developed in .net WinForms with SQL server, it has its own user authentication & profile authorization mechanism mapped to SQL login / user / role, deployed in DMZ network, accessible only for those who are authorized.
A prospective client’s IT department complains about maintaining hundreds of logins / user / roles in SQL server, they want only one SQL login / user attached to single SQL server role and rest of the security things must be managed in client application.
a) Instead of having a login for each database user, Can I reduce the number sql logins by mapping to NT group and link it to the application user profile? Is there a better way?
b) How can I reduce number of database users without losing the possibility of auditing? there are 150 active users each one should be traced.
CodePudding user response：
It all depends how secure you need to be.
When talking about client applications, you already have the problem that client tools - by their very nature - susceptible to being abused; for example, if I have a copy of a client application, it is relatively trivial for me to decompile / reverse engineer whatever it is doing, and change the logic - completely bypassing any and all security in the client application. You can potentially still audit at the database server, but honestly: in most cases, unless you catch malicious database access in the act, it will be very hard to know what happened - the client software can probably just not perform, or perform confusingly, any audit steps. Now: if you take this situation and use a single login, with the client connecting directly: the problem gets worse: those login details will need to be embedded in the application so that the application can log in on behalf of the shared credentials (which means: any attacker can get hold of them), and if you do catch something in the act: you'll see that it happened from those shared credentials - unless you can look at IP data etc.
If this isn't an attack vector for you: then sure, just do what you need - but the fact that you mention auditing means that this is probably undesirable.
I would suggest, instead, to consider making the client talk indirectly to the database. If the client application only talked via web services (of any kind of your choosing), then the client has a much more restricted set of things that they can do. You can use your existing security model on the web-service layer, and then once you've authenticated etc the user, switch to a trusted subsystem model for talking to the database with a single credential - probably the AD account that is running the web-services. The web server is a much harder target for attack and abuse. You can now keep all your logic, security checks, auditing, etc in the web-service tier, not the client (you can of course duplicate those checks at the client, to provide immediate UI feedback of disallowed operations, without bothering the web service). This gives you the best of both worlds and potentially allowing better remote usage. However, it almost certainly means a rewrite of the entire data tier of your application.