The cross-database security for user impersonation in SQL Server 2008, and still in 2012, exposes some of the worst problems with its security design and reveals some major reasons why its dual-level user space is flawed.  I'll discuss these and other problems, and present a better alternative.

Database Users

First consider the Microsoft perspective on user and cross-database security in this example from their discussion of impersonation using EXECUTE AS:

"As the owner of Database 1, Bob can create a user for Fred in his database and because he has full permissions within [it], Bob can also impersonate user Fred.  However, because of the security restrictions imposed by SQL Server, Bob cannot access [a database Fred owns] under the impersonated context.  Without these default restrictions in place, Bob would be able to access Fred’s data without his knowledge." [1]

The example makes it sound like Bob is prevented from gaining access to Fred's database by creating a user "Fred" in his own database and impersonating it not because that impersonated user is different than the one in Fred's database, but rather only because of some other security mechanisms.

Well, indeed, if you navigate the necessary security mechanisms to bypass the default restrictions and allow cross-DB access in SQL Server, you do end up using the same user, effectively.  In order to allow access in a target database for a user in another (source) database, whether for impersonation or not, you first have to create a same-named user in the target database and then map it to the same server login as the one mapped to by the source user.  The two users are therefore essentially equivalent.  So one could say that what you're really doing is managing cross-scope access with server logins, not database users.

Further, Microsoft, instead of using permissions, has added other mechanisms (which I'll discuss in the next 2 sections) that aren't tied to the user to give finer-grained control over which scopes a user can access when impersonated .  Also, SQL Server could be designed such that permissions on all objects & statements are assigned to server-level logins instead of database users.  This is obviously true because the software has complete control over an entire server instance, and because no permission mechanism necessarily depends on what principal you assign permissions.  For example, SELECT permission on a table could be assigned to logins and not users--the table doesn't care.  Finally, the 1-to-1 user-to-login mapping required in SQL Server means that the DB user provides no real unique functionality.  Ignoring for the moment "contained" database users added in 2012, we see that the uselessness of DB users is therefore complete.  So why have them at all?

There should just be logins, period, with permissions used to control all access.  I'll show how this would be better later.  I'll first examine other problems, starting with authenticators, one of those other, finer-grained security mechanisms in SQL Server.

Authenticators

In SQL Server, an authenticator at the database level is the thing that verifies the identity of a user (to other databases).  It's usually the DB owner.  But why make a user the authenticator of other users?  We don't see this anywhere else in the software world.  And the server software is already doing the authentication job at initial connection by checking OS security tokens or passwords.  Why split the job up?

For a user to access resources in a target DB, the verification of his/her identity is achieved by trusting the authenticator of the user's DB.  This appears sensible, if one accepts its antecedents, and seems similar to other security architectures, wherein a user from one scope is considered valid in another scope if it's controller is trusted there.

But DB-level users and authenticators aren't how such trust should be achieved in SQL Server, because they aren't necessary, and because permissions, which are already extant and required, could handle it, and handle it better:

1. It's the server that's really responsible for authenticating users.  Databases aren't separate software and, in the name of consistency, shouldn't be made to appear as authenticators.  Microsoft has misapplied the concept of cross-scope trust.

2. It's at the server or application level that we think of authentication.  I think both users and DBAs are accustomed to the master of an environment doing the authenticating.  With the superfluousness of database users that I demonstrated above, the server instance is that environment (and its master).  For each software, there should be one login context (or less!).

3. Trusting "authenticators" for cross-DB access is really just checking the DB owner, which is essentially a database property.  But it's not necessarily unique across DBs.  Why not check a unique property, such as database name, instead?  And permissions still have to be checked anyway.  So why not make the whole access process regular, and maximally effective, by checking pemissions and the user origin?

Consider how authenticators and the AUTHENTICATE permission are used in SQL Server to understand how cumbersome and unneeded they are ...

This is horribly convoluted!  Just get rid of DB users altogether.  Create a server login that modules in database A can use to access database B, and use permissions to control access.  Simple.

Trustworthy Property

Microsoft recognized the non-uniqueness problem of DB-owner authenticators that I mentioned in point 3 in the above section.  To add to the security granularity in SQL Server and attempt to mitigate this, they added the capability to flag a database as "trustworthy".  This explicitly denotes which DBs an authenticator's users can come from when being considered for access in another DB.

But the problem with this, besides being inelegant, is that it isn't fine-grained enough.  There are scenarios, in which access is needed from multiple source DBs, all with the same owner, to some target DBs but not other target DBs, that can't be achieved.  This is illustrated in Figure 1.  If access is enabled from A to X and from B to Y, it can't be prevented from A to Y (or B to X), because the AUTHENTICATE/TRUSTWORTHY mechanism doesn't empower Y to differentiate between A and B.

Now, the scenario in Figure 1 is only unattainable if the impersonated user is the same from each source database.  However, this isn't an unlikely situation.  Impersonation is often used simply to shield end users from the accessed data objects.  An owner of multiple DBs may therefore want to use the same impersonated user account to ease the administrative burden of controlling access.  But this is impossible due to Microsoft's design (at least, without the mechanisms I mention in the next section).

Finer-Grained Trustworthiness

To get an even finer granularity of cross-database access control (i.e., user-level or module-level), and to mitigate the problem I just discussed above, Microsoft has added yet another separate mechanism: the ability to use certificates and keys as authenticators.  But this just adds to the already unnecessary complexity!  Rather than go into the details of it, though, I'll instead note a few other problems and then discuss a better approach.

Database Owners & Users Inconsistency

One minor problem with database users is their inconsistent usage.  If you want a server login to be able to access a database, you have to create a user in that DB and map it to the login.  But if you want a server login to own a DB, you don't create a user for it in that DB.  You instead must specifically make sure it isn't mapped to a DB user, and then give it ownership.

UI Problem

A related minor problem is the weird visbility of an owner's databases in the GUI.  The observation above would make it seem like SQL Server treats database owners like server-level entities that can own objects contained in the server, such as DBs.  But if you look at the properties of a server login that's the owner of a DB, that database is not in the list of owned securables.  The only way to see, using the GUI, what DBs a particular login owns is to check the property page of each database individually!

SQL Server 2012: "Contained" Database Users

One final minor problem.  In version 2012, Microsoft has added the capability for databases to be partially or fully "contained", meaning self-contained.  This includes database users, so that people can authenticate directly to a database instead of to the server.  Microsoft claims that this is a benefit because it prohibits database users from performing server-level operations. [2]  But, once again, permissions (and a flat user space) could achieve the same thing, and more simply.

They also state that "in case of failover, people would be able to connect...without creating logins...on...the secondary", eliminating the work of copying logins across server instances. [3]  Well, Microsoft has supplied the sp_help_revlogin stored procedure since at least the early 2000s to handle this work, and in recent versions has a "Transfer Logins" task in SSIS that copies SIDs.  But these tools do have caveats or problems for unusual login-copy scenarios.  Microsoft acknowledges this in KB918992, and in the 2012 Books Online, noting that the process "can be difficult and time-consuming". [3]  But why add complexity with "contained" users and sacrifice a simple and functional security architecture for the sake (at least partly) of avoiding the problems with these tools?  Why not just fix or improve the tools instead?

There are additional problems with "contained" features and users in the complications they cause.  Avoiding the collision between these users and server logins becomes a hassle.  There's now more work to do around database-level users because of the caveats and conditions of using "contained" databases (see the 2 references about them).  And if you need to duplicate "contained" users across databases, what will you be doing?  According to the documentation, you'll be looking up SIDs and typing queries, just like you do if you have problems copying logins across servers! [4]

Instead of leveraging a focus on simplicity, Microsoft has instead catered to their existing mistake of the database user by adding yet another level of garish complication.  Apparently, the KISS principle is lost on them.

Proposal: Flat Is Better

A "flat", single-level user space can get all of the access control done that's done now*, and do it better, while being simpler.  Here are some points that explain why:

1. Make the software do the work--that's what it's there for!  Instead of forcing DBAs to manage a dual-level user space and authentication (plus a whole new set of direct-to-database users as of 2012), implement a flat user space and use permissions to manage access.  It's more intuitive, and compared to what SQL Server has now, I believe, more efficient.

2. Concerned about the need for segregating database access?  Don't be.  Remember, users in a flat user space can still have their accounts sent to a default database as soon as they log in to the server.  And the security architecture can be such that users have a "source-DB" property that's checked during permissions validation for every object access and command.  This could be part of the token information that's already maintained by SQL Server.

3. Eliminate the kludge of the AUTHENTICATE permission and TRUSTWORTHY database property (not to mention certificates and keys as authenticators), gaining the ability to handle any mix of source and destination cross-DB access, and getting finer granularity all around, all while simplifying!  In the target of a cross-DB access, just check the CONNECT permission and test the source-DB property mentioned above.  Done.  Any combination you want, handled.  All achieved with a minimal set of maximally-applicable building-blocks.

4. Concerned about users in a flat user space having visibility of other users in other databases?  (This is of utmost importance in a hosted database environment, for example.)  Don't be.  Just include a LIST USERS permission in the DBMS design.  This can be added to and administered on databases, user groups, the whole server, or whatever's necessary.  And it can use the source-DB property of access requests to further restrict user visibility.

5. Leverage groups (roles) to ease permission management instead of fiddling with the dual-level user complexity.  Because DB users must be mapped to 1, and only 1, server login in the existing SQL Server design anyway (except for the new "contained" DB user), why have them?  Manage permissions with user groups when necessary, which we already do somewhat anyway, in a universal rather than half-baked fashion.

6. Microsoft has cited the "difficult and time-consuming" nature of managing logins across servers as a reason to use contained database users in 2012.  But most accounts of such management find it relatively painless, except for special circumstances (such as cross-version login copying).  So either the tools to do this are adequate, or they need to be fixed.  Whichever the case, with a robust login management tool, there would be no excuse, in the form of difficult cross-server login work, for not utilizing a single-level, authenticate-to-server, userspace.

7. A flat userspace would simplify the DBA comprehension load, thereby reducing the overhead of DBA competence and training, making the personnel TCO component lower.

* Except for the direct-to-database access of contained users, which I believe may be more trouble than their worth.

Security Model Summary

The security model for a flat user space could be the same as it is now, with just a few changes:

1. Eliminate database users--have only server logins.
2. Make the source-database context of the user issuing a command available for testing in a permission check (this information is already maintained in SQL Server, more or less, with the user token).
3. Create a LIST USERS command & permission to provide control of what users are visible to whom.

The syntax for permission-setting statements could follow the current general form, with the addition of a source-context option:

GRANT|DENY {permission|command} ON object TO principal [FROM source-context]

The SQL Server user interface could easily be adapted to handle this additional property, as well as the other changes to the security model.  And the elimination of users and streamlining of security should more than offset the engine load of checking source contexts.

Security Methods

1. Restricting user administration to specific DBs:

• DBAs can be restricted to administering only users who have CONNECT permission to DBs they control.
• DBAs can be restricted to managing permissions on DB objects only for users that have CONNECT permission for DBs they control.

2. Restricting user visibility to specific DBs:

• DBAs can create users if that's desired in the DBMS, but they would be assigned as their default, and granted CONNECT to, only a DB that the DBA controls.
• A "LIST USERS" permission on DBs, but not the server, can restrict DBAs (and users if needed) to list only users who have CONNECT permission for their DBs.

3. Restricting cross-DB user visibility & access:

• To control which out-of-DB users a target DBA can see in order to assign permissions:
  • A server admin can give users that need to be visible CONNECT permission to a target DB, or
  • The target DBA can be given LIST USERS permission on the whole server, or on specific source DB(s), or
  • The server admin can create 1+ user groups (roles) in which s/he or source DBAs can place users, and give the target DBA permission to LIST USERS on those group(s).

• To restrict access based on the "location" of a user, permission checks for activities can test the user's source-context property.

Conclusion

It seems pretty clear that Microsoft started with the security architecture in SQL Server 2000 and just continually tacked on mechanisms to cobble together new functionality, instead of taking just a little time to actually think about what they were doing and execute a good design.  (And this approach seems not without precedent.)

Just out of curiosity, I checked how security works in MySQL and found that, like my recommendation here, there is only one level of security principal in it, and access is controlled by permission settings.  To my surprise, it also contains a feature similar to my recommendation for the source-context permission check--MySQL allows distinct permission sets for a security principal depending on what host that principal logs in from!  PostgreSQL is similar.

I believe I've demonstrated that, compared to the overly-complicated dual-level user space, its attendant kludgey, convoluted and lacking AUTHENTICATE and TRUSTWORTHY features, and the misguided further reliance on the database user in version 2012, a flat user space such as the one I've outlined would be more intuitive, simpler and consistent, easier to manage, more likely to be secure, and therefore better.

References

1. "Extending Database Impersonation by Using EXECUTE AS", Books Online, SQL Server 2008 R2.
2. Bachrach, Ann, et al, "What's New In SQL Server 2012", Technical White Paper.  Microsoft, 2012.
3. "Contained Databases", Books Online, SQL Server 2012.
4. "Security Best Practices with Contained Databases", Books Online, SQL Server 2012.

© 2013 Thomas Knight