HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.servicebroker » cross-database execution with certificates and schema?
Topic: cross-database execution with certificates and schema?
I hit a permissions wall - or maybe a wall in my understanding of how
permissions grants to certificate-based users work.
I am implementing a cross-database execution in an activated stored procedure.
The wall - or the gap in my understanding - has to do with the use of a
schema in the *caller's database*.
Here's the deal: I've got two scenarios, one that works and one that
doesn't. The scenario that doesn't work, unfortunately, represents the model
for implementation I've got to target.
As background, the environment for my team's application involves a central
server and a collection of appliances geographically distributed. All are
running SQL Server 2005. There's transport security among the server
instances, and dialog security among the services. The application targets a
very security conscious business sector, so I am interested in employing the
best possible practices for the SQL Server implementation.
Here's a description of the scenario that works:
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 the service broker activated stored procedure [DispatchMessages],
2.2 create a certificate,
2.3 sign the stored procedure (ADD SIGNATURE TO OBJECT::[DispatchMessages]
....)
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 stored procedure to be called by the [dispatcher] user,
[MessageHandler]
4.3 create a certificate from the file,
4.4 Create a user from the certificate,
4.5 GRANT AUTHENTICATE TO the certificate user,
4.6 GRANT EXECUTE ON [providerDB].[pvd].[MessageHandler] to the certificate
user.
5. Send a message to the queue.
Voila! It works. This is the pattern that I've read about (in Klaus
Aschenbrenner's book Pro SQL Server 2005 Service Broker, and in Remus
Rusano's blog
http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx).
But here's the problem - If the caller's database has an explicitly created
user and schema (not simply mapping to the default [dbo] user and the
corresponding [dbo] schema), I get 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.
Here's a description of the scenario that fails (the steps with *** are the
only differences from the previous scenario):
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 [DispatchMessages] (*** this time it is
[dsp].[DispatchMessages], not [dbo].[DispatchMessages]),
2.2 create a certificate,
2.3 sign the stored procedure (ADD SIGNATURE TO OBJECT::[DispatchMessages]
....)
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 stored procedure to be called by the [dispatcher] user,
[MessageHandler]
4.3 create a certificate from the file,
4.4 Create a user from the certificate,
4.5 GRANT AUTHENTICATE TO the certificate user,
4.6 GRANT EXECUTE ON [providerDB].[pvd].[MessageHandler] to the certificate
user.
5. Send a message
This results in 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.
I managed to get hold of the sys.user_token in both scenarios. Perhaps this
will help.
Here's the user token, captured in the calling procedure, during the
successful execution:
<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 the user token, captured in the calling procedure, during the failed
execution:
<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" />
There's a difference, but I'm not sure what is significant about it.
What is the technique I would need to allow the second scenario to succeed?
Any clues?
Or did I step in some magical properties bestowed upon the [dbo] user and
the [dbo] schema?
I realize that this is not a service broker issue per se - it is a
permissions problem. I'm hoping, though, that someone has had the experience
of running the service broker activated stored procedure in a database that
uses schemas for permissions management.
I couldn't post the actual source of my application here - for all kinds of
reasons - but I was able to create two scripts that demonstrate the
permissions problem without all the service broker infrastructure. Those
scripts are posted in the SQL Server Security forum here:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.security&p=1&tid=62334118-872d-49fa-9bb8-4efff114f9cf
Thanks,
--Craig.
Replies below ↓