July 9, 2009 at 3:59 pm
Hi there everyone,
is it best to have an application, designed that it uses like a single logon to connect to the database?
for example, using application roles or a single domain user or sql account? and only give this user access to the database?
We have an application where all domain users have access to the tables.. so the problem is they can use exel or access to change the data. if we were going to redesign the security, what would be the most perferred option.
cheers
July 9, 2009 at 5:31 pm
have two seperate issues here.
1. All domain users have access to the tables.. How? What role do they have? Are they all administrators? Has the everyone group been given sysadmin? This is a HUGE issue for you, just so that you can know and control what is happening to your server..
2. There are a couple schools of thought on this and a lot depends on your employer and their policies.. My preference is to use a single logon from the application for the app to use, BUT to capture the security principle (domain\username) from the app and user that to check for Active Directory role/group membership. The app can always login, but if the user isn't a member of a required group then the security sprocs tell the app that and the app tells the user that they don't have rights. I'd rather my app be in control than to have a statement fail in a less controlled way. But then again thats me..
CEWII
July 9, 2009 at 8:07 pm
Elliott W (7/9/2009)
have two seperate issues here.1. All domain users have access to the tables.. How? What role do they have? Are they all administrators? Has the everyone group been given sysadmin? This is a HUGE issue for you, just so that you can know and control what is happening to your server..
CEWII
Legacy program - Have a domain group mapped to sql login group mapped to database role group. Database role group has insert/update/delete.. this means the user could use say Access to update data.
We'd like to secure any new apps, so we can control who and what has access to the SQL Box. I was thinking of a few options
SQL Login account
Database Application Role
or AD SQL Login account
is there a preference or does it depend on the polices/environment you work in.. For example, i'm an operation DBA, so it might be easier for me to managed security from AD (1 central user management)
Cheers
July 9, 2009 at 8:24 pm
I think Microsoft offers an Authentication application block that I would look at..
CEWII
July 13, 2009 at 7:18 am
If internal applications are accessing the database, we tend to like a single Windows user login, a service account, if you will. That way individual users can't get direct access to the database; they must come through the application. Also, there is a performance benefit, because resource pooling can be used on the application side, especially if you're talking a tiered application. For external application accessing the database, we use a SQL Server login as we don't allow Internet facing systems to be on a Windows domain.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply