Re: Cross-database execution permissions with certificates and sch

The evidence that Dan, Uri, and I find suggests that the owner must be the
database principal [dbo]. (If this understanding is wrong, I'd appreciate
learning about it!)

Whatever principal you impersonate with EXECUTE AS must have a security context in both databases but it doesn't need to be dbo. As Uri and I mentioned earlier, the second script will work if you add the impersonated principal (or guest) to providerDB. No permissions need be granted since the user is only needed for context:

USE providerDB,
CREATE USER dispatcher
GO

--this now works
USE [dispatcherDB]
GO
EXECUTE AS LOGIN = 'dispatcher'
GO
EXECUTE [dispatch]
GO
REVERT
GO

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 (sa).

The upshot here seems to be that I can't employ schema-scoped permissions on
the execution context of my service broker activated stored procedure.

Is there any truth to this?

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. I'm not exactly sure I understand what you mean by schema-scoped permissions, though. Can you elaborate?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Craig Thomas" <CraigThomas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:B3F2FDA3-9C46-4203-9FB5-B373204D83E6@xxxxxxxxxxxxxxxx
Dan, Uri,

Thanks much for looking into this, for the followup and the references.

I confess, though, that I still don't see the principals at play here (pun
intended).

In the article Uri references
(http://www.sommarskog.se/grantperm.html#ownershipchaining) there is a
section that describes the perils of using "EXECUTE AS OWNER", particularly
when the owner has plenty of privileges. Yet in every reference I read about
Service Broker, EXECUTE AS OWNER is the way to achieve the cross-database
permissions needed for an "activated" stored procedure.

The evidence that Dan, Uri, and I find suggests that the owner must be the
database principal [dbo]. (If this understanding is wrong, I'd appreciate
learning about it!)

The upshot here seems to be that I can't employ schema-scoped permissions on
the execution context of my service broker activated stored procedure.

Is there any truth to this?

Any and all insights much appreciated.

Thanks,
--Craig.


"Dan Guzman" wrote:

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 (

.

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)
  • 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 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: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)