Creating Proxies in SQL Server
In this post, Data Education founder and trainer Adam Machanic continues his discussion of security through the principle of least privilege. Here, he explains how to create proxy logins, which can used to delegate specific server-level permissions, among other advantages.
SQL Server 2005 allows creation of both server-level principals (logins) that cannot log in, and database-level principals (users) that are not associated with a login. It is only possible to switch into the execution context of one of these users or logins via impersonation, making them ideal for privilege escalation scenarios.
In order to create a proxy login (which can be used to delegate server-level permissions such as
BULK INSERT or
ALTER DATABASE), you must first create a certificate in the master database. Certificates are covered in depth in Chapter 5, but for now think of a certificate as a trusted way to verify the identity of a principal without a password. The following syntax can be used to create a certificate in master (note that before a certificate can be created in any database, a master key must be created. Again, see Chapter 5):
USE master GO CREATE CERTIFICATE Dinesh_Certificate ENCRYPTION BY PASSWORD = 'stR0n_G paSSWoRdS, pLE@sE!' WITH SUBJECT = 'Certificate for Dinesh' GO
Once the certificate has been created, a proxy login can be created using SQL Server 2005′s
CREATE LOGIN FROM CERTIFICATE syntax:
CREATE LOGIN Dinesh FROM CERTIFICATE Dinesh_Certificate
This login can be granted permissions, just like any other login. However, to use the permissions, it must be mapped to a database user. This is done by creating a user using the same certificate that was used to create the login, using the
CREATE USER FOR CERTIFICATE syntax. See the section “Stored Procedure Signing Using Certificates,” later in this chapter, for more information on how to use a proxy login for server-level permissions.
Another type of proxy principal that can be created is a database user not associated with a server login. This is done using
CREATE USER WITHOUT LOGIN:
CREATE USER Bob WITHOUT LOGIN
This user, like any database user, can be assigned ownership and other permissions. However, it is impossible to log into the server and authenticate as Bob. Instead, you must log in using a valid server-level login and authenticate to the database with whatever database user is associated with your login. Only then can you impersonate Bob, taking on whatever permissions the user is assigned. This is discussed in detail in the section “Basic Impersonation Using
EXECUTE AS” later in these posts.