Home » Blog » The TRUSTWORTHY Database Property Explained – Part 1

The TRUSTWORTHY Database Property Explained – Part 1

2013-02-19 - General, Security, Trustworthy

Introduction

There are a few things in SQL Server that require you to, or that at least are a lot simpler to make work if you turn the TRUSTWORTHY property of the database you are working with to on. But when you investigate a little further, you will find many articles and posts that warn you that evil will come upon you if you were to indeed turn this setting on. Yet, very few of these sources even attempt to explain what exactly the problem with the TRUSTWORTHY database property is.

With this post and a few more to follow, I am going to attempt to shed some light into the mystery of the TRUSTWORTHY property.

The TRUSTWORTHY Database property

You can see the current TRUSTWORTHY setting for a database by running this query:

[sql] SELECT is_trustworthy_on FROM sys.databases WHERE name = 'DatabaseName';
[/sql]

To change the property you can use the ALTER DATABASE statement:

[sql] ALTER DATABASE DatabaseName SET TRUSTWORTHY ON;
[/sql]

You need to be a member of the sysadmin server role to be able to execute this ALTER DATABASE statement.

In Books Online we can read the following about the TRUSTWORTHY database property:
"The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it."

This is not very helpful for understanding what is going on. However, there are two areas of concern mentioned:

Malicious Assemblies
Malicious SQL Modules that were created with a WITH EXECUTE AS clause

In this article I am going to look at how the TRUSTWORTHY database property changes the permission set of a stored procedure that is defined with a WITH EXECUTE AS clause.

Switching TRUSTWORTHY from Off to On

First we need to create a playground. The following script creates the database TrustDb and adds a few procedures and functions to it that we are going to use later. Careful: Any existing database with this name is dropped first!

[sql] 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 'TrustDb';
GO
IF EXISTS(SELECT 1 FROM sys.server_principals WHERE name = 'TrustDbOwner') DROP LOGIN TrustDbOwner;
GO
CREATE LOGIN TrustDbOwner WITH PASSWORD='**************';
ALTER SERVER ROLE sysadmin ADD MEMBER TrustDbOwner;
GO
EXECUTE AS LOGIN = 'TrustDbOwner';
GO
CREATE DATABASE TrustDb;
GO
USE TrustDb;
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
EXEC hlp.DatabaseProperties;
GO
USE tempdb;
GO
REVERT;
GO
[/sql]

The EXEC hlp.DatabaseProperties; at the end lets us know that the database is owned by the login TrustDbOwner and that the TRUSTWORTHY database property is set to off:

Now let's create two logins and users for them in this database:

[sql] USE TrustDb;
GO
CREATE LOGIN ChainPrincipal1 WITH PASSWORD ='**************';
CREATE USER ChainDbPrincipal1 FROM LOGIN ChainPrincipal1;
GO
CREATE LOGIN ChainPrincipal2 WITH PASSWORD ='**************';
CREATE USER ChainDbPrincipal2 FROM LOGIN ChainPrincipal2;
GO
GRANT CREATE SCHEMA TO ChainDbPrincipal2;
GRANT CREATE PROCEDURE TO ChainDbPrincipal2;
[/sql]

This also grants the permission to create schemata and procedures in this database to the ChainDbPrincipal2 user.

Next we are going to use those freshly granted permissions to have ChainPrincipal2 create a schema and a procedure:

[sql] EXECUTE AS LOGIN='ChainPrincipal2';
GO
CREATE SCHEMA Chain;
GO
GRANT EXECUTE ON SCHEMA::Chain TO ChainDbPrincipal1;
GO
CREATE PROCEDURE Chain.Test
WITH EXECUTE AS SELF
AS
BEGIN
SELECT * FROM hlp.CurrentTokens();
END;
GO
EXEC hlp.ObjectProperties 'Chain.Test';
GO
REVERT;
GO
[/sql]

This creates the procedure Chain.Test and grants execute permission on it to ChainDbPrincipal1, the other user we created previously. The procedure Chain.Test is created WITH EXECUTE AS SELF. That means that every time the procedure is executed, the permissions of the person that created the procedure are in effect. The hlp.ObjectProperties shows that this is indeed the case:

When you create a procedure it by default does not have an owner itself. That is indicated by the NULL value in the object_owner column. That means that ownership is transferred to the owner of the schema this object belongs to. The execute_as_user column tells us that the security context inside the procedure – during its execution – is determined by the permissions of the ChainDbPrincipal2 user.

The procedure itself selects form the hlp.CurrentTokens() functions. This function gets information form the sys.user_token and sys.login_token DMVs. It also uses the USER_NAME() and SUSER_NAME() functions to display the user and login names of the current user.

sys.user_token contains one row for every database principal (user or role) that needs to be considered when checking permissions. For example, if a user user1 is member of the database role role1 this DMV would return one row for the user itself and one row for the role. sys.login_token does the same but for logins and server roles.

Before we continue, let's make sure that the TRUSTWORTHY database property is set to off:

[sql] ALTER DATABASE TrustDb SET TRUSTWORTHY OFF;
EXEC hlp.DatabaseProperties;
[/sql]

Now we can run the first test:

[sql] EXECUTE AS LOGIN='ChainPrincipal1';
GO
SELECT * FROM hlp.CurrentTokens();
EXEC Chain.Test;
GO
REVERT;
[/sql]

The EXECUTE AS LOGIN='ChainPrincipal1'; causes SQL Server to behave as if ChainPrincipal1 was used to logon to this connection. The first SELECT shows the current tokens of this login. Than the procedure Chain.Test is executed and the current tokens are selected again:

You can clearly see that the procedure changed the security context completely. Before the procedure call all the tokens where related to the ChainPrincipal1 login as it was used to execute the batch. Within the procedure however all the tokens came from ChainPrincipal2. ChainPrincipal1 is not mentioned anymore. So, even if ChainPrincipal1 had a specific permission granted or denied, inside the procedure that would not matter as only the user and login hierarchies of ChainPrincipal2 are checked.

There is one important difference between the two results. While the first has GRANT OR DENY in the usage column for all four rows, the second result shows DENY ONLY for the two rows that are related to logins. The implication of this is, that any server level permissions granted to the ChainPrincipal2 login are of no use within this procedure. However, if there is something that was explicitly denied, that would take effect during the execution of this procedure.

Now let's turn the TRUSTWORTHY database property is to on:

[sql] ALTER DATABASE TrustDb SET TRUSTWORTHY ON;
EXEC hlp.DatabaseProperties;
[/sql]

After that switch let's rerun the same query that we executed before:

[sql] EXECUTE AS LOGIN='ChainPrincipal1';
GO
SELECT * FROM hlp.CurrentTokens();
EXEC Chain.Test;
GO
REVERT;
[/sql]

This time the result looks like this:

There is no difference in the token list outside of the procedure execution. However, the list from inside the procedure changed dramatically. There are two new user rows and one login row that show the usage AUTHENTICATOR. What the implications of that are will be the topic of another post.

The more important change is that the two login rows that previously showed DENY ONLY now show GRANT OR DENY as well. That means, that any server level permissions that are granted to the ChainPrincipal2 login or the roles it is a member of are in full effect while the procedure is executing.

Summary

We investigated how the TRUSTWORTHY database property influences the security context of sql modules that are created with a WITH EXECUTE AS clause. During the execution of a module like this, the security context is completely switched to the one of the principal that was specified in the WITH EXECUTE AS clause. However, server level permissions of that principal apply only if the TRUSTWORTHY database property is set to on.

This behavior allows us to write procedures that execute functionality requiring server level permissions. However it also opens up a few vulnerabilities. Particularly it might allow a malicious person to gain membership in the sysadmin server role. The details of this exploit are topic of the next article. Stay tuned.