August 21, 2008 at 9:49 pm
Comments posted to this topic are about the item Managing Security
August 22, 2008 at 1:39 am
For the majority of users I'll reuse the AD groups, which are setup for departments/roles, as I deal with both sides of the system and when I make a user I don't want to have to remeber to add them to all of the other systems that are needed. I try as much as possible to keep to groups, as you say even for a single user as one day that employee may move on and someone else will take their place, I don't have time to look at all of the systems to see what a job role should have permissons to, I want to set it up once and then leave it at that. When adding a new user I can always copy the existing AD user account and it will take on the AD group memberships.
August 22, 2008 at 4:47 am
An interesting and relevant question it is indeed, and an issue I'm struggling with nowadays.
I tend to create an AD-group for every single role that I discover, but it leads to a lot of AD-groups. For instance, I have a Developers group, but when we get a new developer working for us, s/he is not placed in this group, but in the junior-developer group, with different permissions.
But what do I do if I have 2 new developers, and they are not allowed to work in each other's database? Do I set up a junior-developer-database1 group, and a junior-developer-database2 group?
Any ideas about how to deal with this?
regards,
Hans
August 22, 2008 at 5:12 am
I always try to create the minimum number of roles possible. If permissions need to split later, I split them at that time rather than trying to anticipate that two of the Jr. developers will need access to one database and the other three Jr. developers will not.
In most of the companies I have worked with, I have not been an administrator for AD, but I am often in the admin group on the SQL Servers. So, at times I will also add an additional buffer of local server groups and give the local server groups access to the SQL Server. It is just one more level in which I can move users and groups around to try to keep my SQL Server role count down.
August 22, 2008 at 5:30 am
Now it may just be due to the way that the companies I've work for work, and the way that I do it, but in the context of the post above, I would have had and AD Group call Developers which gives the generic permissions needed and (depending on requirements) I may have made a Developers would have the editing permissions needed in SQL for the database. But that probably helps with keeping the databases seperate for different projects and functions. It's almost the seperate silos situation but attempting to use consistent naming and centralised security management/administration to make life easier.
I feel that as a SQL Administrator you should have at least some AD Administration deligated to you for user management.
August 22, 2008 at 5:31 am
We don't always duplicate the AD groups and the Roles in the databases (we use Roles exactly as you described), but we frequently do. I don't mind lumping more than one AD group into a Role, but if I've got an explicit set of behaviors, I create a 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
August 22, 2008 at 6:30 am
I have always just used the same AD groups to apply permissions to them in a role. Although I do recall discussing how we were going to set up security in a new application and because of some weird user assignments (team lead, etc), we created new AD groups to map to the different roles in this application. Then someone could be "promoted" but they would also be a part of their old AD security group that handled their network access.
I guess the security where I am now is a little simpler since almost everyone I work with has the SA passwords. Then again, in a team of 8 developers this isn't too bad - yet.
August 22, 2008 at 8:14 am
🙂 It's the "stinky" onion approach. I generally use the "base" component model for all uses (i.e. Office 2007, etc.)
add layers of specific products (i.e. DiskKeeper, etc,) to AD group models by region, by product, by function.
This means multiple deployment models for software, database usage, etc.
By region example: Region 1 and 2 use Oracle. Region 3 and 4 use SQL 200X.
By product example: Application X uses SQL 200X DBMS and users are defined as Local DBA, Power User, etc.
By function Example: Insurance Adjusters don't have admin. rights to their computers. Any software installed or executed on their machine needs to have elevated rights for proper execution, specifically if the software needs to create, delete, and/or alter files or registry settings.
Hope this helps...Gene
August 22, 2008 at 8:29 am
This may not be the BEST way, but I typically use the main AD groups and then if special permissions are needed that only apply to SQL Server then I would create a Role and directly add AD users to the server and that Role. Rarely happens for me as basically everything I do is web-based and we have not gone through the pain of making Trusted Authentication work so I have a Web User login to the database and do application security within the application.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2008 at 9:10 am
Like Steve (and many others) I've had great success with using SQL roles to manage database permissions and adding AD groups to those roles. The pertinent question is, does the AD group align with the SQL role? If the answer is yes always then I'll use the AD group, if not, I'll have a secondary AD group added and create the necessary SQL role. My goal is to simplify the administration of SQL permissions and use a tool that's easy to move people in and out of the functions (AD). This allows people management to remain in AD.
Another security feature I'm using is to leverage the schema's and assigning ownership to dbo but granting permissions to roles. This has also worked quite well.
--Paul Hunter
August 22, 2008 at 9:41 am
I believe security groups should apply to a single resource. It is so easy to "leverage" existing groups to grant access to a new resource, but it inevitably leads to incrrect access somewhere else. The overhead to create the new groups can be a burden, but in the long term, the maintenance is much easier, DB moves to a new server, people come and go...etc. I've participated in too many "Group Clean-up" activities to advocate anything else!
August 22, 2008 at 10:08 am
Thanks for asking this question.
About three years back I architected an approach to use a set of web services against an Active Directory Application Mode, ADAM, store. The ADAM Store works in conjunction with Authorization Manager for AD users and through a developed Application called ASAM we wrote to manage internet users.
In the strategy we use both roles and groups, users are added to groups and groups are added to roles. Depending on the memberships of the users we are able to getroles and thus determine the appropriate privilege for the user.
Using this approach we are able to include both internet and intranet users within the same store and access them in a like manor during execution. The approach also allows us a simplified approach to single or limited signon for applications.
So the short answer is that we do not use AD or AD groups but we have extended this approach to a more robust ADAM solution
Miles...
Not all gray hairs are Dinosaurs!
August 23, 2008 at 12:36 am
I think this whole role based security thing is obsolete. What we need is a three or four dimensional security model. The dimensions are user group (admin/data entry operator), region and department. Permissions are specified for every user group, region and department separately and each user is assigned a user group, region and department. So each user is given a complex combination of permissions inherited three dimensionally from the user group, region and department he belongs to.
Now if you define 3 user groups, 4 regions and 5 departments a user can be given one of 3*4*5 = 60 different combination of permissions. So instead of creating 60 roles you create only 3+4+5 = 12 roles under 3 dimensions. So administering security becomes much simpler.
Now who will tell Microsoft to implement this?
August 23, 2008 at 7:24 am
I find the whole question to be moot. SQL Server access should only be granted to support groups and to applications and no users and no developers should be allowed any access. This includes reports, where ad-hoc queries should not be allowed.
For administrative support of SQL Server, there are really only two groups needed - DBAs and the security department, so there are only two active directory groups needed and the Security department is a SQL Server specific AD group that exists in all servers.
I will note that jobs are not run by the SQL Server Agent but instead a different product is used that supports complex job inter-dependies. If Job X, that accesses SQL Server, cannot start until after the successful completion of both Jobs A and B, that access a different servers running different OSs and/or different DBMSs, how do you define this dependency to SQL Server? The job scheduler product uses SQL Server as its repository.
SQL = Scarcely Qualifies as a Language
August 24, 2008 at 11:18 am
Carl Federl (8/23/2008)
I find the whole question to be moot. SQL Server access should only be granted to support groups and to applications and no users and no developers should be allowed any access. This includes reports, where ad-hoc queries should not be allowed.For administrative support of SQL Server, there are really only two groups needed - DBAs and the security department, so there are only two active directory groups needed and the Security department is a SQL Server specific AD group that exists in all servers.
I will note that jobs are not run by the SQL Server Agent but instead a different product is used that supports complex job inter-dependies. If Job X, that accesses SQL Server, cannot start until after the successful completion of both Jobs A and B, that access a different servers running different OSs and/or different DBMSs, how do you define this dependency to SQL Server? The job scheduler product uses SQL Server as its repository.
Adhoc queries is the punch line of our system and what makes it so popular to our clients. Fortunately the people I work with and provide support to is professional and with good security on your database/s you can actually allow them to do adhoc queries. I am also quite fortunate that where I work DBA's and developers are not enemies and we do not try to undermine each other. I must say that some people think that because they are DBA's that they are a superior race that developers must worship. Fortunately not all DBA's are like that.
I guess the security where I am now is a little simpler since almost everyone I work with has the SA passwords. Then again, in a team of 8 developers this isn't too bad - yet.
I read a book written by Brad McGehee where he states that should you need to access the database with sysadmin rights to rather create another login with sysadmin rights and not use the SA login at all. I don't actually know why so if anyone can enlighten me?
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply