September 23, 2009 at 2:44 am
Does anyone know how to get round a issue I am having.
Where I work the Risk team want the Security Administration on the SQL Servers to be done by another team. However this team should just be able to Create, remove logins, users, assigns users to roles and password changes and have no access to user data. The current Server level role securityadmin does not allow the individual to create the access to the DB as you need to give it permission to each DB on the server. This role will not allow you to create a user with another server role either. It will create the user though which is annoying. Same thing happens at DB level if you give the db_securityadmin and db_accessadmin role to the security user it can not create a user with the db_datareader role.
Is anyone aware of a tool that could do the required work or know what is going wrong?
September 23, 2009 at 3:25 am
Unfortunately for you that all sounds about right.. If they can grant access then there is nothing to prevent them from granting access to themselves, therefore by default they have the capability to access the data.
I also think this particular seperation of duties does not truly make sense.. A better way is to give the DBA rights and then monitor their use. Also if you have some kind of a ticket system you can tie requests to grants.
CEWII
September 23, 2009 at 8:13 am
Your risk team is approaching such access incorrectly based on a full understanding of Windows authentication and Active Directory. Here's the best way to approach it:
- All access within SQL Server is tied to Windows groups.
- The DBAs administer security access within SQL Server.
- Another team administers the Windows users and groups (which is typical).
- All changes to any part of SQL Server security is tied to a strict change control procedure which is verified and enforced.
- All changes to the Windows security environment is tied to a strict change control procedure which is verified and enforced.
- Regular, automated auditing checks for changes in sys.server_permissions as well as in each database's sys.database_permissions catalog views.
- Regular, automated auditing checks for changes in sys.server_principals as well as in each database's sys.database_principals catalog views.
- Regular, automated auditing checks for changes in sys.server_role_members as well as in each database's sys.database_role_members catalog views.
- Regular, automated auditing checks for changes in Windows Active Directory user accounts, security groups, and security group membership.
Now for auditor speak: The last four provide detective controls over SQL Server and Active Directory, which are really the only ones effective in this case, regardless of who is administering security at either level. The privileges they wield do not permit preventive controls to work and unless you have some sort of system that can roll back changes (and knows which ones to roll back), there aren't really any corrective controls that are efficient. And there are third party tools like BindView Control & Compliance Suite that can be set up to perform these detective controls.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply