locked
'User Management' Database Role RRS feed

  • Question

  • Hello

    The database I am currently working on for SQL Server 2010 Express needs to have the ability to allow certain users to manage the logins and roles of users, but I obviously what to avoid potential privilege escalation on those users themselves.

    Basically these users needs to be able to:

    1) Create logins on the server, and possibly be able to disable and enable then.

    2) Create database users.

    3) Assign and remove database roles to users.

    Deletion of users will need to be restricted to those with higher permissions, i.e. the 'sysadmin' users.

    In other words, how would I go about creating a 'User Management' database role?

    Is it even possible to do this?

    Thanks in advance.

    Tuesday, March 5, 2013 3:30 PM

Answers

  • Hello Avan,

    1) See Permissions of Fixed Server Roles (Database Engine) => server role "securityadmin"

    2) See database role db_accessadmin

    3) See database role "db_securityadmin", but this role can also modify the permissions of a role.


    Olaf Helper

    Blog Xing

    • Marked as answer by Avan_Madisen Thursday, March 7, 2013 8:04 AM
    Tuesday, March 5, 2013 3:47 PM
  • Try to stay away from the securityadmin fixed server role. From the SQL Server documentation Permissions of Fixed Server Roles (Database Engine) "The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role." Using the more granular permissions is a better idea.

    The ALTER ANY LOGIN permission enables the creating of logins and included enabling and disabling logins. However it also contains the permission to drop logins. The drop login permission cannot be separated through using permissions. The ALTER ANY LOGIN permission is the same permission possessed by the securityadmin role, but it is not able to be abused in the same way as securityadmin.

    The ALTER ANY USER permission in each database allows creating and dropping users.

    The ALTER ANY ROLE permission in each database permits changing the role membership. This can be restricted to a single role with ALTER ON ROLE::<name> if you wish.

    You can use the fixed database roles db_accessadmin and db_securityadmin for the user and role permissions if you wish, though they aren't quite targeted to what you want to do.

    For granular permissions, see the permissions poster at http://go.microsoft.com/fwlink/?LinkId=229142. For fixed role permissions, see Database Engine Fixed Server and Fixed Database Roles.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Avan_Madisen Thursday, March 7, 2013 8:04 AM
    Tuesday, March 5, 2013 4:24 PM

All replies

  • Hello Avan,

    1) See Permissions of Fixed Server Roles (Database Engine) => server role "securityadmin"

    2) See database role db_accessadmin

    3) See database role "db_securityadmin", but this role can also modify the permissions of a role.


    Olaf Helper

    Blog Xing

    • Marked as answer by Avan_Madisen Thursday, March 7, 2013 8:04 AM
    Tuesday, March 5, 2013 3:47 PM
  • Try to stay away from the securityadmin fixed server role. From the SQL Server documentation Permissions of Fixed Server Roles (Database Engine) "The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role." Using the more granular permissions is a better idea.

    The ALTER ANY LOGIN permission enables the creating of logins and included enabling and disabling logins. However it also contains the permission to drop logins. The drop login permission cannot be separated through using permissions. The ALTER ANY LOGIN permission is the same permission possessed by the securityadmin role, but it is not able to be abused in the same way as securityadmin.

    The ALTER ANY USER permission in each database allows creating and dropping users.

    The ALTER ANY ROLE permission in each database permits changing the role membership. This can be restricted to a single role with ALTER ON ROLE::<name> if you wish.

    You can use the fixed database roles db_accessadmin and db_securityadmin for the user and role permissions if you wish, though they aren't quite targeted to what you want to do.

    For granular permissions, see the permissions poster at http://go.microsoft.com/fwlink/?LinkId=229142. For fixed role permissions, see Database Engine Fixed Server and Fixed Database Roles.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Avan_Madisen Thursday, March 7, 2013 8:04 AM
    Tuesday, March 5, 2013 4:24 PM
  • One option, although it's a more complex one, is to write stored procedures that performs the precise tasks should be able to do. Then you sign these procedures with a certificate, and create a login or user (depending on whether you need server-level or database-level permission) from that certificate and grant that login/user the permissions required for the actions in the procedure.

    For a more detailed discussion on this technique, see this article on my wen site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 5, 2013 10:35 PM
  • Thanks for the information.

    I think I'll go with Rick's suggestion of granting 'ALTER ON ROLE::<name>' to the custom database role that will used for this.

    On the subject of stored procedures, I have built some to do the specific jobs of granting and revoking membership to certain custom database roles, however I could not get these to work without using 'EXEC sp_executesql' since the user name is supplied as a char() parameter (I am aware of the potential danger of building a SQL string at runtime with the ATLER ROLE command, I have taken measures to restrict what can be entered to avoid potential SQL injection). Would this override the permissions and allow the ALTER ROLE to execute for any user with permission to execure the procedure (I understand sp_executesql can do this in some cases), or would I still need to grant permissions to modify roles to those users?

    I've had a search and cannot find a source that confirms this.

    Something thing that concerns me, if a user has 'ALTER ON ROLE' permission on a role they are a member of, could that create the possibility of privilege escalation?
    • Edited by Avan_Madisen Wednesday, March 6, 2013 9:15 AM Added further question.
    Wednesday, March 6, 2013 9:05 AM
  • The technique to avoid SQL injection is to build the command with quotename:

    SELECT @sql = 'CREATE LOGIN ' + quotename(@name) +
                 ' WITH PASSWORD = ' + quotename(@password, '''')

    By default quotename wraps the string in square brackets, and double any right brackets in the string, but you can also supply a different delimiter, as demonstrated by the @password example.

    Using sp_executesql does not add any privileges. One thing that traps people is that permissions to table that you have through stored procedure due to ownership chaining, does not extend to sp_executesql. However, the effect of certificate signing I brought up does.

    ALTER permission on a role can lead to permission escalation, if you grant it to a user who is not member of that role. Which you of course should not do.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 6, 2013 9:35 AM
  • Thanks for the extra information on injection.

    At the moment I'm leaning towards avoiding using certificates if I can, as you said they can complex.

    I'm a little confused, my thought on privlilege escation was that if a user has permission to alter a role, could they then add additional permissions to that role beyond what was originally set out, essentially granting permission at any level. Am I wrong?

    I have also found another function 'GRANT' that can be used to grant users membership to roles, however reading through the documentation for it I cannot see if it is possible to grant permission to grant membership to a role without granting membership to that role itself. The only way to do that I can see if to grant 'ALTER' permission on the roles that the user manager role is allowed to grant. Is my understanding here wrong as well?

    Wednesday, March 6, 2013 9:58 AM
  • To add someone as a member to a role, you use the command ALTER ROLE ADD MEMMBER (SQL 2012) or sp_addrolemember (earlier versions).

    GRANT is the command you use to grant permission to a principle (i.e a user, login, role.) There are a number of entries for GRANT in Books Online, because you can grant different rights to diffrerent classes of objects.

    Just because you have ALTER permissions on a role, you cannot grant that role any right. You can only grant rights that you have yourself, and that you are entitled to grant.

    Here is a script that illustrates. It uses a loginless user and impersonates the user for a simple test:

    CREATE ROLE myrole
    CREATE USER nisse WITHOUT LOGIN
    GRANT ALTER ON ROLE::myrole TO nisse
    go
    EXECUTE AS USER = 'nisse'
    go
    -- Nisse can add himself to the role.
    ALTER ROLE myrole ADD MEMBER nisse
    go
    -- But he can't grant the role any rights.
    GRANT SELECT ON SCHEMA::dbo TO myrole
    go
    REVERT
    go
    -- Cleanup
    DROP USER nisse
    DROP ROLE myrole


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 6, 2013 10:38 PM
  • That makes sense, thanks for the explanation.
    Thursday, March 7, 2013 8:04 AM
  • I've been struggling with this same issue for over a day now. 

    I am writing a system administration application to allow user of our main application to create users.  The only way I've been able to the ALTER ROLE db_datawriter ADD MEMBER MyUser is if the login is a member of sysadmin or the user is a member of db_owner.  I've made the login a member of the server role securityadmin and the user a member of db_securityadmin and db_accessadmin with no success.  I've also granted all the ALTER ANY LOGIN TO TheUser, ALTER ANY USER TO TheUser and ALTER ANY ROLE TO TheUser, where TheUser is the login/user that is connected to the database and attempting to execute the ALTER ROLE db_datawriter ADD MEMBER MyUser statement.

    I'm sure there is something obvious that I'm missing, but I can see it.

    Thanks,

    Jamie Irwin

    Tuesday, June 3, 2014 6:42 PM
  • There appears to be a mismatch between the documentation and how it actually works. I tried this:

    CREATE USER appladmin WITHOUT LOGIN
    ALTER ROLE db_securityadmin ADD MEMBER appladmin
    GRANT ALTER ANY USER TO appladmin
    GRANT ALTER ON ROLE::db_datawriter TO appladmin

    Interesting enough the last command fails with Cannot find the object 'db_datawriter', because it does not exist or you do not have permission. And it does help to grant ALTER ANY ROLE either. As you say, you need to be db_owner, and I've submitted a doc bug on Connect:
    https://connect.microsoft.com/SQLServer/feedback/details/888121/incorrect-permissions-documented-for-alter-role

    (Maybe I should have submitted a bug for the function as such, but I would not really expect MS to change how it works.)

    For what you actually want to do, I have a solution: put the commands that you want the user to be able to exectute in a stored procedure and sign that procedure with a certificate and then create a user from that certificate and add that certificate user to db_owner. Then you can control exactly what the user behind the screen can do.

    For more details on the technique, see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 3, 2014 9:41 PM