March 26, 2012 at 6:29 am
I am taking over a bunch of database servers where in the past if anyone needed access to a SQL Server they would add the individual domain name to the databases and gave them whatever Database role rights they needed (datareader, datawriter, etc..). This is very unmanageable as some DB's have over 80-100 individual users.
I want to start adding Domain Security groups and give these groups database roles. Then slowly start removing the individual access and adding them to the correct Security group.
Do any DBA Admins already do this and if you can share your experience or best practice, for example
What naming convention do you for your security group something like "SG-ServerName-DBName-DBRead"?
Do you have a separate Networking\IT Security team that adds users for you and you add the SG into the appropriate database?
If you have more than 10 database servers with 10 individual databases in each, do you create 100+ Security groups?
Just wanting to get a feel on how others are handling DB security access with Security groups, if you have any articles you can share or your suggestions. Thanks
March 26, 2012 at 9:07 am
dbdmora (3/26/2012)
I am taking over a bunch of database servers where in the past if anyone needed access to a SQL Server they would add the individual domain name to the databases and gave them whatever Database role rights they needed (datareader, datawriter, etc..). This is very unmanageable as some DB's have over 80-100 individual users.
Yes, that would become unmanageable in a hurry, especially when you consider what to do when someone leaves the company. If the Active Directory (AD) admins delete the AD Accounts of people who have left the company, I bet you have a lot of SQL Server Logins (and related Database users) that no longer relate to an AD Account. Use sys.sp_validatelogins to determine SQL Server Logins that need cleaning up.
I want to start adding Domain Security groups and give these groups database roles. Then slowly start removing the individual access and adding them to the correct Security group.
Windows Groups is the recommended way to manage authentication, while Database Roles is the recommended way to manage authorization. Make sure you are evaluating each independently before deciding on a way forward.
Do any DBA Admins already do this and if you can share your experience or best practice, for example
What naming convention do you for your security group something like "SG-ServerName-DBName-DBRead"?
Do you have a separate Networking\IT Security team that adds users for you and you add the SG into the appropriate database?
If you have more than 10 database servers with 10 individual databases in each, do you create 100+ Security groups?
Just wanting to get a feel on how others are handling DB security access with Security groups, if you have any articles you can share or your suggestions. Thanks
I look to create Windows Groups for each logical group of people, e.g. HumanResources, Finance, Accounting, Procurement, etc. Start general, and get more granular as necessary.
Within each database I look to create Database Roles that correspond to specific applications, e.g. SomeAppAdmin, SomeAppReader. Same approach as Logins: Start general, and get more granular as necessary.
The principle I try to adhere, where possible, and there are always exceptions, is to only create Serevr Logins based on Windows Groups. For databases, I have a hard and fast rule, I only grant access to dataabse resources via Database Roles, even if the Role has one member, I always create a Role and grant access to that, never to a Database User directly.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 5, 2012 at 8:38 am
Hello opc.three, I apologize for not replying sooner. I been putting out a lot of fires out.
We have run sp_validatelogins on all our servers and found a few orphan logins, thanks for the tip.
Just so I understand correctly, you suggest in addition to creating AD Security groups, we should also create new Database roles to go with these Security groups? If I create a HumanResourcesDB security group in AD, I should also create a Database role HumanResourcesDataReader role? Then give the Security group this role instead of giving them the default DataReader role?
Thanks
opc.three (3/26/2012)
dbdmora (3/26/2012)
I am taking over a bunch of database servers where in the past if anyone needed access to a SQL Server they would add the individual domain name to the databases and gave them whatever Database role rights they needed (datareader, datawriter, etc..). This is very unmanageable as some DB's have over 80-100 individual users.Yes, that would become unmanageable in a hurry, especially when you consider what to do when someone leaves the company. If the Active Directory (AD) admins delete the AD Accounts of people who have left the company, I bet you have a lot of SQL Server Logins (and related Database users) that no longer relate to an AD Account. Use sys.sp_validatelogins to determine SQL Server Logins that need cleaning up.
I want to start adding Domain Security groups and give these groups database roles. Then slowly start removing the individual access and adding them to the correct Security group.
Windows Groups is the recommended way to manage authentication, while Database Roles is the recommended way to manage authorization. Make sure you are evaluating each independently before deciding on a way forward.
Do any DBA Admins already do this and if you can share your experience or best practice, for example
What naming convention do you for your security group something like "SG-ServerName-DBName-DBRead"?
Do you have a separate Networking\IT Security team that adds users for you and you add the SG into the appropriate database?
If you have more than 10 database servers with 10 individual databases in each, do you create 100+ Security groups?
Just wanting to get a feel on how others are handling DB security access with Security groups, if you have any articles you can share or your suggestions. Thanks
I look to create Windows Groups for each logical group of people, e.g. HumanResources, Finance, Accounting, Procurement, etc. Start general, and get more granular as necessary.
Within each database I look to create Database Roles that correspond to specific applications, e.g. SomeAppAdmin, SomeAppReader. Same approach as Logins: Start general, and get more granular as necessary.
The principle I try to adhere, where possible, and there are always exceptions, is to only create Serevr Logins based on Windows Groups. For databases, I have a hard and fast rule, I only grant access to dataabse resources via Database Roles, even if the Role has one member, I always create a Role and grant access to that, never to a Database User directly.
June 5, 2012 at 9:14 am
dbdmora (6/5/2012)
Hello opc.three, I apologize for not replying sooner. I been putting out a lot of fires out.We have run sp_validatelogins on all our servers and found a few orphan logins, thanks for the tip.
Just so I understand correctly, you suggest in addition to creating AD Security groups, we should also create new Database roles to go with these Security groups? If I create a HumanResourcesDB security group in AD, I should also create a Database role HumanResourcesDataReader role? Then give the Security group this role instead of giving them the default DataReader role?
Thanks
Ideally you would not grant anyone db_datareader membership, directly nor indirectly via a User-defined Database Role. Why do they need db_datareader? If it is a legacy app and you have to support it then I would add the User-defined Database Role to db_datareader instead of adding Database Users directly.
Ideally you will grant specific permissions to your User-defined Database Roles, but not make them members of any Fixed Database Roles. If you're using stored procedures (ideal) then no one should have so much as a single GRANT directly to a table, only EXEC permissions to stored procedures.
If you must allow users to issue ad hoc SQL then at the most a User-defined Database Role can have permissions to access a VIEW, but not a TABLE directly.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply