June 10, 2013 at 3:13 pm
My colleague has received a new application to maintain, written in 4G language.
The users have their own, unique credentials to connect to this application, BUT the application itself connects to MSSQL
(there are various servers, running 2005 or 2008 versions) using only one, application dedicated, login. He thought to advise as well
to allocate unique sql login to each user, but in preliminary discussions he was told that there is no benefit for them to go that route,
as it will only add an additional layer of maintenance.
May I have your pros and cons what are the advantages to have unique sql login for each of the users as oppose of having only one,
application dedicated login, please?
Much appreciated!
June 10, 2013 at 9:05 pm
Some benefits:
Auditing - if everyone logs in as themselves then you know who is doing what. If you want capture who changed a row you can use built in T-SQL functions like ORIGINAL_LOGIN() to log their name in an audit column. If everyone uses a service account you lose that information.
Troubleshooting - same reasons as above. If a process goes haywire you can easily know who is running what in the database if everyone logs in as themselves.
The recommendation is to use Windows Authentication and Active Directory Groups in your situation. This way you only setup one Server Login for the Group, and then one Database User based on that Login. Then create a Database Role, add the User to the Role, and grant all permissions to the Role. When new people are hired and need access to the application, add their Active Directory account to the Group and you're done. Not much maintenance headaches there, I say, and you retain visibility into who is doing what.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 11, 2013 at 12:16 am
in addition to the post of opc.three:
when using the users credentials (preferably through Windows Authentication) you could restrict rights of some users/groups. You could prevent users from accessing or updating specific tables, etc.
June 11, 2013 at 3:37 am
Thanks for the ideas!
In terms of auditing we thought as well that SQL is better, but the customer insists they do better auditing within their 4G application.
Thanks again
June 11, 2013 at 3:49 am
Zeev Kazhdan (6/11/2013)
Thanks for the ideas!In terms of auditing we thought as well that SQL is better, but the customer insists they do better auditing within their 4G application.
Thanks again
Nothing should stop you to do the double auditing if you wish.
Database level auditing in additon to application, will allow to audit database actions if they happen outside of "their" 4G application. Let say some connect directly to SQL server using their Windows account via SSMS or even Excel.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply