Database Access: The Principle of Least Privilege

Welcome to Data Education’s Experts Blog. Over the upcoming days, we will be featuring posts on SQL Server security by Adam Machanic, Microsoft MVP and founder of Data Education.

Of course, SQL Server security is an enormous subject area. In this and future posts, Machanic will focus on those aspects of security that are most important during the software design and development processes.


The key to locking down resources in any kind of system—database or otherwise—is quite simple in essence: Any given user should have access to only the bare minimum set of resources required, and for only as much time as access to those resources is needed. Unfortunately, in practice, this is more of an ideal goal than an actual prescription for data security; many systems do not allow privilege to be easily escalated dynamically, and Windows-based solutions have not historically been engineered to use escalation of privilege as a means by which to gain additional access at run time.

In many non-Windows operating systems that were originally designed for multiuser access, it has long been possible to impersonate other users when access to a resource owned by that user is required. It is important to note that impersonation is slightly different than reauthentication; instead of logging out and resending credentials, thereby stopping whatever is running, impersonation allows a process to temporarily escalate its privileges, taking on the rights held by the impersonated principal.

The most common example of this at an operating system level is UNIX’s su command, which allows a user to temporarily take on the identity of another user, easily reverting back when done. Windows systems can handle some degree of impersonation as well, but Microsoft has only recently provided APIs—such as the .NET WindowsIdentity class—that make doing so as convenient as in UNIX systems.

Permissions in Windows systems are typically provided using Access Control Lists (ACLs). Granting permission to a resource means adding a user to the list, after which the user can access the resource again and again, even after logging in and out of the system. This kind of access control provides no additional security if, for instance, an attacker takes over an account in the system. By taking control of an account, the attacker automatically has full access to every resource that the account has permission to access.

By controlling access with impersonation, the user is required to effectively request access to the resource dynamically, each time access is required. In addition, rights to the resource will only be maintained during the course of impersonation. Once the user reverts (i.e., turns off impersonation), the additional access rights are no longer granted. In effect, this means that if an account is compromised, the attacker will have to also compromise the impersonation context in order to gain access to more secure resources.

The idea of security through least privilege involves creating users with few or no permissions, and allowing them to briefly escalate their privileges when greater access is required. This is generally implemented using proxies—users (or other security principals) that have access to a resource but cannot be authenticated externally. Use of low-privileged external users complemented by higher-privileged proxy users provides a buffer against attack due to the fact that the only accounts that an attacker can directly compromise from the outside have no permissions. Accessing more valuable resources requires additional work on the part of the attacker, giving you that much more of a chance to detect problems before they occur.