none
Conflicting SQL Server Permissions RRS feed

  • Question

  • Hi.

    I have just discovered a potential security problem with a database I have inherited that is used by a third party software product that is quite old and for which the software is no longer supported.  The software allows users access to the database using a Windows Group, which has db_owner permissions.

    As I didn't want the database updated directly through SQL Server Management Studio, some time ago I created a new user login for each of the support users, and assigned them db_datareader and db_denydatawriter permissions. I tested it at the time and it seemed to work.  The reason for this, is that if a database table was updated or changed it could break the software, and as we don't have support, it wasn't worth taking the risk!!

    However, our newest support person was having permissions problem when using the software, that said UPDATE permissions were denied.  Another user who has exactly the same user permissions did not have the problem using the software.

    As the users have the same deny permissions on the database, I was surprised that this was happening, so decided to look at the windows groups that had permissions to the database. That's when I found out that a particular group (which happened to include the support staff) had full db owner permissions.

    My question is, is there a way to give a user read only permissions to a database when connecting through SSMS, whilst keeping the database owner permissions for the group so that the software still functions as it should??

    Hope someone out there can help.

    Chris

    Friday, August 7, 2015 3:22 PM

Answers

  • Not directly. One potential ingredient can be "application roles", where the user doesn't have update permissions, but the approle does. However the app need to call sp_setapprole to "enter" the application role, and since a password is supplied to enter the app role, this is only really secure when you have an app server in between (since otherwise the pwd need to be on the end-users machine and even if encryted then the key need to be accessible by the end-user).

    I believe that Erland somewhat touches on this in his great article. It is a bit to read, but well worth the time: http://sommarskog.se/grantperm.html


    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, August 7, 2015 7:44 PM
  • Tibor mentions application roles, but since this requires application changes, I guess this is not an option for you anyway.

    There is one alternative, but it is likely to be too complicated to be worth it. With this solution, you put the application on a Terminal Server or Citrix server. Users need to use different logins when they use the application and then they use SSMS.

    The SQL Server machine needs to have two network cards. The network is configured so that one of the NIC is only reachable from the Citrix machines. Only the application logins has CONNECT permission on this endpoint. The SSMS users only has permission to the other endpoint. Rather than messing with the network configuration, you can use a LOGON trigger that throws out users who come from the wrong IP address on the db_owner endpoint.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, August 7, 2015 9:09 PM

All replies

  • Not directly. One potential ingredient can be "application roles", where the user doesn't have update permissions, but the approle does. However the app need to call sp_setapprole to "enter" the application role, and since a password is supplied to enter the app role, this is only really secure when you have an app server in between (since otherwise the pwd need to be on the end-users machine and even if encryted then the key need to be accessible by the end-user).

    I believe that Erland somewhat touches on this in his great article. It is a bit to read, but well worth the time: http://sommarskog.se/grantperm.html


    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, August 7, 2015 7:44 PM
  • Thanks Tibor :)
    Friday, August 7, 2015 7:46 PM
  • Tibor mentions application roles, but since this requires application changes, I guess this is not an option for you anyway.

    There is one alternative, but it is likely to be too complicated to be worth it. With this solution, you put the application on a Terminal Server or Citrix server. Users need to use different logins when they use the application and then they use SSMS.

    The SQL Server machine needs to have two network cards. The network is configured so that one of the NIC is only reachable from the Citrix machines. Only the application logins has CONNECT permission on this endpoint. The SSMS users only has permission to the other endpoint. Rather than messing with the network configuration, you can use a LOGON trigger that throws out users who come from the wrong IP address on the db_owner endpoint.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, August 7, 2015 9:09 PM
  • I would validate the permissions for both users. I generally use this script. It works great but I don't recall where I saved it from:

    SELECT  
        [UserName] = CASE princ.[type] 
                        WHEN 'S' THEN princ.[name]
                        WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                     END,
        [UserType] = CASE princ.[type]
                        WHEN 'S' THEN 'SQL User'
                        WHEN 'U' THEN 'Windows User'
                     END,  
        [DatabaseUserName] = princ.[name],       
        [Role] = null,      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],       
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --database user
        sys.database_principals princ  
    LEFT JOIN
        --Login accounts
        sys.login_token ulogin on princ.[sid] = ulogin.[sid]
    LEFT JOIN        
        --Permissions
        sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
    LEFT JOIN
        --Table columns
        sys.columns col ON col.[object_id] = perm.major_id 
                        AND col.[column_id] = perm.[minor_id]
    LEFT JOIN
        sys.objects obj ON perm.[major_id] = obj.[object_id]
    WHERE 
        princ.[type] in ('S','U')
    UNION
    --List all access provisioned to a sql user or windows user/group through a database or application role
    SELECT  
        [UserName] = CASE memberprinc.[type] 
                        WHEN 'S' THEN memberprinc.[name]
                        WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                     END,
        [UserType] = CASE memberprinc.[type]
                        WHEN 'S' THEN 'SQL User'
                        WHEN 'U' THEN 'Windows User'
                     END, 
        [DatabaseUserName] = memberprinc.[name],   
        [Role] = roleprinc.[name],      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],   
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --Role/member associations
        sys.database_role_members members
    JOIN
        --Roles
        sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
    JOIN
        --Role members (database users)
        sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
    LEFT JOIN
        --Login accounts
        sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
    LEFT JOIN        
        --Permissions
        sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN
        --Table columns
        sys.columns col on col.[object_id] = perm.major_id 
                        AND col.[column_id] = perm.[minor_id]
    LEFT JOIN
        sys.objects obj ON perm.[major_id] = obj.[object_id]
    UNION
    --List all access provisioned to the public role, which everyone gets by default
    SELECT  
        [UserName] = '{All Users}',
        [UserType] = '{All Users}', 
        [DatabaseUserName] = '{All Users}',       
        [Role] = roleprinc.[name],      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],  
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --Roles
        sys.database_principals roleprinc
    LEFT JOIN        
        --Role permissions
        sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN
        --Table columns
        sys.columns col on col.[object_id] = perm.major_id 
                        AND col.[column_id] = perm.[minor_id]                   
    JOIN 
        --All objects   
        sys.objects obj ON obj.[object_id] = perm.[major_id]
    WHERE
        --Only roles
        roleprinc.[type] = 'R' AND
        --Only public role
        roleprinc.[name] = 'public' AND
        --Only objects of ours, not the MS objects
        obj.is_ms_shipped = 0
    ORDER BY
        princ.[Name],
        OBJECT_NAME(perm.major_id),
        col.[name],
        perm.[permission_name],
        perm.[state_desc],
        obj.type_desc--perm.[class_desc] 


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Friday, August 7, 2015 9:18 PM