locked
Security admin privilege to application owner RRS feed

  • Question

  • Hi all!

    I am responsible of a mid size SQL Environment managed by third party. The environment consists of various versions and editions of SQL server from ver 2000 std to 2008R2 ent. There is normally tens of databases grouped by several different database owners in one SQL instance. Nowadays our application owners / users must request their permissions through quite bureaucratic process (couple of approvers for the request and 3rd party who grants the permissions) and it takes too much time to be granted from the application owner point of view.

    I was thinking that could we let the application owner to grant permissions to his (and only for his own) database. The idea is that the database owner could create logins & users which would have access only to the defined database. By this way the response time would get shorter and the application owner would be responsible of permissions in his database.

    I´ve been told that to be able to grant permissions for certain database, the application owner must have DB_Owner privilege to the corresponding database and also Security admin -server role (for creating logins).

    The question is: is there any risk to give Security admin -server role to the application owner? Is he able to create/delete/modify any of logins in the SQL instance or only logins that are users in his own database (database where he is Db owner)?

     

    Thanks in advance

     

     

     

    Tuesday, October 18, 2011 11:03 AM

Answers

  • Action: To grant Login1 the permission to create other logins is the ALTER ANY LOGIN permission. Problem: Logins are server wide. Login1 could mess with logins that were intended for other databases. For example, Login1 might decide that Login2 was no longer needed for Database1 so Login1 deletes Login2, not realizing that Login2 also had access to Database2. Mitigation1: Perhaps, in a domain environment, the Windows Users already have access to SQL Server through a login for a Windows group. Then Login1 doesn't have to create logins, only users. Mitigation2: Create a stored procedure that creates logins, but doesn't delete or alter logins. Grant execute permission to the proc, to the logins who you trust to create logins. Then they can add logins, but not mess with them. You are still ceeding control over SQL Server access to someone else so you have to trust these people. I agree about staying away from the securityadmin fixed server role. Members of securityadmin can create new powerful users. Books Online says consider members of securityadmin the same as sysadmin. ALTER ANY LOGIN is still powerful but much less than securityadmin.

    Action: Once you have logins, to create database users grant the ALTER ANY USER permission, and probably the ALTER ANY ROLE permission. These permissions are within the database, so they are safe if people are trusted with the whole database. CONTROL ON DATABASE includes these permissions. It sounds like that's already present.

    These permissions are only available starting with SQL Server 2005, so they are not available for SQL Server 2000. For a list of all the permissions, see the Permissions Poster 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, October 18, 2011 8:09 PM
  • Everyone who can connect to SQL Server is a member of public. You don't have to do anything to arrange that.

    You can create a login for all domain users. If you do, anyone who is authenticated as a domain member will be able to connect to SQL Server, and they will have at least the permissions granted to public. It sounds like that is OK with you.

    You are correct that SQL Server Authentication users will still need to be created individually by someone.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, October 19, 2011 4:07 PM
  • As a follow up, SQL Server 2012 (currently in Release Candidate 0) has the concept of contained databases and contained database users. This allows the owners of databases to create both Windows and SQL Server Authenticated users directly at the database level, without needing a login. This appears to be exactly what you need to solve your problem in the most efficient way. I suggest you look into contained database users so you can consider that when the product is released (and your budget permits). For more information, see Contained Databases http://msdn.microsoft.com/en-us/library/ff929071(SQL.110).aspx and CREATE USER (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173463(sql.110).aspx 
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, October 19, 2011 4:12 PM

All replies

  • Hello,

    I would advise against doing this. The security admin role can do much more than create a login (server level).

    See MSDN: http://msdn.microsoft.com/en-us/library/ms188659.aspx

    If you are looking to expedite the process you'll have to look at the architecture and why the process is bad. You mention that it's very bureaucratic, but that only explains one part. For example: Are they passing a paper sheet around until all signatures are completed and then finally a request is made? Is security taking a long time to investigate the user and the actual need is there? Is it taking the helpdesk/desktop team a long time to install the application?

    In general I never advise, nor would I want, an end-user to have any server level configuration permissions. This may just be me, but if others can get in an change my instances around, how would I know they aren't changing something that *shouldn't* be touched because they didn't know any better? It's a scary thought.

    Hope this helps,
    Sean

    Tuesday, October 18, 2011 2:47 PM
  • Action: To grant Login1 the permission to create other logins is the ALTER ANY LOGIN permission. Problem: Logins are server wide. Login1 could mess with logins that were intended for other databases. For example, Login1 might decide that Login2 was no longer needed for Database1 so Login1 deletes Login2, not realizing that Login2 also had access to Database2. Mitigation1: Perhaps, in a domain environment, the Windows Users already have access to SQL Server through a login for a Windows group. Then Login1 doesn't have to create logins, only users. Mitigation2: Create a stored procedure that creates logins, but doesn't delete or alter logins. Grant execute permission to the proc, to the logins who you trust to create logins. Then they can add logins, but not mess with them. You are still ceeding control over SQL Server access to someone else so you have to trust these people. I agree about staying away from the securityadmin fixed server role. Members of securityadmin can create new powerful users. Books Online says consider members of securityadmin the same as sysadmin. ALTER ANY LOGIN is still powerful but much less than securityadmin.

    Action: Once you have logins, to create database users grant the ALTER ANY USER permission, and probably the ALTER ANY ROLE permission. These permissions are within the database, so they are safe if people are trusted with the whole database. CONTROL ON DATABASE includes these permissions. It sounds like that's already present.

    These permissions are only available starting with SQL Server 2005, so they are not available for SQL Server 2000. For a list of all the permissions, see the Permissions Poster 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, October 18, 2011 8:09 PM
  • Mitigation2: Create a stored procedure that creates logins, but doesn't delete or alter logins. Grant execute permission to the proc, to the logins who you trust to create logins. Then they can add logins, but not mess with them.

    For this to work, you need to sign the procedure with a certificate, and create a login from this certificate which you grant ALTER ANY LOGIN. I describe the process in detail on
    http://www.sommarskog.se/grantperm.html

    Without the signing the users need to have ALTER ANY LOGIN themselves.

    Like the others I recommned against add application admins to security admin.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, October 18, 2011 9:15 PM
  • Thanks for your contribution!

    I am now very convinced the Security admin -server role shouldn't be used.

    Mitigation1: Perhaps, in a domain environment, the Windows Users already have access to SQL Server through a login for a Windows group. Then Login1 doesn't have to create logins, only users.

    Action: Once you have logins, to create database users grant the ALTER ANY USER permission, and probably the ALTER ANY ROLE permission. These permissions are within the database, so they are safe if people are trusted with the whole database. CONTROL ON DATABASE includes these permissions. It sounds like that's already present.

    Related to proposal above, we could grant Public - server role to all domain users (AD group) by default and then grant ALTER ANY USER to the application owner to his databases for creation users. Is there any risks to grant Public  -server role to group that contains all domain users?

    Some of our application users need to use SQL Authentication as well. I guess there is no any similar solution for these people?

     

    Wednesday, October 19, 2011 5:06 AM
  • Everyone who can connect to SQL Server is a member of public. You don't have to do anything to arrange that.

    You can create a login for all domain users. If you do, anyone who is authenticated as a domain member will be able to connect to SQL Server, and they will have at least the permissions granted to public. It sounds like that is OK with you.

    You are correct that SQL Server Authentication users will still need to be created individually by someone.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, October 19, 2011 4:07 PM
  • As a follow up, SQL Server 2012 (currently in Release Candidate 0) has the concept of contained databases and contained database users. This allows the owners of databases to create both Windows and SQL Server Authenticated users directly at the database level, without needing a login. This appears to be exactly what you need to solve your problem in the most efficient way. I suggest you look into contained database users so you can consider that when the product is released (and your budget permits). For more information, see Contained Databases http://msdn.microsoft.com/en-us/library/ff929071(SQL.110).aspx and CREATE USER (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173463(sql.110).aspx 
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, October 19, 2011 4:12 PM
  • Thanks for pointing this out!

    As you stated, contained databases and contained database users are the features that I am basically looking for. Unfortunately they will come with version 2012... so it takes a while when we have migrated our all databases into new environment. Before that, we could use the option to create a login for all domain users.

     

    Friday, October 21, 2011 5:06 AM