SQL Server 2005: Demo for enabling database impersonation for cross database access

There is an excellent article on this topic in Books Online: Extending Database Impersonation by Using Execute As. I just wrote a small demo to illustrate the techniques described in that article. It can be used as a companion to that article, if you're looking for a more hands-on approach to learning about this subject. The demo is quite long and I have plenty of comments describing what is going on, but I'll include a short description as well: We have two database owners, Alice and Bob. Charles is a third principal that has access to some objects in Bob's database. We're looking at methods that allow (and restrict) how Alice can impersonate Charles inside her database and how she can then attempt to access the objects from Bob's database. The demo shows how the TRUSTWORTHY bit can be used and then it shows an alternative method of using a certificate as an authenticator. For the latter method, I also showed how Bob can allow Alice to sign a procedure without granting her access to the private key of the certificate used for signing. So here's the demo:

-- A demo for cross database access
--

-- create our principals: alice, bob, charles
--
create login alice with password = 'SimplePwd01'
create login bob with password = 'SimplePwd01'
create login charles with password = 'SimplePwd01'

-- create two databases owned by alice and bob
--
create database db_alice
create database db_bob

alter authorization on database::db_alice to alice
alter authorization on database::db_bob to bob

use db_bob

-- execute the following as bob, this database's dbo
--
execute as login = 'bob'

-- add charles to bob's database
--
create user charles

-- create a table and a procedure accessing it,
-- both owned by charles
--
create table t (c int)
insert into t values (42)

alter authorization on t to charles

create procedure proc_select_t as
begin
    select * from sys.login_token
    select db_name()
    select * from sys.user_token

    print 'Selecting from t...'
    select * from t
end

alter authorization on proc_select_t to charles

create procedure proc_drop_t as
begin
    select * from sys.login_token
    select db_name()
    select * from sys.user_token

    print 'Dropping t...'
    drop table t
end

alter authorization on proc_drop_t to charles

-- verify the selection procedure
--
execute as user = 'charles'

exec proc_select_t

-- revert impersonation of charles
--
revert

-- revert impersonation of bob
--
revert

use db_alice

-- execute the following as alice, this database's dbo
--
execute as login = 'alice'

-- add charles to alice's database
--
create user charles

-- note that alice cannot impersonate charles at server level
-- the following call will produce an ERROR!
--
execute as login = 'charles'

-- however, being the dbo, she can impersonate charles at database level
--
execute as user = 'charles'

select * from sys.login_token
select * from sys.user_token

-- create a procedure for cross database access
--
go
create procedure proc_crossdb
  with execute as 'charles'
as
begin
    select * from sys.login_token
    select db_name()
    select * from sys.user_token

    exec db_bob.dbo.proc_select_t
end
go

-- call the procedure, to impersonate charles and access bob's database
--
exec dbo.proc_crossdb
--
-- won't work - database is not trustworthy
-- user token is "sandboxed"

-- revert the impersonation of alice
--
revert

-- let's mark alice's database as trustworthy
--
alter database db_alice set trustworthy on

-- now let's see if this changes anything
--
execute as login = 'alice'

exec dbo.proc_crossdb
--
-- we now see alice as an authenticator,
-- but bob doesn't trust alice to authenticate,
-- so it still doesn't work

-- let's make bob trust alice to authenticate
--
use db_bob

execute as login = 'bob'

create user alice

grant authenticate to alice

-- let's see how things work now
--
use db_alice

execute as login = 'alice'

exec dbo.proc_crossdb
--
-- the token authenticated by alice is now accepted

-- first, let's remove the trustworthy setting on alice's database
-- and bob's trust in alice as an authenticator
--
alter database db_alice set trustworthy off

use db_bob

execute as login = 'bob'

-- remove alice, which will drop any permissions as well
--
drop user alice

-- now we'll use signing to enable cross database access
-- we'll create a certificate and a principal mapped to it
-- that will be used as authenticator
--
create certificate cert_sign_alice
  encryption by password = 'SimplePwd01'
  with subject = 'Sign procedures in Alice''s database'

create user u_cert_sign_alice from certificate cert_sign_alice

grant authenticate to u_cert_sign_alice

-- we create and sign the proc_crossdb procedure in bob's database,
-- because we don't want to give the private key of the certificate to alice
--
go
create procedure proc_crossdb
  with execute as 'charles'
as
begin
    select * from sys.login_token
    select db_name()
    select * from sys.user_token

    exec db_bob.dbo.proc_select_t
end
go

add signature to proc_crossdb by certificate cert_sign_alice with password = 'SimplePwd01'

-- retrieve the signature from the catalogs
--
select crypt_property from sys.crypt_properties where major_id = object_id('proc_crossdb')
--
-- the value returned will change because the certificate will be generated randomly
-- we need to copy the returned value
-- for my execution, I got back:
-- 0x17B803D0550C450AD4D815CB2CEB730E941E2F8BD41B6848B2546D0657E7F85FEDA69FF9048F358AECCBE0B7E9B4AC3F7420513AB539E6B87C8E638FB9AF2F557A3CB389D4ECA72DE1C34523AEDF48E2AB290AA94EC496CBAF527D3D0B95B7395DD7A77ED06A0894102C653DFC6425A317DD383B4F79C8AF8A7A5A62E5AA1899

-- backup the public key of the certificate to a file alice can access
-- remember that the private key is needed for signing and the public key
-- is only used for verification, hence alice won't be able to arbitrarily sign code
--
backup certificate cert_sign_alice to file = 'cert_sign_alice.cer'
--
-- if you rerun this script, you'll get an error if you already created the file
-- you can either change the name of the file or drop the old file

revert
--
-- we're done, now alice just has to take the signature and the certificate
-- and apply the signature to the exact same procedure - she cannot change the code
-- or the signature won't work
-- bob can provide all these (procedure, certificate, and signature) to alice

use db_alice

execute as login = 'alice'

create certificate cert_sign_alice from file = 'cert_sign_alice.cer'

create user u_cert_sign_alice from certificate cert_sign_alice

add signature to proc_crossdb by certificate cert_sign_alice
  with signature = 0x17B803D0550C450AD4D815CB2CEB730E941E2F8BD41B6848B2546D0657E7F85FEDA69FF9048F358AECCBE0B7E9B4AC3F7420513AB539E6B87C8E638FB9AF2F557A3CB389D4ECA72DE1C34523AEDF48E2AB290AA94EC496CBAF527D3D0B95B7395DD7A77ED06A0894102C653DFC6425A317DD383B4F79C8AF8A7A5A62E5AA1899

-- note that the certificate will appear as authenticator,
-- which will enable the context to be trusted in bob's database,
-- allowing the call to go through
--
exec proc_crossdb

-- Let's see what happens if alice attempts to do something different in the procedure,
-- such as attempting to drop the table
--
alter procedure proc_crossdb
  with execute as 'charles'
as
begin
    select * from sys.login_token
    select db_name()
    select * from sys.user_token

    exec db_bob.dbo.proc_drop_t
end

-- won't work because impersonated context is not authenticated
--
exec proc_crossdb

-- let's try to add the signature
--
add signature to proc_crossdb by certificate cert_sign_alice
  with signature = 0x17B803D0550C450AD4D815CB2CEB730E941E2F8BD41B6848B2546D0657E7F85FEDA69FF9048F358AECCBE0B7E9B4AC3F7420513AB539E6B87C8E638FB9AF2F557A3CB389D4ECA72DE1C34523AEDF48E2AB290AA94EC496CBAF527D3D0B95B7395DD7A77ED06A0894102C653DFC6425A317DD383B4F79C8AF8A7A5A62E5AA1899
--
-- it won't work because signature is not valid
-- alice's attempt has failed - she can only use the signature
-- with the code provided by bob

revert

use master

drop database db_alice
drop database db_bob

drop login alice
drop login bob
drop login charles
--
-- EOD