Logo
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
EPL Standings
 
 
Windows Server

SQL Server 2005 : Privilege Escalation Without Ownership Chains

7/24/2011 4:54:24 PM
Ownership chaining will not work if the object owner does not match the module owner, or if dynamic SQL is used. In these cases, you'll have to use one of the two other kinds of privilege escalation provided by SQL Server: an extension to stored procedures using the EXECUTE AS clause, or module signing using certificates.

Using the EXECUTE AS clause with stored procedures is an easy and effective method of escalating permissions, but is not nearly as flexible as what can be done using certificates. With certificates, permissions are additive rather than impersonated—the additional permissions provided by the certificate add to, rather than replace, the permissions of the calling principal.

Stored Procedures and EXECUTE AS

The EXECUTE AS command can be used on its own in T-SQL batches in order to temporarily impersonate other users. However, EXECUTE AS is also available for stored procedures, functions, and triggers. The examples in this section only focus on stored procedures, but the same principles apply to the other object types.

To use EXECUTE AS to change the impersonation context of an entire stored procedure, add it to the CREATE PROCEDURE statement as in the following example:

CREATE PROCEDURE SelectSensitiveData
WITH EXECUTE AS 'Louis'
AS
BEGIN
SET NOCOUNT ON

SELECT *
FROM dbo.SensitiveData
END

When this stored procedure is executed by a user, all operations within the procedure will be evaluated as if they are being run by the Louis user rather than by the calling user (as is the default behavior). This includes any dynamic SQL operations, or manipulation of data in tables that the Louis user has access to. When the stored procedure has completed execution, context will be automatically reverted back to that of the caller.

Keep in mind that use of EXECUTE AS does not break ownership chains, but rather can be used to add to them and create additional flexibility. For instance, consider the following two users and associated tables:

CREATE USER Kevin
WITHOUT LOGIN
GO

CREATE TABLE KevinsData
(
SomeData INT
)
GO

ALTER AUTHORIZATION ON KevinsData TO Kevin
GO

CREATE USER Hilary
WITHOUT LOGIN
GO

CREATE TABLE HilarysData
(
SomeOtherData INT
)
GO

ALTER AUTHORIZATION ON HilarysData TO Hilary
GO

Both users, Kevin and Hilary, own tables. A stored procedure might need to be created that accesses both tables, but using ownership chaining will not work; if the procedure is owned by Kevin, that user would need to be given access to HilarysData in order to select from that table. Likewise for Hilary and the KevinsData table.

One solution in this case is to combine EXECUTE AS with ownership chaining and create a stored procedure that is owned by one of the users, but executes under the context of the other. The following stored procedure shows how this might look:

CREATE PROCEDURE SelectKevinAndHilarysData
WITH EXECUTE AS 'Kevin'
AS
BEGIN
SET NOCOUNT ON

SELECT *
FROM KevinsData

UNION ALL

SELECT *
FROM HilarysData
END
GO

ALTER AUTHORIZATION ON SelectKevinAndHilarysData TO Hilary
GO

Because Hilary owns the stored procedure, ownership chaining will kick in and allow selection of rows from the HilarysData table. But because the stored procedure is executing under the context of the Kevin user, permissions will also cascade for the KevinsData table. In this way, both permission sets can be used, combined within a single module.

Unfortunately, this is about the limit of what can be done using EXECUTE AS. For more complex permissions scenarios, it is necessary to resort to signing stored procedures using certificates.

Stored Procedure Signing Using Certificates

Proxy logins and users can be created based on certificates. Creating a certificate-based proxy is by far the most flexible way of applying permissions using a stored procedure, as the permissions are additive. One or more certificates can be used to sign a stored procedure, and each certificate will apply its permissions to the others already present, rather than replacing the permissions as happens when impersonation is done using EXECUTE AS.

To create a proxy user using a certificate, first create the certificate, and then create the user using the FOR CERTIFICATE syntax:

CREATE CERTIFICATE Greg_Certificate
WITH SUBJECT='Certificate for Greg'
GO

CREATE USER Greg
FOR CERTIFICATE Greg_Certificate
GO

Once the proxy user is created, it can be granted permissions to any resource in the database, just like any other database user. But a side effect of having created the user based on a certificate is that the certificate itself can also be used to propagate permissions granted to the user. This is where stored procedure signing comes into play.

To illustrate this, the following table can be created, and access granted to the Greg user:

CREATE TABLE GregsData
(
DataColumn INT
)
GO

GRANT ALL ON GregsData
TO Greg
GO

A stored procedure can then be created that selects from the table, but for the sake of this example, the stored procedure will be owned by a user called Steve, in order to break any possible ownership chain that might result from creating both the table and the stored procedure in the same default schema:

CREATE PROCEDURE SelectGregsData
AS
BEGIN
SET NOCOUNT ON

SELECT *
FROM GregsData
END
GO

CREATE USER Steve
WITHOUT LOGIN
GO

ALTER AUTHORIZATION ON SelectGregsData TO Steve
GO

Even if granted permission to execute this stored procedure, a third user will be unable to successfully do so, as the stored procedure does not propagate permissions to the GregsData table:

CREATE USER Linchi
WITHOUT LOGIN
GO

GRANT EXECUTE ON SelectGregsData TO Linchi
GO

EXECUTE AS USER='Linchi'
GO

--This will fail -- SELECT permission denied
EXEC SelectGregsData
GO

In order to make the stored procedure work for the Linchi user, permissions to the GregsData table must be propagated through the stored procedure. This can be done by signing the procedure using the same certificate that was used to create the Greg user. Signing a stored procedure is done using the ADD SIGNATURE command:

ADD SIGNATURE TO SelectGregsData
BY CERTIFICATE Greg_Certificate

Once the procedure is signed with the certificate, the procedure has the same permissions that the Greg user has; in this case, that means that any user with permission to execute the procedure will be able to select rows from the GregsData table when running the stored procedure.

The flexibility of certificate signing becomes apparent when you consider that you can sign a given stored procedure with any number of certificates, each of which can be associated with different users and therefore different permission sets. This means that even in an incredibly complex system with numerous security roles, it will still be possible to write stored procedures to aggregate data across security boundaries.

Keep in mind when working with certificates that any time the stored procedure is altered, all signatures will be automatically revoked by SQL Server. Therefore, it is important to keep signatures scripted with stored procedures, such that when the procedure is modified, the permissions can be easily kept in sync.

It is also important to know how to find out which certificates, and therefore which users, are associated with a given stored procedure. SQL Server's catalog views can be queried to find this information, but getting the right query is not especially obvious. The following query, which returns all stored procedures, the certificates they are signed with, and the users associated with the certificates, can be used as a starting point:

SELECT
OBJECT_NAME(cp.major_id) AS signed_module,
c.name AS certificate_name,
dp.name AS user_name
FROM sys.crypt_properties AS cp
INNER JOIN sys.certificates AS c ON c.thumbprint = cp.thumbprint
INNER JOIN sys.database_principals dp ON SUBSTRING(dp.sid, 13, 32) = c.thumbprint


This query is somewhat difficult to understand, so it is worth explaining here. The sys.crypt_properties view contains information about which modules have been signed by certificates. Each certificate has a 32-byte cryptographic hash, its thumbprint, which is used to find out which certificate was used to sign the module, via the sys.certificates view. Finally, each database principal has a security identifier, the final 32 bytes of which is the thumbprint if the principal was created from a certificate.

Assigning Server-Level Permissions

The previous example showed only how to assign database-level permissions using a certificate. Signing a stored procedure can also be used to propagate server-level permissions, such as BULK INSERT or ALTER DATABASE. Doing so requires creation of a proxy login from a certificate, followed by creation of a database user using the same certificate. To accomplish this, the certificate must be backed up after being created, and restored in the database in which you are creating the user. Once the database user is created, the procedure to apply permissions is the same as when propagating database-level permissions.

To begin with, create a certificate in the master database. Unlike previous examples, this certificate must include a password in its definition, in order to encrypt its private key. Once the certificate has been created, use it to create a proxy login:

CREATE CERTIFICATE alter_db_certificate
ENCRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
WITH SUBJECT = 'ALTER DATABASE permission'
GO

CREATE LOGIN alter_db_login FROM CERTIFICATE alter_db_certificate
GO

This login, in case you can't tell from the name, will be used to propagate ALTER DATABASE permissions. The next step is to grant the appropriate permissions to the login:

GRANT ALTER ANY DATABASE TO alter_db_login

At this point, the next step required is to back up the certificate to a file. The certificate can then be restored from the file into the database of your choosing, and from there can be used to create a database user that will have the same permissions as the server login, by virtue of having been created using the same certificate.

BACKUP CERTIFICATE alter_db_certificate
TO FILE = 'C:\alter_db.cer'
WITH PRIVATE KEY
(
FILE = 'C:\alter_db.pvk',
ENCRYPTION BY PASSWORD = 'an0tHeR$tRoNGpaSSWoRd?',
DECRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
)

Once backed up, the certificate can be restored in a database. For the purpose of this example, a new database can be created and used to keep things simple:

CREATE DATABASE alter_db_example
GO

USE alter_db_example
GO

CREATE CERTIFICATE alter_db_certificate
FROM FILE = 'C:\alter_db.cer'
WITH PRIVATE KEY
(
FILE = 'C:\alter_db.pvk',
DECRYPTION BY PASSWORD = 'an0tHeR$tRoNGpaSSWoRd?',
ENCRYPTION BY PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
)
GO

It is worth noting that at this point, the certificate's physical file should probably be either deleted or backed up to a safe storage repository. Although the private key is encrypted with the password, it would certainly be possible for a dedicated attacker to crack it via brute force. And since the certificate is being used to grant ALTER DATABASE permissions, such an attack could potentially end in some damage being done—so play it safe with these files.

After the certificate has been created in the database, the rest of the process is just as before. Create a stored procedure that requires the privilege escalation, create a user based on the certificate, and sign the stored procedure with the certificate:

CREATE PROCEDURE SetMultiUser
AS
BEGIN
ALTER DATABASE alter_db_example
SET MULTI_USER
END
GO

CREATE USER alter_db_user
FOR CERTIFICATE alter_db_certificate
GO

ADD SIGNATURE TO SetMultiUser
BY CERTIFICATE alter_db_certificate
WITH PASSWORD = 'stR()Ng_PaSSWoRDs are?BeST!'
GO

The permissions can now be tested. In order for propagation of server-level permissions to work, the user executing the stored procedure must be associated with a valid server login, and the login must be impersonated rather than the user. So this time, CREATE USER WITHOUT LOGIN will not suffice:

CREATE LOGIN test_alter WITH PASSWORD = 'iWanT2ALTER!!'
GO

CREATE USER test_alter FOR LOGIN test_alter
GO

GRANT EXECUTE ON SetMultiUser TO test_alter
GO

Finally, the test_alter login can be impersonated, and the stored procedure executed:

EXECUTE AS LOGIN='test_alter'
GO

EXEC SetMultiUser
GO

This example was obviously quite simplistic, but it should serve as a basic template that you can adapt as necessary when you need to provide escalation of server-level privilege to database users.

Other -----------------
- SQL Server 2005 : Privilege and Authorization - Ownership Chaining
- SQL Server 2005 : Privilege and Authorization - Basic Impersonation Using EXECUTE AS
- Configuring Standard Permissions for Exchange Server 2010 (part 2) - Understanding & Assigning Advanced Exchange Server Permissions
- Configuring Standard Permissions for Exchange Server 2010 (part 1)
- Feature Overview of Microsoft Lync Server 2010 : Dial-In Conferencing & Enterprise Voice
- Feature Overview of Microsoft Lync Server 2010 : Instant Messaging & Web Conferencing
- Feature Overview of Microsoft Lync Server 2010 : Presence
- Installing Windows Small Business Server 2011
- Business Server 2011 : Planning Fault Tolerance and Avoidance - Disk Arrays
- Microsoft Dynamics GP 2010 : Improving financial reporting clarity by splitting purchasing accounts & Speeding up lookups with Advanced Lookups
- Microsoft Dynamics GP 2010 : Remembering processes with an Ad hoc workflow
- Microsoft Dynamics GP 2010 : Gaining additional reporting control with Account Rollups
- SharePoint 2010 Search : Replacing the SharePoint Search Engine (part 2) - FAST Search Server 2010 for SharePoint
- SharePoint 2010 Search : Replacing the SharePoint Search Engine (part 1) - Google Search Appliance
- Microsoft Dynamics NAV : Backing up and restoring with SQL Server
- Microsoft Dynamics NAV : Using HotCopy backup & Testing the database
- Microsoft Dynamics NAV : Creating and restoring backups using a Dynamics NAV client
- Microsoft SQL Server 2008 Analysis Services : Building Basic Dimensions and Cubes - Setting up a new Analysis Services project
- Windows Server 2008 Server Core : Managing IIS - Working with the ApplicationHost.CONFIG File
- Microsoft Dynamics CRM 2011 : Creating a Dynamic Marketing List
 
 
Most view of day
- Microsoft Systems Management Server 2003 : Standard and Advanced Security
- Windows Phone 7 : Running XNA Projects in Windows (part 1) - Porting Projects to Windows
- Adobe Illustrator CS5 : Understanding Appearances (part 2) - Targeting Attributes, Applying Multiple Attributes
- Troubleshooting Hardware and Performance : First Aid for Troubleshooting Hardware
- SharePoint 2010 : Configuring Search Settings and the User Interface - Search Scopes
- Microsoft Content Management Server Development : A Placeholder Control to Store All HTML Tags (part 1)
- System Center Configuration Manager 2007 : Operating System Deployment - Post Deployment Tasks, Troubleshooting
- Managing SharePoint 2010 with Windows PowerShell : Managing Permissions in SharePoint 2010, Managing Content Databases in SharePoint 2010
- Windows 7 Mobility Features : Working with the Windows 7 User Interface
- Microsoft Lync Server 2010 : Planning for Voice Deployment - Media Bypass
Top 10
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 3) - Creating IPv4 DHCP Scopes
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 2) - Installing DHCP Server and Server Tools
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 1)
- Windows Server 2012 : DHCP,IPv6 and IPAM - Understanding the Components of an Enterprise Network
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 3) - Translating Text with the Mini Translator
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 2) - Translating a Word or Phrase with the Research Pane
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 1) - Setting Options for the Research Task Pane, Searching with the Research Task Pane
- Microsoft OneNote 2010 : Doing Research with Linked Notes (part 2) - Ending a Linked Notes Session, Viewing Linked Notes
- Microsoft OneNote 2010 : Doing Research with Linked Notes (part 1) - Beginning a Linked Notes Session
- Microsoft OneNote 2010 : Doing Research with Side Notes (part 3) - Moving Side Notes to Your Existing Notes
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro