December 13, 2002 at 12:45 pm
I confused on how and when to use database application roles. Can any help clear this up.
Currently, I have NT Security Groups that are sql logins and I create specific roles for these groups and assign permissions as needed. I've been trying to shift all security to integrated. Heres a snapshot of what I've been putting into place. Any thoughts on this structure would also be appreciated.
Thanks
Active Directory Security Groups:
App1 Admin Group
----User1
----User2
App1 ReadOnly Group
----User3
----User4
SQL Server Security Logins
-----App1 Admin Group
-----App1 ReadOnly Group
App1 Database Users
-----App1 Admin Group
-----App1 ReadOnly Group
App1 Database Roles
-----Admin
----------App1 Admin Group
-----ReadOnly
----------App1 ReadOnly Group
December 13, 2002 at 2:03 pm
I'm sure Andy will chime in soon. This is his baby, but if you use std or integrated security, those users can access your db with any tool, excel, Access, etc.
App roles can secure this to ensure only your application is being used to work in the database.
Steve Jones
December 13, 2002 at 2:14 pm
My first question - are you just defining roles for the application, or true "application" roles as defined by SQL?
Having an admin and a readonly role (regardless of type above) is a great paradigm, rarely do I use more roles than that per application.
The downside of integrated security used without a true application role to support is that you're granting the person access to the db using the tool of their choice - your application, Query Analyzer, MS Query, even VBScript. Not usually what you want. If you use an application role, then you have to put a password in the app.
Depending on where you're heading, I've got a couple recent articles about the reasons why sql logins are better for this type of situation.
Andy
December 13, 2002 at 2:52 pm
These are standard roles. I'm not sure what and how to use application roles. I'm looking for information, guidance, best practices. The more the better.
Currently, I have found that SA was used in DSN connections both on the users workstation and in the servers asp pages. I'm not a security expert, but this was obvious even to me.
I'm in the process of moving database from multiple small servers to a new single server. I've been resisting anyone who wants a sql server user in all but the most critical cases. I've been creating AD groups for all the rest and putting the appropriate group in a role I create in SS2K.
My hope is to be able to use AD to control database security, thus the role of ADMIN is not SQLServer ADMIN, but the power users of the app. The role ReadOnly is the other end.
After reading the little bit I've found on application roles, I was curious if I was heading the correct direction.
December 13, 2002 at 3:26 pm
Nothing wrong with your idea, using AD does reduce the overhead needed to maintain access. If you're going to do this, you either live with the potential security gap I mentioned earlier, or implement application roles.
http://www.sqlservercentral.com/columnists/awarren/thecaseforsqlloginspart1.asp
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply