January 26, 2011 at 6:01 am
I am currently faced with a problem with the way the database SQL security is currently setup.
The scenario is as follows.
They have about 100 users and access to the application is controlled by AD users on the database, so if I as a user needs read only access to 2 sections within the application, I get put within a AD group within the database which has read access to 2 databases which the application makes reference to.
On the same token, since each user requires certain level of permissions which is different across various databases. It poses a problem, For example, we have 10 databases, 3 different role profiles i.e read, write, ddl per database. This means we can have 30 different permutations. Once a user requests access to the application, it goes through an intermediary system to work out if the user has access etc through their domain account, then connects to the backend database.
One easy way to get around this problem is to deal with the permission login within the application itself, rather than on the SQL server side as it then causes administrative overhead and unnecessary point of failure, if the active directory server goes offline then there wouldn’t be any connection to the application.
I am looking at ideas on better managing the security setup since its near impossible to change the application at this point in time.
Thanks.
January 26, 2011 at 6:11 am
The only suggestion I can make is to start using Roles within the database in addition to AD groups. Yes, if the AD server goes offline it can and does affect the database, but let's face it, it also affects all the client boxes, the servers, the application servers... I wouldn't sweat that as a weak point in the system. It's just that you need a better way to manage it.
In general we work off the idea of creating roles within the database. We then have general groups within AD that individuals belong to, and we assign the AD group to a role. This means that the DB team only manages the roles and the AD assignments, not AD membership. That's managed by a security team, seperate from us. We've found it makes life easier. We create a role, assign one or more AD groups to it, and from that point forward, just manage the security for the role.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply