Windows vs Mixed Mode Authentication
-
I am developing an app in VB.NET that will have SQL Server 2000 as the database and I have to decide between the authentication. I am thinking, to use Windows Authentication. Have one userid to access the database and in the database, define my own user table so that person who logs into the system will be validated against the table. That way I won' t have to maintain multiple user and their roles at SQL Server level. If I use this approach over mixed mode, am I losing anything?
-
I think you are wanting to use SQL Server/Windows security...
You sound like you want to define a single SQL userid that connects to your application and then manage a list of Windows Userids in a table.
Why not do this instead...
Create an NT group. Place all the users of your application in it. Add the Group to your SQL server with the correct permissions. Anytime someone new comes along the I\S security people who manage groups will add the person into the group and BOOM security resolved. Along with this is IF someone leaves they remove them from the group.
This option would remove you from being the security gatekeeper and leave it in the hands of security.
IMO,
Good Hunting!AJ Ahrens
webmaster@kritter.net -
good point. i for sure don't want to be the security administrator.
-
AJ is absolutely correct. Plus, you'll gain the advantage of logging (if you choose to do so) user activity -- ie, add LASTMOD_NM, LASTMOD_DT columns to your tables, add some triggers and store the username with the record on updates, add, etc with the USER_NAME() function.
Check out this disucssion:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=32&messageid=9888#bm53867
I think you'll find a number of good points in there.
-
Yaip, FWIW as a developer not a dba I've always done what you proposed ie use one user id in the code and a user table, for the reason that the security issues invariably revolve around features of the app(s), not about permissions to particular tables, SP's etc; and also because in many apps a user table has been necessary anyway eg to provide features such as ' refer this item to your supervisor'
-
Thank you for confirming that. I was getting worried about doing something terribly wrong. By the way, what if FWIW?
-
For What It's Worth ! = I don't want to get into an argument with pro dba's on their own turf - but as a developer, I've never personally seen a way of using the SQL Server permissions effectively to do what the app needs to do - most obvious/frequent example is to give different users access to different areas of the app, not so much to different areas of the db; and to make that visible to the user by eg greying-out buttons or menu items. How would you do that using the db permissions? Try to hit every table on startup and trap the resulting errors?
And what if it's to part of the app that doesn't map onto database permissions anyway, like, um, 'change report paper to headed', or something? Right away you've got yourself a tblUsers anyway.Also in my experience the 'who does the admin' argument doen't hold much water either, because invariably it's the 'boss user' who wants to control who does what on 'their' system, and they don't especially want to have to email someone in IS every time they want to change something- they want to be able to do it themselves.
Hope you find this reassuring!
pg
-
Why not have an NT group built PER Role for the application... This way again you are not in charge of Suzy cant do this, or Bill can do this. If they are in the wrong group(s) it is NOT your fault.
As far as security goes as well, in your application you can reference what permissions they have and enable/disable functionality inside the application. I.E. members of user role can't do admin function but admin role can do user functions.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I have worked in shops where ther have been dbas, security admins, operators, network specialists, etc. My role there was that of an application developer. This situation is different though.
This is a ver small shop where I have to be the dba as well as the developer. Under these circumstances, I would like to concentrate on what I love to do rather than what I have to do. I tend to agree with pg as I know what I've gone thru'.
And I know that in this shop, no matter how right I will be, if it has to with computers, it will ALWAYS be my fault. Why computers? If the user could blame me for non-computer related stuff, it WILL be my fault.
-
urrrrr, well you can't win anyway then, so time to find a new job.
I like having:
NT Users---->NT Group---->DB Role---->ApplicationUserTable
NT User---->NT Group gives HR and the NT security group ability to manage the security of the environment. Once someone is revoked from a group, it shouldn't matter what permissions they have in an application. They can no longer do anything.
NT Group---->DB Role allows the DBA to manage the integrity and security of the data. By properly defining these roles, they can report effectively the state and visibility of data. This is defined at a group level and does not need to be micro-managed in most cases.
DB Role---->ApplicationUserTable allows the developers and "Boss Users" or application security personnel to effectively manage what the user can do in the application. This naturally will limit what parts of the database can be seen edited, etc within the application itself. It translates to a more granular security of the database.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
This is great information ( even more than I ever expected to get ). But I still would like to know if there is a book/site explaining the "suttle" differences between Windows Authentication vs Mixed Mode Authentication when using SQL Server.
-
OK.
About "differences between Windows Authentication vs Mixed Mode Authentication when using SQL Server", well, functionally there are none.
Once inside the DB, everything works as advertised.
However, from the User Rights management point of view you are much better off with mixed mode vs. internal or windows only.
Why?...
Some Software only works well with an Internal System Administrator password. Strangely, I found Backup software that falls into this category. So you need SQL Security.
Another reason whay you need SQL security is to create "backdoors", or, in a more politically correct phrasing, "maintenance mode functions" in your applications (know the F11 key in Access? Ever tried to "relocate" it to "CTRL-F11" or similar?). Imagine that you are in Sally's machine (with her User ID) and she found an error on your application. Now Sally is the CFO and she's in a hurry with the annual report - she needs it fixed right away - and it's an easy fix. But, with Sally's ID you're never going to make it. That's when you fire that clever option that allows you to enter a User ID/Password for that "maintenance mode" that you cleverly programmed in your application - for instance, by pressing a special function key in a menu... And bingo!... you (and your important User) are off the hook.
For management reasons (security, accountability and so on), you should use Windows security as the "normal" way.
For best practice, you should set up User groups in the AD (or Windows security, if not running Active Directory) and use them in the application for attributing security permissions (easier that way!). This takes the burden of determining "who's who" away from you but still gives you full control.
I hope I saved you some reading. What I said is NOT endorsed probably by any DBA. It's just that I'm in this position of yours - manage and do at the same time and I've got to be ... well, practical.
Leo Brum
-
Oh thamk you. Thank you. Thank you.
Even if I head read a thousand pages, I would never have gotten the "true" reason. This is exactly I needed.
I moticed you mentioning AD(Active Directory). Since this is the first time I have to implement Windows Security, I have to learn a lot of new concepts. AD seems to be one of them. Is it recommended that one uses it? If yes, do you know of any good resources?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply