Cross-database access. Part 2 – trustworthy databases and dbo as an authenticator

In the previous part “Cross-database access. Part 1 – the worst nightmare or why applications should not use sa login” we saw what happens if an application uses sa login. Now, we are going to discuss a better and more secure, but still not the best, solution.

Can a dbo access resources from another database?

First, we are going to see the difference between a login’s and a user’s tokens. To do so, let me create two logins and two databases: DB1 is owned by User1, DB2 by User2:

CREATE LOGIN User1
WITH PASSWORD = 'Pa$sw0rd';

CREATE LOGIN User2
WITH PASSWORD = 'P@ssw0rd';
GO

CREATE DATABASE DB1;
CREATE DATABASE DB2;
GO

ALTER AUTHORIZATION ON DATABASE::DB1 TO User1
ALTER AUTHORIZATION ON DATABASE::DB2 TO User2
GO

 

Suppose that DB1 is a resource database and a table in this db should be accessible from DB2. So, as a DB1 owner, I’m going to create this table and grant to DB2 owner necessary permission. Note, to do so, I have to create an appropriate user account in the resource database:

EXECUTE AS LOGIN =''User1''
USE DB1
SELECT USER_NAME()
GO

CREATE TABLE T (K INT);
GO

CREATE USER user2 FOR LOGIN user2;
GO
GRANT SELECT ON T TO user2;
GO

 

Remember, to revert from User1’s login context we have to switch to master:

USE master
GO
REVERT
GO

 

Now login as an owner of the second database and create in their database a stored procedure that returns caller’s security tokens (at both server and database levels), and do cross-database access:

EXECUTE AS LOGIN =''User2''
USE DB2;
SELECT USER_NAME()
GO

CREATE PROC CrossDBProc
AS 
SELECT DB_NAME()
SELECT * FROM sys.login_token;
SELECT * FROM sys.user_token;
SELECT * FROM DB1..T
GO

 

Now, if we try to execute this proc as a User2 login, it will works:

EXEC CrossDBProc
GO
-----
(No column name)
DB2

principal_id    sid                                   name    type            usage
278            0x20A9B1D127687144A75BCA306CBAE5C9    User2    SQL LOGIN        GRANT OR DENY
2                0x02                                public    SERVER ROLE    GRANT OR DENY

principal_id    sid                                                            name    type        usage
1                0x20A9B1D127687144A75BCA306CBAE5C9                            dbo    SQL USER    GRANT OR DENY
0                0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY
16384            0x01050000000000090400000000000000000000000000000000400000    db_owner    ROLE    GRANT OR DENY

 

Note, that at server level this stored procedure has access to User2 login’s token (it’s usage is GRANT OR DENY). And at the database level it has a dbo’s token (because we are still inside DB2, not in a resource database).

Lesson learned — a database owner has, without any additional server or database level configuration, access to resources that live in other databases. All was needed to select rows from a remote table was a user account associated with their login in this remote database and necessary permissions.

What if a dbo creates a user in their database that is associated with another database owner account?

The second scenario we are going to discuss right now looks like this: a database owner will try to get access to remote resources by creating in their database a user account that is associated with a remote database owner account.

To do so, let me create (inside DB2 database and as a User2) this user account and grant it an execute permission on our cross-db stored procedure:

CREATE USER user1 FOR LOGIN user1;
GO
GRANT EXECUTE ON CrossDBProc TO user1
GO

 

Everything looks fine, now it’s time to test this approach:

EXECUTE AS user ='user1'
SELECT USER_NAME()
EXEC CrossDBProc
GO
-----
Msg 916, Level 14, State 1, Procedure CrossDBProc, Line 6
The server principal "User1" is not able to access the database "DB1" under the current security context.

(No column name)
user1

(No column name)
DB2

principal_id    sid                                    name    type            usage
277            0x7F2EBC4F7BA3FB4BAEA4E07DE152B081    User1    SQL LOGIN        DENY ONLY    
2                0x02                                public    SERVER ROLE    DENY ONLY    

principal_id    sid                                                        name        type        usage
5             0x7F2EBC4F7BA3FB4BAEA4E07DE152B081                            user1    SQL USER    GRANT OR DENY
0             0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE        GRANT OR DENY

 

Well, the server principal “User1″ is not able to access the database “DB1″ under the current security context. What does this mean?

If we check server level tokens, we will find that User1 login token has usage DENY ONLY. As a result, the User2 user account created inside DB2 cannot access resources outside this database. Great, this is what we should expect.

But what if a DB2 owner try to impersonate User1 (the resource database owner) login?

REVERT
GO
EXECUTE AS LOGIN ='User1'
GO
-----
Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal "User1" does not exist, this type of principal cannot be impersonated, or you do not have permission.

 

Lesson learned — a db owner can do everything but only onside their database. This also means that a db owner is not allowed to impersonate other logins, only local users account.

Trustworthy databases

As you probably know, the TRUSTWORTHY database property indicates whether the whole SQL Server trusts the database and all the contents within it. This property is by default switch off, but you can set it to:

1. Use EXTERNAL_ACCESS or UNSAFE assemblies.

2. Enable cross-database access.

In third scenario we will see how to configure this option and how serious the implications of doing this are.

First of all, a db owner cannot set this property, even for their own database:

ALTER DATABASE DB2
SET TRUSTWORTHY ON
GO
------
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

 

Only sysadmin can do this, which makes perfect sense:

USE master
GO
REVERT
GO

ALTER DATABASE DB2
SET TRUSTWORTHY ON
GO

 

Note: This property is automatically reset when a database is attached or restored. Otherwise a malicious user will be able to attach a trustworthy database to difrrent SQL Server.

Now DB2 is trustworthy. Let see what has change in terms of cross-db access:

EXECUTE AS LOGIN ='User2'
USE DB2; 

EXECUTE AS USER ='User1'
GO

EXEC CrossDBProc
GO
------
Msg 916, Level 14, State 1, Procedure CrossDBProc, Line 6
The server principal "User1" is not able to access the database "DB1" under the current security context.

(No column name)
DB2

principal_id    sid                                name        type        usage
277            0x7F2EBC4F7BA3FB4BAEA4E07DE152B081    User1    SQL LOGIN    DENY ONLY    
2                0x02                                public    SERVER ROLE    DENY ONLY    
278            0x20A9B1D127687144A75BCA306CBAE5C9    User2    SQL LOGIN    AUTHENTICATOR

principal_id    sid                                                name        type        usage
5        0x7F2EBC4F7BA3FB4BAEA4E07DE152B081                        user1    SQL USER    GRANT OR DENY
0        0x01050000000000090400000083741B006749C04BA943C02702F2A762 public    ROLE    GRANT OR DENY
1        0x20A9B1D127687144A75BCA306CBAE5C9                        dbo        SQL USER    AUTHENTICATOR
16384    0x01050000000000090400000000000000000000000000000000400000 db_owner    ROLE    AUTHENTICATOR

 

Our stored procedure still fails, but it gains three additional tokens: one at server level (User2 SQL LOGIN with AUTHENTICATOR usage) and two at database level (dbo/db_owner also with AUTHENTICATOR usage). Seems like by setting TRUSTWORTHY property, we allow use of the current database owner (User2 login) as an authenticator. But this user also has to have an authenticate permission in the remote database:

REVERT
GO
USE master
GO
REVERT
GO

EXECUTE AS LOGIN ='User1'
USE DB1
GO

GRANT AUTHENTICATE TO User2
GO

 

Finally, it’s time to test complete solution:

USE master
GO
REVERT
GO

EXECUTE AS LOGIN ='User2'
USE DB2; 

EXECUTE AS USER ='User1'
GO

EXEC CrossDBProc
GO

 

This time our cross-db stored proc executed successfully but under User1 security account.

This means that from now on a User2 (DB2 ower) can change this stored proc:

REVERT
GO

ALTER PROC CrossDBProc
AS 
SELECT DB_NAME()
SELECT * FROM sys.login_token;
SELECT * FROM sys.user_token;
DELETE FROM DB1..T
GO

 

And even if user2 cannot execute it, because it only has a select permission (the permission given by DB1 owner):

EXEC CrossDBProc
GO
------
Msg 229, Level 14, State 5, Procedure CrossDBProc, Line 6
The DELETE permission was denied on the object ''T'', database ''DB1'', schema ''dbo''.

 

He can impersonate User1 in their database and then execute maliciously modified stored procedure:

EXECUTE AS USER ='User1'
GO

EXEC CrossDBProc
GO

REVERT

 

Lesson learned — if a database trust another one, this trust is virtually unlimited.

This is why in the upcoming parts of this series we will see a much more secure solutions.

See you there.

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration, Security, SQL Server and tagged by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Comments are closed.