August 5, 2010 at 6:29 am
Adding domain groups as sql logins is risky unless the data admin people control who goes in and out of such groups.
We have a screwy arrangement right now. Somehow an office manager is assigning permissions, which wind up as a CRM task/ticket for the group that actually grants the permissions to various systems, folders and servers.
So recently, out of pure ignorance, she sent a ticket to our Systems group to add a domain group as a read/write dbowner on our new production client database. She uses Outlook to try and see who is in the group but there isn't a strong connection between Outlook groups and domain groups.
Apparently Systems went ahead and did this. I'm still acting DBA ( with the hope of that being formalized soon) so when I noticed this change, I used Sysinternals active directory program to see who was in the group and it included help desk folks with no sql training at all.
So I discussed it with our data architect and we agreed to remove that group . Those in that group who had a legitimate right to access already had individual logins with customized permissions. He and I decided long ago that individual logins was the way to go for us.
If you had to grant sql access to dozens and dozens of people you might want a plan B, but for us it's about a dozen.
You can use the DOS command line:
net group /domain "group name in quotes" but it won't reveal groups inside of groups.
August 5, 2010 at 11:28 am
Looks like everybody agrees with me.
August 5, 2010 at 11:37 am
I dont exactly agree with you. We have strickly defined groups for our DBA's, developers etc. Users do not go into those groups without upper management approval. We do add these domain groups to certain SQL servers where the access is needed and have yet to see any problem. That being said it may be only a matter of time until we do then I will deny everything i said here. 🙂
In most cases though we do use indivudual logins but in the cases where we need to add over 10 or so, we go the group route.
August 5, 2010 at 11:43 am
Makes sense Steve. My issue would be that generally upper management would not have any clue about who should have read and/or write access. Even read access could be bad for the untrained -- e.g. select * from hugeTable ( no where clause )
Beyond that, if those who know sql server don't have either easy access to the contents of groups or automatic notification when group contents change, it's just a matter of time before you have a security problem. How thrilled would most systems teams be about having to report voluntarily to DBAs?
August 5, 2010 at 12:00 pm
Not using groups becomes impractical at a certain point.
Trying to add individual logins to SQL Servers when you have hundreds of servers, thousands of databases, and 20,000+ users is a nightmare job.
August 5, 2010 at 12:02 pm
I completely agree with that many users. Completely impractical though it probably could be scripted and not be that painful. 🙂
Groups are definitely the way to go there though.
August 11, 2010 at 5:32 am
The case of upper management not knowing who needs what access in SQL Server should not be the case in the first place. In most large organizations, that task falls on the project leads and they of all people, should be knowledgeable enough to know who needs what access..
If that is not the case with you then you have my sympathies...:-)
August 12, 2010 at 9:52 am
We don't use general domain groups for managing access to SQL Server. We create security groups in AD for managing specific sets of access and individual users have to be added to the appropriate group. Anyone can request to be added to a specific group, but the operations team manages the groups and controls who actually gets added or not.
August 12, 2010 at 10:03 am
I think much of this depends on your company and whether the Systems/Network admin group has any overlap with the database staff. If not, the DBA-types may not have any control and/or visibility into group contents -- thus our choice not to use groups.
As far as management not having a clue about sql logins/permissions -- unless you're talking about the manager who supervises DBAs or possibly the CIO/CTO, I would think that would be the rule rather than the exception.
August 13, 2010 at 2:59 pm
Indianrock (8/5/2010)
Adding domain groups as sql logins is risky unless the data admin people control who goes in and out of such groups....she sent a ticket to our Systems group to add a domain group as a read/write dbowner on our new production client database.
Is it read/write or db_owner you are granting to AD groups? If it is the latter then yes there is significant risk if just about anyone can add members to those groups. Rarely does any individual or group need db_owner and only under the strictest justification.
The great advantage of AD groups is that it lifts the burden of managing thousands of accounts in SQL server by the DBA and hands it over to the application administrators and/or support teams. The DBA then only has to manage small subset of individual (usually just application) and group accounts on SQL server.
It does however require a well designed AD structure, properly maintained, controlled, and audited.
Example 1:
The server-DB-admin group which is in a secure OU (meaning only domain admins can make changes per approved change request.) Members cannot add or delete other members. The Manager is not a member but an approved requester. Only individuals not groups can be added due to the high level access of this AD group--changes are automatically monitored and detected changes sent to a distribution list. Group membership is also audited quarterly by the Chief Security Officer and reported to the CIO.
Example 2:
app-XYZ-admin again is in a secure OU and subject to the above rules. The app-XYZ-admin members can manage the app-XYZ-Grantors and app-XYZ-Users groups. app-XYZ-Grantors can manage the app-XYZ-Users. There are of course several variances in AD groups based on application needs.
Also if I read correctly--the systems group in your company is capable of performing SysAdmin functions on the SQL Servers... such as adding an SQL Login and assigning database permissions? Unless they also officially support SQL server and databases--this is not sound practice. There should be separation of server administrative duties and SQL Server administrative duties--proper change control, scheduling, notifications etc notwithstanding amongst teams. I was in your shoes when I was just forming a DBA team where none existed before. Access management was pitiful with certain guarantee of audit failure and undo risk. Today, with just a few minutes time I can prove that not even Domain Admins can access SQL servers or databases at least not without being caught. It took working together across all of IT along with Management commitment to get there. It never really stops--just keep plugging away at it and it gets better one step at a time.
August 13, 2010 at 5:09 pm
Fortunately in our environment most people authenticate to sql server via a single login coming from the web server ( GUI access to sql, not via any sql tool such as SSMS) They have their own application username and password, but no direct access to sql.
Those needing direct access number less than 20 so it's not much work to manage them individually.
August 16, 2010 at 3:52 am
It is established best practice in the security world that the people who grant rights to a given object should be different to the people who allow people to use those rights. This is one aspect of separation of duties. Depending on the application, this best practice is enforced by legislation in some countries.
A major impact of this on DBAs is that the practice of having unique logins for individual users violates the separation of duties concept. The DBA job should be to ensure that the login account is given the rights it needs, but it should not be part of the DBA job to even know who has use of the account.
Within the Windows world, this means that logins should be created around domain groups, so that the DBA can assign the correct rights to the group. Some other team should then be responsible for authorising people to use a given group. It is the job of management to ensure that procedures exist to ensure to ensure that people are authorised to use only those groups they need in order to do their job.
I know that some DBAs will be uncomfortable with this, especially if they work in an environment where group access is granted without adequate checks being made. However, the proper course of action should be to highlight the poor authorisation process, and avoid making matters worse by setting up unique logins for each user. If the droppings hit the legal fan, a DBA who has tried to enforce separation of duties will be in a far better situation than one who has taken all the duties and risks on themselves.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 10, 2011 at 9:48 am
i have a process set up that dumps audit data from domain controllers to a database and last year i caught a user being added to a group that had R/W to sensitive billing data.
turns out it was a new employee and the person who created the account just copied the account of the hiring manager which added her to all his groups
what i do is email a report from my audit database to a bunch of people and check it myself as well
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply