User without a matching login can gain access

  • PHXHoward

    SSCrazy Eights

    Points: 8380

    Hi, I came across something that changed the way I see SQL Server permissions.

    Way back, we created a LOGIN in SQL Server for an AD group.  At the time, we gave the new LOGIN db_datareader permission to a database. As expected, all of the people in the AD group could SELECT from that database.

    Later, we dropped the LOGIN but did not drop the database USER that was associated with that LOGIN. We thought that because the database USER was mapped to a LOGIN that no longer existed, it would be a dead end for access.

    Some time later, we added a new AD group as a LOGIN and gave it permission to other databases.  There is a person in the new AD group who surprisingly gained db_datareader permission on the first database because he is a member of the AD group that is still a USER of the first database (with no matching login) even thought the new LOGIN does not grant access to that first database.

    It was a surprise and tells me that when we drop a login, we really do need to clean up the associated database users. It states as much in SSMS when dropping a login but I did not understand the correlation between database users and instance logins that don't have explicit permission to a database.

    From Management Studio: "Deleting server logins does not delete the database users associated with the logins. To complete the process, delete the users in each database." <--- this seems important.

  • Sreekanth B

    SSCertifiable

    Points: 6139

    Sorry, but is there a question?

  • Chris Harshman

    SSC-Forever

    Points: 41820

    PHXHoward wrote:

    Some time later, we added a new AD group as a LOGIN and gave it permission to other databases.  There is a person in the new AD group who surprisingly gained db_datareader permission on the first database because he is a member of the AD group that is still a USER of the first database (with no matching login) even thought the new LOGIN does not grant access to that first database.

    This makes sense to me.  LOGINs do not have database permissions, they allow authentication and connectivity to an instance.  USERs do have database permissions but not connectivity to an instance.  I believe your specific case is mentioned in the documentation:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15

     

  • PHXHoward

    SSCrazy Eights

    Points: 8380

    Because logins have user mappings, I though that a login had to be mapped to a database user for the person to get those database permissions. Apparently not... having a LOGIN gets the person in the front door and then if that person has permissions at a database level, the permissions will be effective regardless if their login is mapped to the database user.

    This would be a wonderful exam question.

    • This reply was modified 1 week, 1 day ago by  PHXHoward.
  • Jeffrey Williams 3188

    SSC Guru

    Points: 88150

    PHXHoward wrote:

    Because logins have user mappings, I though that a login had to be mapped to a database user for the person to get those database permissions. Apparently not... having a LOGIN gets the person in the front door and then if that person has permissions at a database level, the permissions will be effective regardless if their login is mapped to the database user.

    This would be a wonderful exam question.

    But - the login is mapped to a user in the database through the security group.  The issue is that the user in the database is a security group with active members so when SQL checks that security group membership - the user exists and is granted access.

    I think the confusion is the fact that the security group in the database no longer has a login mapped directly to that security group.  This doesn't actually matter because SQL Server is not authenticating the security group - it is authenticating the members in the security group.

    The user is able to login to SQL Server because that user is a member of security group A (the member is authenticated) - and gets access to the database because they are a member of security group B (again - the member is authenticated, not the group).

    This leads to the next concern - which is that permissions in a database are cumulative.  That is - if you have a member in multiple security groups and each security group has been granted different permissions  in a database - that member will have *all* permissions associated with both groups.  In fact, this is the exact scenario you ran into...

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • schleep

    SSChampion

    Points: 12538

    You can use xp_logininfo 'Domain\Username', 'all' to get some insight into how that user might be getting access to the first db.

    One of the items in the permission path column will be mapped to a user or group in the first db.

     

  • Mike Good

    SSCertifiable

    Points: 7373

    I appreciate you posting this.  I reckon the exhibited behavior does make sense, but would not have occurred to me.

  • PHXHoward

    SSCrazy Eights

    Points: 8380

    schleep, not exactly. I too expected that xp_logininfo would show that the user has read permission to the database but it does not.  It indicates only that the person gains access to the instance based on the AD security group where they are a member. The AD group does not give permission to the database where the person can SELECT.

    The only way I can find to confirm that they have SELECT access to the other database is through "execute as" which will show SELECT.

    USE <db_name>;

    execute as login = 'domain\account'

    SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

    REVERT;

  • schleep

    SSChampion

    Points: 12538

    Try this: (Not sure if this works in 2017, setuser was deprecated, but i'm still on 2008):

    setuser 'Domain\UserName';

    use <db_name>;

    setuser;

    If you don't get an error, they have access to the DB one way or another.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply