I am posting a small demo at the end taht I hope will help you. Thanks a lot for your comments and feedback.
-Raul Garcia
SDE/T
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
-
/*******************************************************************
*
* This posting is provided "AS IS" with no warranties, and
* confers no rights.
*
* Author: Raulga
* Date: 08/24/2005
* Description:
* This demo shows how to use digital signatures to access
* resources on a different database by using digitaly signed stored
* procedures to control the access rather than using cross database
* ownership chaining.
*
* The first SP will be using the siganture as a secondary identity
* on top of the calling context. This means that only a context with
* a server-presence will succeed on this call (i.e. approles will not
* be able to accsss the resources on the target database as they
* don't have a server presence).
*
* The second approach will be by specifying a context switch
* (EXECUTE AS) on the stored procedure and using the signature as an
* authenticator; this means that the signature can vouch for the
* impersonated context (specifid on the module). This mechanism will
* allow to access the resources regardless of the original calling
* context because a new context (vouched by the signature) is placed
* on top of the orginal one, but requires more managment.
*
* (c) 2005 Microsoft Corporation. All rights reserved.
*
***********************************************************************************************/
CREATE DATABASE db_Source
go
CREATE DATABASE db_Target
go
CREATE LOGIN dbo_db_Source WITH PASSWORD = 'My S0uRc3 D8 p@.55W0rD!'
CREATE LOGIN dbo_db_Target WITH PASSWORD = 'My +@.r637 D8 p@.55W0rD!'
go
-- Change the ownership for the source and the target databases
ALTER AUTHORIZATION ON DATABASE::db_Source to dbo_db_Source
ALTER AUTHORIZATION ON DATABASE::db_Target to dbo_db_Target
go
-- This principal will be the data owner, he can access the data on
-- the target database, and he controls the stored procedures on the
-- source database
CREATE LOGIN data_owner WITH PASSWORD = 'd@.+4 0wn3R'
-- This principal should only have access to the data via the stored
-- procedures
CREATE LOGIN someuser WITH PASSWORD = 's0m3 p@.55w0Rd'
go
use db_Target
go
CREATE USER someuser
CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner
go
CREATE SCHEMA data_owner AUTHORIZATION data_owner
go
CREATE TABLE data_owner.MyTable( data nvarchar(100) )
go
INSERT INTO data_owner.MyTable values ( N'My data' )
go
use db_Source
go
CREATE USER someuser
CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner
go
CREATE SCHEMA data_owner AUTHORIZATION data_owner
go
-- ALlow someuser to execute any module on the schema called data_owner
GRANT EXECUTE ON SCHEMA::data_owner TO someuser
go
-- Create a stored procedure that uses the default execution context
-- (the caller's context) at runtime
CREATE PROC data_owner.sp_GetMyData01
AS
select * from db_Target.data_owner.MyTable
go
-- Create a stored procedure similar to teh previous one, but this time we will explicitly
-- use the data_owner context via EXECUTE AS
CREATE PROC data_owner.sp_GetMyData02
WITH EXECUTE AS 'data_owner'
AS
select * from db_Target.data_owner.MyTable
go
-
-- Let's see what is the behavior without any signatures
--
-- You can either start new connections or just use the
-- EXECUTE AS LOGIN & REVERT statements I show here for testing
-- Execute as the data owner
-
EXECUTE AS LOGIN = 'data_owner'
go
-- will succeed
EXEC data_owner.sp_GetMyData01
go
-- Will fail as the impersonated context is not trusted on the target
-- database
EXEC data_owner.sp_GetMyData02
go
REVERT
go
-
-- Execute as someuser
-
EXECUTE AS LOGIN = 'someuser'
go
-- will fail due to the lack of permissions on the target database
EXEC data_owner.sp_GetMyData01
-- will fail as the impersonated context is not trusted on the target
-- database
EXEC data_owner.sp_GetMyData02
go
REVERT
go
-
-- Signing the stored procedures
--
-- Create 2 certificates one to sign each SP.
-- Note that I am using passwords to protect the private keys.
-- It is also possible to use a DB master key to protect private the
-- keys, please refer to BOL for more information on the key
-- hierarchy
CREATE CERTIFICATE cert_GetMyData01
ENCRYPTION BY PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'
WITH SUBJECT = 'Certificate to sign sp_GetMyData01'
go
CREATE CERTIFICATE cert_GetMyData02
ENCRYPTION BY PASSWORD = 'GetMyData02 c3r+ P455W0Rd'
WITH SUBJECT = 'Certificate to sign sp_GetMyData02'
go
-- Now sign the stored procedures, as the cert's
-- private keys are protected by passwords, we have to use the
-- passwords to sign
ADD SIGNATURE TO data_owner.sp_GetMyData01 BY CERTIFICATE cert_GetMyData01
WITH PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'
go
ADD SIGNATURE TO data_owner.sp_GetMyData02 BY CERTIFICATE cert_GetMyData02
WITH PASSWORD = 'GetMyData02 c3r+ P455W0Rd'
go
-- Let's take a quick look to the metadata for the signed modules
SELECT schema_name( c.schema_id ) as schema_name, c.name,
b.name, a.crypt_property as 'module siganture' FROM
sys.crypt_properties a,
sys.certificates b,
sys.objects c
WHERE a.thumbprint = b.thumbprint AND a.class = 1
AND a.major_id = c.object_id
go
-- Depending on your application and environment, sometimes you may
-- not want to leave the private keys on the database, and either
-- destroy the private keys (this way, they can never be used to
-- sign anything else), or back up a copy of the private keys and
-- store them in a safe place. For this demo I will just destoy the
-- private keys as we don't need them anymore
ALTER CERTIFICATE cert_GetMyData01 REMOVE PRIVATE KEY
ALTER CERTIFICATE cert_GetMyData02 REMOVE PRIVATE KEY
go
-- Now, we need to create a backup for the certificate public data.
-- We will need to import it back on teh target database.
BACKUP CERTIFICATE cert_GetMyData01 TO FILE = 'cert_GetMyData01.cer'
BACKUP CERTIFICATE cert_GetMyData02 TO FILE = 'cert_GetMyData02.cer'
go
use db_Target
go
-- Import the certificates on the target database, note that we don't
-- need the private keys
CREATE CERTIFICATE cert_GetMyData01
FROM FILE = 'cert_GetMyData01.cer'
go
CREATE CERTIFICATE cert_GetMyData02
FROM FILE = 'cert_GetMyData02.cer'
go
-- Now let's create users mapped to each one of the certificates.
-- As permissions can only be granted to principals and not directly
-- to a certificate, we need to map the certificate to a user.
-- Note: The cert-mapped user SID is derived from teh certificate
-- thumbprint
-- therefore any 2+ principals (login or user in any database)
-- mapped to the
-- same certificate will have the same SID and will refer to the same
-- principal for practical purposes.
CREATE USER cert_GetMyData01 FOR CERTIFICATE cert_GetMyData01
go
CREATE USER cert_GetMyData02 FOR CERTIFICATE cert_GetMyData02
go
-- For the first SP, grant the permissions to the cert-mapped
-- user directly
GRANT SELECT ON data_owner.MyTable TO cert_GetMyData01
go
-- For the second SP, we want only AUTHENTICATE permissiion, this
-- will allow teh certificate to vouch for the context only on this
-- database.
-- Note: As the trust is only accross database and not accross the
-- instance, the new context is only valid for database operations,
-- and will not honor any server-scoped permissions.
GRANT AUTHENTICATE TO cert_GetMyData02
go
USE db_Source
go
-
-- Let's see what is the behavior without any signatures
--
-- You can either start new connections or just use the
-- EXECUTE AS LOGIN & REVERT statements I show here for testing
-- Execute as the data owner
-
EXECUTE AS LOGIN = 'data_owner'
go
-- will succeed
EXEC data_owner.sp_GetMyData01
go
-- will succeed as the module is executing as "data_owner"
-- (the module is specifying the context itself), and the
-- signature is vouching for this context
EXEC data_owner.sp_GetMyData02
go
REVERT
go
-
-- Execute as someuser
-
EXECUTE AS LOGIN = 'someuser'
go
-- will succed as the certificate will be granting the required
-- permission to select the data from the table
-- Note that someuser is a valid context accross the server at
-- this point
EXEC data_owner.sp_GetMyData01
-- will succeed as the module is executing as "data_owner"
-- (the module is specifying the context itself), and the
-- signature is vouching for this context
EXEC data_owner.sp_GetMyData02
go
REVERT
go
-
-- cleanup
USE master
go
DROP DATABASE db_Source
go
DROP DATABASE db_Target
go
DROP LOGIN dbo_db_Source
DROP LOGIN dbo_db_Target
DROP LOGIN data_owner
DROP LOGIN someuser
go
