Cross-database access. Part 3 – the first attempt to use code signing

In previous parts of the series we examined two insecure “solutions”. Even if we were able to get cross-database access, the first one (you can read about it here: Cross-database access. Part 1 – the worst nightmare or why applications should not use sa login) requires sysadmin privilege, the second one (described here: Cross-database access. Part 2 – trustworthy databases and dbo as an authenticator) was at first look more secure, but in the end we figured out that by granting authenticate privilege, we virtually lost control over our database. Now it’s time to learn the first secure solution to this problem.

Code signing

SQL Server 2005 onwards allows as to use built-in cryptography functions to encrypt data and digitally sign code modules. We are going to use this second capability. But first, we need to recreate two sample databases, as previously each one is owned by different user. The first database (DB1) is our resource database, from the second one (DB2) we will try to access DB1’s table:

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

EXECUTE AS LOGIN ='User1'
USE DB1
GO

CREATE TABLE T (K INT);
GO

USE master
GO
REVERT
GO

EXECUTE AS LOGIN ='User2'
USE DB2;
GO

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

 

As we already know, right now DB2 owner cannot execute this stored procedure:

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

 

Our first attempt to use code signing will be a little bit odd — we are going to create a certificate not inside the resource database, but in the “remote” database, the one from where we are going to get cross-db access.

In order to do this, we will need:

1. A database master key (to protect a certificate)

2. A certificate (to sign a stored procedure)

3. A user account associated with this certificate (to be able to grant necessary permission):

CREATE MASTER KEY
ENCRYPTION BY PASSWORD ='DSFSSFSFkjh(*&FAV23452'
GO

CREATE CERTIFICATE Cert1
WITH SUBJECT = 'Cross-db access'
GO

CREATE USER UserCert1
FROM CERTIFICATE Cert1
GO

 

The next necessary step is to sign crossdb stored procedure using a private key associated with our certificate:

ADD SIGNATURE TO CrossDBProc
BY CERTIFICATE Cert1
GO

 

From now one, this stored procedure will get an additional user token — the token of the user who was associated with the certificate that was used to sign this proc.

Because a certificate can be backed up and then restore in another database, this way we can create in the resource database the same user account, and then give it appropriate permissions:

BACKUP CERTIFICATE Cert1
TO FILE ='C:\SQL\Cert1.crt'
GO

USE master
GO
REVERT
GO

EXECUTE AS LOGIN ='User1'
USE DB1;
GO

CREATE CERTIFICATE Cert1 
FROM FILE ='C:\SQL\Cert1.crt'

CREATE USER UserCert1
FROM CERTIFICATE Cert1
GO

GRANT SELECT ON T TO UserCert1
GO

 

That’s all — let me test our solution:

USE master
GO
REVERT
GO

EXECUTE AS LOGIN ='User2'
USE DB2;
GO

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
0              0x0106000000000009010000006124E7E39CAB4197F2295A4C8C13EBBF96DD69B3    S-1-9-1-3823576161-2537663388-1280977394-3219854220-3010059670    CERTIFICATE    GRANT OR DENY

principal_id    sid                                                        name    type    usage
1            0x20A9B1D127687144A75BCA306CBAE5C9                            dbo    SQL USER    GRANT OR DENY
0            0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY
5            0x0106000000000009010000006124E7E39CAB4197F2295A4C8C13EBBF96DD69B3    UserCert1    USER MAPPED TO CERTIFICATE    GRANT OR DENY
16384        0x01050000000000090400000000000000000000000000000000400000    db_owner    ROLE    GRANT OR DENY

 

It works as expected. Furthermore, if a DB2 owner tried to alter this stored procedure (well, as we will see shortly, if he tried to perform an operation to which this certificate-based user has no permission), this attempt will failed:

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

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

 

And re-signing this stored procedure will not help in this scenario:

ADD SIGNATURE TO CrossDBProc
BY CERTIFICATE Cert1
GO

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

 

Lesson learned — certificates and code signing can be used together to create some kind “multi-database” user accounts. By doing this we will be able to grant permissions to this special user accounts without granting authenticate permission or trusting other databases.

In the last part of this series we will see the ultimate solution to this problem. It is also based on code signing, but this time we will fully utilize the power of this functionality.

See you

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in 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.

Leave a Reply

Connect with:
  • This field its required.
  • This field its required.
    • Message is required