Re: Cross-database execution permissions with certificates and schemas



Dan
are different. It also works if I add the dispatcher user to providerDB
Yes , I did the same , just in additon I grant an exectuon on schema pvd to
dispatcher



"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:54166A91-BFB3-484B-8D38-5EA686F9425E@xxxxxxxxxxxxxxxx
Thanks for the repro scripts.

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?

The dbo user is special for a number of reasons. However, I haven't
worked with certificates much so I'm not exactly sure how this works with
cross-database authentication.

I'm short on time right now to identify the root cause but I'll share some
of my observations when I played around with your scripts. The issue
seems to be related to EXECUTE AS OWNER. The second script works when I
change the "dsp" schema authorization to dbo but then breaks if the
database owners are different. It also works if I add the dispatcher user
to providerDB

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Craig Thomas" <CraigThomas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:62334118-872D-49FA-9BB8-4EFFF114F9CF@xxxxxxxxxxxxxxxx
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

  • 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 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 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)