Cross-database access. Part 4 – the ultimate solution

Finally, after three weeks, during which we had a chance to see gradually better attempts to solve cross-db access issue, we are ready to see the very best use of code signing.

This time the owner of a resource database will be the only person who controls the private key needed to sign code modules. As a result, we will be able to fully benefit from it — if somebody else (i.e. the owner of the remote database) tries to change signed code module, the signature is invalid and the additional permission is lost.

Necessary preparations

As usual, we will start by creating three logins and two databases: the first one will be owned by User1, the second one by User2:

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

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

CREATE LOGIN User3
WITH PASSWORD = 'P@SSw0rd1';
GO

CREATE DATABASE DB1;
CREATE DATABASE DB2;
GO

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

 

But this time in DB1 we have to create not only the table, but also two stored procedures — one of those procedures will be called from the other database:

EXECUTE AS LOGIN ='User1'
USE DB1
GO

CREATE TABLE T (K INT);
GO

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

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

 

Finally, let me finalize this part of our task. To do this I will need another stored procedure — as we will later use this one to get the signature that can be given to the remote database owner, the body of it has to be exactly the same as the body of the code module that we are going to give cross-db access. This is why in the last SELECT a three-part name was used:

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

 

If we test our stored procedures by calling them from the local database, they should work seamlessly:

CREATE USER User3
GO

GRANT EXECUTE ON CrossDBProc TO User3
GRANT EXECUTE ON spDelete TO User3
GRANT EXECUTE ON spSelect TO User3
GO

EXECUTE AS USER ='User3'
GO

EXEC CrossDBProc
GO
-------
principal_id    sid    name    type    usage
281    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL LOGIN    DENY ONLY    
2    0x02    public    SERVER ROLE    DENY ONLY    

principal_id    sid    name    type    usage
5    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL USER    GRANT OR DENY
0    0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY

 

So far, so good. Now it’s time to recreate the same CrossDBProc in the remote database and try to call it as the same User3 user:

REVERT
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

CREATE USER User3
GRANT EXECUTE ON CrossDBProc TO User3
GO

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

principal_id    sid    name    type    usage
281    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL LOGIN    DENY ONLY    
2    0x02    public    SERVER ROLE    DENY ONLY    

principal_id    sid    name    type    usage
5    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL USER    GRANT OR DENY
0    0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY

 

Of course, this attempt fails.

Signing remote code modules without giving away the private key

This is the most interesting part — we are going to allow the owner of the remote database signing a stored procedure (from part 3 we already know that this is a necessary step to get across database boundary without granting the over-powered authorization permission). But this time only the approved stored procedure can be signed.

Firstly, we will need a database master key (to protect a certificate), a certificate (to sign a stored procedure) and a user (to give him necessary permissions):

REVERT
GO
USE master
GO
REVERT
GO

EXECUTE AS LOGIN ='User1'
USE DB1
GO

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

GRANT AUTHENTICATE 
TO UserCert1
GO

 

Now we are ready to create the final version of our cross-db stored procedure — note, this procedure is executed as a user created from the certificate. In addition, we are going to sign this stored procedure — this is the only way to generate the correct signature of it:

CREATE PROCEDURE spCrossDB
  WITH EXECUTE AS 'user3'
AS
BEGIN
    SELECT * FROM sys.login_token
    SELECT db_name()
    SELECT * FROM sys.user_token
    EXEC db1.dbo.spSelect
END
GO

ADD SIGNATURE TO spCrossDB
BY CERTIFICATE Cert1
GO

 

The signature we are interested in can be selected with the following query:

SELECT crypt_property
FROM sys.crypt_properties
WHERE object_name(major_id) = 'spCrossDB'
GO
-----
0x93829A06C36EE93B5B3DD087417023D143DB08D567C43037E55DA2B6CE46B1E96E6769965363AD15C0F01E95498074DFAA44C23244CC997D6042155566F2FE7986ACC93755BDEF109EDFF2201787D7BA492DC15644563308172D6BE2D2FD300698D4B027AEF14514DC9EA50C2CBEB39EDF63A903D056DD504A5E9EAB7C2D9653

 

This signature and the public key associated with the certificate used to sign the stored procedure have to be given to the other database owner:

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

 

Now, he can recreate exactly the same stored procedure inside his database:

USE master
GO
REVERT
GO

EXECUTE AS LOGIN ='User2'
USE DB2;
GO

CREATE PROCEDURE spCrossDB
  WITH EXECUTE AS 'user3'
AS
BEGIN
    SELECT * FROM sys.login_token
    SELECT db_name()
    SELECT * FROM sys.user_token
    EXEC db1.dbo.spSelect
END
GO

 

But to sign it, the private key will be needed, or not?

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

CREATE USER UserCert1
FROM CERTIFICATE Cert1
GO

ADD SIGNATURE TO spCrossDB
BY CERTIFICATE Cert1
GO
-----
Msg 15556, Level 16, State 1, Line 2
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

 

No, to sign a code module a valid signature will be enough:

ADD SIGNATURE TO spCrossDB
BY CERTIFICATE Cert1
WITH SIGNATURE = 0x93829A06C36EE93B5B3DD087417023D143DB08D567C43037E55DA2B6CE46B1E96E6769965363AD15C0F01E95498074DFAA44C23244CC997D6042155566F2FE7986ACC93755BDEF109EDFF2201787D7BA492DC15644563308172D6BE2D2FD300698D4B027AEF14514DC9EA50C2CBEB39EDF63A903D056DD504A5E9EAB7C2D9653
GO
-----
Command(s) completed successfully.

 

Bingo, now this stored procedure has the authenticate permission (from the certificate) and the execute permission to spSelect in the resource database (from User3). As a result it just works:

EXEC spCrossDB
GO
------
principal_id    sid    name    type    usage
281    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL LOGIN    DENY ONLY    
2    0x02    public    SERVER ROLE    DENY ONLY    
0    0x010600000000000901000000609A101FF52446EDA6529E47801EB8F392FBCE94    S-1-9-1-521181792-3980797173-1201558182-4088929920-2496592786    CERTIFICATE    DENY ONLY    
0    0x010600000000000901000000609A101FF52446EDA6529E47801EB8F392FBCE94    S-1-9-1-521181792-3980797173-1201558182-4088929920-2496592786    CERTIFICATE    AUTHENTICATOR

(No column name)
DB2

principal_id    sid    name    type    usage
5    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL USER    GRANT OR DENY
0    0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY
6    0x010600000000000901000000609A101FF52446EDA6529E47801EB8F392FBCE94    UserCert1    USER MAPPED TO CERTIFICATE    GRANT OR DENY
256    0x010600000000000901000000609A101FF52446EDA6529E47801EB8F392FBCE94    Cert1    CERTIFICATE    AUTHENTICATOR

(No column name)
DB1

principal_id    sid    name    type    usage
281    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL LOGIN    DENY ONLY    
2    0x02    public    SERVER ROLE    DENY ONLY    
0    0x010600000000000901000000609A101FF52446EDA6529E47801EB8F392FBCE94    S-1-9-1-521181792-3980797173-1201558182-4088929920-2496592786    CERTIFICATE    AUTHENTICATOR

principal_id    sid    name    type    usage
5    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL USER    GRANT OR DENY
0    0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY
256    0x010600000000000901000000609A101FF52446EDA6529E47801EB8F392FBCE94    Cert1    CERTIFICATE    AUTHENTICATOR

 

However, if this stored procedure was altered, the additional permissions are lost:

ALTER PROCEDURE spCrossDB
  WITH EXECUTE AS 'user3'
AS
BEGIN
    SELECT * FROM sys.login_token
    SELECT db_name()
    SELECT * FROM sys.user_token
    EXEC db1.dbo.spDelete
END
GO

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

principal_id    sid    name    type    usage
281    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL LOGIN    DENY ONLY    
2    0x02    public    SERVER ROLE    DENY ONLY    

(No column name)
DB2

principal_id    sid    name    type    usage
5    0x10CE62D6D1983144BF1B1024EFEE1247    User3    SQL USER    GRANT OR DENY
0    0x01050000000000090400000083741B006749C04BA943C02702F2A762    public    ROLE    GRANT OR DENY

 

What’s more, the modified version of this stored procedure cannot be re-signed — this signature is only valid with the original, approved version of it:

ADD SIGNATURE TO spCrossDB
BY CERTIFICATE Cert1
WITH SIGNATURE = 0x93829A06C36EE93B5B3DD087417023D143DB08D567C43037E55DA2B6CE46B1E96E6769965363AD15C0F01E95498074DFAA44C23244CC997D6042155566F2FE7986ACC93755BDEF109EDFF2201787D7BA492DC15644563308172D6BE2D2FD300698D4B027AEF14514DC9EA50C2CBEB39EDF63A903D056DD504A5E9EAB7C2D9653

GO
-
Msg 15299, Level 16, State 1, Line 1
The signature of the public key is invalid.

 

Lesson learned — secure cross-database access is not as easy as setting a database trustworthy. But with code-signing it is perfectly achievable. All you need to do is create a local version of this code module, sign it and give away the certificate (only the public key) and the signature.

See you next time

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