Cross-database execution permissions with certificates and schemas



I have run into an obstacle while implementing cross-database execution
permissions using certificates and schemas.

The scenario I'm working with is pretty simple, and I've got a formula that
works and a formula that doesn't (complete with scripts below). Can someone
take a look and help me out?

Database 1 (named [dispatcherDB]) has a stored procedure called [dispatch].
This stored procedure executes a procedure in database 2 (named [providerDB]).

I follow these steps to create everything and grant cross-database
permissions:
1. Executing as login='sa':
1.1 create a database [dispatcherDB], and a login [dispatcher],
1.2 create a second database [providerDB], and a login [provider],
1.3 in the [dispatcherDB] create a user [dispatcher],
1.4 make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner',
N'dispatcher')
2. Executing as user='dispatcher':
2.1 create a stored procedure [dispatch],
2.2 create a certificate,
2.3 sign the stored procedure (ADD SIGNATURE TO OBJECT::[dispatch] ...)
2.4 Discard the private key from the certificate,
2.5 Back up the certificate to a file.
3. Executing as login='sa':
3.1 in the [providerDB] create a user [provider] with a default schema [pvd]
3.2 make the provider user the owner (EXEC sp_addrolemember N'db_owner',
N'provider')
4. Executing as user='provider]':
4.1 create a schema [pvd]
4.2 create a certificate from the file.
4.3 Create a user from the certificate,
4.4 GRANT AUTHENTICATE TO the certificate user,
4.5 GRANT EXECUTE ON [providerDB].[pvd].[testProc] to the certificate user.
5. Executing as user='dispatcher'
5.1 execute [dispatch]

Everything works in this case.

But here's a description of the subtly different case (which is the
condition my application demands), which does not work:
1. Executing as login='sa':
1.1 create a database [dispatcherDB], and a login [dispatcher],
1.2 create a second database [providerDB], and a login [provider],
1.3 in the [dispatcherDB] create a user [dispatcher] *** with a default
schema [dsp] ***,
1.4 make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner',
N'dispatcher')
2. Executing as user='dispatcher':
2.0 *** create a schema [dsp] ***,
2.1 create a stored procedure [dispatch] (this time it is [dsp].[dispatch],
not [dbo].[dispatch]),
2.2 create a certificate,
2.3 sign the stored procedure (ADD SIGNATURE TO OBJECT::[dispatch] ...)
2.4 Discard the private key from the certificate,
2.5 Back up the certificate to a file.
3. Executing as login='sa':
3.1 in the [providerDB] create a user [provider] with a default schema [pvd]
3.2 make the provider user the owner (EXEC sp_addrolemember N'db_owner',
N'provider')
4. Executing as user='provider]':
4.1 create a schema [pvd]
4.2 create a certificate from the file.
4.3 Create a user from the certificate,
4.4 GRANT AUTHENTICATE TO the certificate user,
4.5 GRANT EXECUTE ON [providerDB].[pvd].[testProc] to the certificate user.
5. Executing as user='dispatcher'
5.1 execute [dispatch]

This time there's the error message:
Msg 916, Level 14, State 1, Procedure testProc, Line 10
The server principal "dispatcher" is not able to access the database
"providerDB" under the current security context.

Okay, there is a difference in the security context of the caller -- the
callee's context is the same in both scenarios.

Here's a look at the rows in sys.user_token in the scenario that works:
<user_token principal_id="1" name="dbo" type="SQL USER" usage="GRANT OR
DENY" />
<user_token principal_id="0" name="public" type="ROLE" usage="GRANT OR
DENY" />
<user_token principal_id="16384" name="db_owner" type="ROLE" usage="GRANT
OR DENY" />

Here's what is in sys.user_token in the scenario that doesn't work:
<user_token principal_id="5" name="dispatcher" type="SQL USER"
usage="GRANT OR DENY" />
<user_token principal_id="0" name="public" type="ROLE" usage="GRANT OR
DENY" />
<user_token principal_id="16384" name="db_owner" type="ROLE" usage="GRANT
OR DENY" />

Did I discover some magic property of the [dbo] user and [dbo] schema (not
likely), or am I missing something conceptual about permissions grants
through certificates?

Thanks,
--Craig.

Here's the script that works: (the script that doesn't follows)
-----
SELECT 0, SUSER_NAME(), USER_NAME()
GO
REVERT
GO
SELECT 1, SUSER_NAME(), USER_NAME()
GO
USE [master]
GO
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'dispatcherDB')
DROP DATABASE [dispatcherDB]
GO
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'providerDB') DROP
DATABASE [providerDB]
GO
IF EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] =
'dispatcher') DROP LOGIN [dispatcher]
GO
IF EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] =
'provider') DROP LOGIN [provider]
GO
EXECUTE xp_cmdshell 'del c:\temp\dispatchSproc.cert', NO_OUTPUT;
GO
CREATE DATABASE [dispatcherDB]
GO
CREATE DATABASE [providerDB]
GO
CREATE LOGIN [dispatcher]
WITH PASSWORD = '53cr37',
DEFAULT_DATABASE = [dispatcherDB],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
CREATE LOGIN [provider]
WITH PASSWORD = '53cr37',
DEFAULT_DATABASE=[providerDB],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
-- Create objects in dispatcher database
USE [dispatcherDB]
GO
CREATE USER [dispatcher]
-- WITH DEFAULT_SCHEMA = [dsp];
GO
EXEC sp_addrolemember N'db_owner', N'dispatcher'
GO
-- Continue to create objects as the dispatcher user, in the default schema
for this user, dsp:
EXECUTE AS USER = 'dispatcher'
GO
-- CREATE SCHEMA [dsp] AUTHORIZATION [dispatcher];
GO
CREATE PROCEDURE [dispatch]
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @lclRequest xml;
DECLARE @lclResponse xml;

SET @lclRequest = (
SELECT
[principal_id],
[name],
[type],
[usage]
FROM
[sys].[user_token] AS [user_token]
FOR XML AUTO, ROOT('Request')
);
SELECT @lclRequest;

EXECUTE [providerDB].[pvd].[testProc]
@inRequest = @lclRequest,
@outResponse = @lclResponse OUTPUT;

SELECT @lclResponse AS 'Response';
END;
GO
-- Drop existing certificate
IF EXISTS (
SELECT *
FROM [sys].[certificates]
WHERE [name] = 'dispatcherSproc'
) DROP CERTIFICATE [dispatchSproc];
GO
-- Create a certificate to sign this stored procedure
CREATE CERTIFICATE [dispatchSproc]
ENCRYPTION BY PASSWORD = '53cr37'
WITH SUBJECT = 'Cross-database permissions certificate',
START_DATE = '01/01/2008',
EXPIRY_DATE = '12/31/2018';
GO
-- Sign the service process' stored procedure with the certificate
ADD SIGNATURE TO OBJECT::[dispatch]
BY CERTIFICATE [dispatchSproc]
WITH PASSWORD = '53cr37';
GO
-- Discard the private key as a security measure
ALTER CERTIFICATE [dispatchSproc]
REMOVE PRIVATE KEY;
GO
BACKUP CERTIFICATE [dispatchSproc]
TO FILE = 'c:\temp\dispatchSproc.cert';
GO
-- back to sa:
REVERT
GO
SELECT 2, SUSER_NAME(), USER_NAME()
GO
-- Create objects in provider database
USE [providerDB]
GO
CREATE USER [provider]
WITH DEFAULT_SCHEMA = [pvd];
GO
EXEC sp_addrolemember N'db_owner', N'provider'
GO
-- Continue to create objects as the provider user, in the default schema
for this user, pvd:
EXECUTE AS USER = 'provider'
GO
CREATE SCHEMA [pvd] AUTHORIZATION [provider]
GO
-- Create a certificate from the file created for signing dispatcherDB's
stored procedures:
CREATE CERTIFICATE [dispatchSproc]
FROM FILE = 'c:\temp\dispatchSproc.cert';
GO
-- Create a user from the certificate
CREATE USER [dispatchSprocUser]
FROM CERTIFICATE [dispatchSproc];
GO
CREATE TABLE [test](
[testID] [bigint] IDENTITY(1,1) NOT NULL,
[request] [xml] NOT NULL,
[requester] [xml] NOT NULL,
[updatetime] [datetime] NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO
CREATE PROCEDURE [testProc]
@inRequest xml,
@outResponse xml OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @lclRequester xml;
SET @lclRequester = (
SELECT
[principal_id],
[name],
[type],
[usage]
FROM
[sys].[user_token] AS [user_token]
FOR XML AUTO, ROOT('Requester')
);

INSERT [test] (
request,
requester
) VALUES (
@inRequest,
@lclRequester
);

SET @outResponse = (
SELECT *
FROM [test]
FOR XML AUTO, ROOT('Response')
);
END;
GO
GRANT AUTHENTICATE TO [dispatchSprocUser];
GO
GRANT EXECUTE ON [testProc] TO [dispatchSprocUser];
GO
-- back to sa
REVERT
GO
SELECT 3, SUSER_NAME(), USER_NAME()
GO
USE [dispatcherDB]
GO
-- test as dispatcher login:
EXECUTE AS LOGIN = 'dispatcher'
GO
EXECUTE [dispatch]
GO
REVERT
GO

-----

Here's the script that doesn't work:

-----
SELECT 0, SUSER_NAME(), USER_NAME()
GO
REVERT
GO
SELECT 1, SUSER_NAME(), USER_NAME()
GO
USE [master]
GO
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'dispatcherDB')
DROP DATABASE [dispatcherDB]
GO
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'providerDB') DROP
DATABASE [providerDB]
GO
IF EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] =
'dispatcher') DROP LOGIN [dispatcher]
GO
IF EXISTS (SELECT * FROM [sys].[server_principals] WHERE [name] =
'provider') DROP LOGIN [provider]
GO
EXECUTE xp_cmdshell 'del c:\temp\dispatchSproc.cert', NO_OUTPUT;
GO
CREATE DATABASE [dispatcherDB]
GO
CREATE DATABASE [providerDB]
GO
CREATE LOGIN [dispatcher]
WITH PASSWORD = '53cr37',
DEFAULT_DATABASE = [dispatcherDB],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
CREATE LOGIN [provider]
WITH PASSWORD = '53cr37',
DEFAULT_DATABASE=[providerDB],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
GO
-- Create objects in dispatcher database
USE [dispatcherDB]
GO
CREATE USER [dispatcher]
WITH DEFAULT_SCHEMA = [dsp];
GO
EXEC sp_addrolemember N'db_owner', N'dispatcher'
GO
-- Continue to create objects as the dispatcher user, in the default schema
for this user, dsp:
EXECUTE AS USER = 'dispatcher'
GO
CREATE SCHEMA [dsp] AUTHORIZATION [dispatcher];
GO
CREATE PROCEDURE [dispatch]
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @lclRequest xml;
DECLARE @lclResponse xml;

SET @lclRequest = (
SELECT
[principal_id],
[name],
[type],
[usage]
FROM
[sys].[user_token] AS [user_token]
FOR XML AUTO, ROOT('Request')
);
SELECT @lclRequest;

EXECUTE [providerDB].[pvd].[testProc]
@inRequest = @lclRequest,
@outResponse = @lclResponse OUTPUT;

SELECT @lclResponse AS 'Response';
END;
GO
-- Drop existing certificate
IF EXISTS (
SELECT *
FROM [sys].[certificates]
WHERE [name] = 'dispatcherSproc'
) DROP CERTIFICATE [dispatchSproc];
GO
-- Create a certificate to sign this stored procedure
CREATE CERTIFICATE [dispatchSproc]
ENCRYPTION BY PASSWORD = '53cr37'
WITH SUBJECT = 'Cross-database permissions certificate',
START_DATE = '01/01/2008',
EXPIRY_DATE = '12/31/2018';
GO
-- Sign the service process' stored procedure with the certificate
ADD SIGNATURE TO OBJECT::[dispatch]
BY CERTIFICATE [dispatchSproc]
WITH PASSWORD = '53cr37';
GO
-- Discard the private key as a security measure
ALTER CERTIFICATE [dispatchSproc]
REMOVE PRIVATE KEY;
GO
BACKUP CERTIFICATE [dispatchSproc]
TO FILE = 'c:\temp\dispatchSproc.cert';
GO
-- back to sa:
REVERT
GO
SELECT 2, SUSER_NAME(), USER_NAME()
GO
-- Create objects in provider database
USE [providerDB]
GO
CREATE USER [provider]
WITH DEFAULT_SCHEMA = [pvd];
GO
EXEC sp_addrolemember N'db_owner', N'provider'
GO
-- Continue to create objects as the provider user, in the default schema
for this user, pvd:
EXECUTE AS USER = 'provider'
GO
CREATE SCHEMA [pvd] AUTHORIZATION [provider]
GO
-- Create a certificate from the file created for signing dispatcherDB's
stored procedures:
CREATE CERTIFICATE [dispatchSproc]
FROM FILE = 'c:\temp\dispatchSproc.cert';
GO
-- Create a user from the certificate
CREATE USER [dispatchSprocUser]
FROM CERTIFICATE [dispatchSproc];
GO
CREATE TABLE [test](
[testID] [bigint] IDENTITY(1,1) NOT NULL,
[request] [xml] NOT NULL,
[requester] [xml] NOT NULL,
[updatetime] [datetime] NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO
CREATE PROCEDURE [testProc]
@inRequest xml,
@outResponse xml OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @lclRequester xml;
SET @lclRequester = (
SELECT
[principal_id],
[name],
[type],
[usage]
FROM
[sys].[user_token] AS [user_token]
FOR XML AUTO, ROOT('Requester')
);

INSERT [test] (
request,
requester
) VALUES (
@inRequest,
@lclRequester
);

SET @outResponse = (
SELECT *
FROM [test]
FOR XML AUTO, ROOT('Response')
);
END;
GO
GRANT AUTHENTICATE TO [dispatchSprocUser];
GO
GRANT EXECUTE ON [testProc] TO [dispatchSprocUser];
GO
-- back to sa
REVERT
GO
SELECT 3, SUSER_NAME(), USER_NAME()
GO
USE [dispatcherDB]
GO
-- test as dispatcher login:
EXECUTE AS LOGIN = 'dispatcher'
GO
EXECUTE [dispatch]
GO
REVERT
GO

-----
.



Relevant Pages

  • Re: Cross-database execution permissions with certificates and sch
    ... activated user cannot access objects in other schemas in this database. ... injection is defended against, however, the threat exists anywhere EXECUTE ... CREATE USER dispatcher ... the certificate is a trusted authenticator because you granted AUTHENTICATE ...
    (microsoft.public.sqlserver.security)
  • Re: Cross-database execution permissions with certificates and schemas
    ... The issue seems to be related to EXECUTE AS OWNER. ... make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner', ... Discard the private key from the certificate, ... make the provider user the owner (EXEC sp_addrolemember N'db_owner', ...
    (microsoft.public.sqlserver.security)
  • Re: Cross-database execution permissions with certificates and schemas
    ... make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner', ... Discard the private key from the certificate, ... make the provider user the owner (EXEC sp_addrolemember N'db_owner', ... execute [dispatch] ...
    (microsoft.public.sqlserver.security)
  • Re: Cross-database execution permissions with certificates and schemas
    ... seems to be related to EXECUTE AS OWNER. ... It also works if I add the dispatcher user ... Discard the private key from the certificate, ... make the provider user the owner (EXEC sp_addrolemember N'db_owner', ...
    (microsoft.public.sqlserver.security)
  • Re: Cross-database execution permissions with certificates and sch
    ... Whatever principal you impersonate with EXECUTE AS must have a security context in both databases but it doesn't need to be dbo. ... CREATE USER dispatcher ... The reason EXECUTE AS OWNER works with dbo as the schema owner is that 1) the certificate is a trusted authenticator because you granted AUTHENTICATE and 2) the impersonated dbo principal exists in dispatchDB by virtue of the fact that both databases are owed by the same server principal. ... You can still use the certificate as the authenticator as long as the impersonated principal has a security context in the referenced database as well. ...
    (microsoft.public.sqlserver.security)