In SQL Server 2000, Cross database ownership chaining (CDOC) was a mechanism used to allow access (DML access) to resources on different DBs without explicitly granting access to the resources (such as tables) directly.

 

  Unfortunately CDOC is a feature that Microsoft does not recommend as it has some serious security risks inherent to the feature (for details on this topic, you can consult BOL, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4iyb.asp).

 

   Fortunately in SQL Server 2005 we count with 2 alternative mechanisms that will enable cross-database resource usage without explicitly granting access to either the database or to the resources directly to each principal. These mechanisms are EXECUTE AS feature and using digital signatures.

 

  Some of the best references on how to use these mechanisms can be found in BOL (Extending Database Impersonation by Using Execute As) and in

Laurentiu Cristofor’s blog (http://blogs.msdn.com/lcris/).

 

 On this demo, I want to extend these materials with a demo that relies only on digital signatures as secondary identity (i.e. no authenticator involved).

 

  While this approach has some advantages, including:

·         No need to create/re-use a login for the application

·         Works as a CDOC replacement

o   In addition, works with dynamic SQL

·         Denied permissions on the caller will be honored

·         Easier to script for ISV applications (i.e. the signature can be precalculated)

·         Can be easily adapted for either DB or server scoped permissions

 

  As any other security feature, this approach also has some limitations you should consider before deploying:

·         It doesn’t work if the calling context is a DB-scoped context (i.e. approles, EXECUTE AS USER).

·         Doesn’t work if the operation requires creating an object or use the calling’s primary identity.

o   Do not rely on implicit user creation!

·         Be extra careful when using dynamic SQL as SQL injection attacks can abuse the escalated privileges.

·         Need to write the certificate to disk (at least temporarily) in order to create the certificate in 2 or more databases.

·         If you require calling nested modules, each one of the nested modules should be signed or counter signed as well.

 

Demo

/*******************************************************************

*

*   This posting is provided "AS IS" with no warranties, and

* confers no rights.

*

* Author:   Raulga

* Date:     10/30/2006

*

*     (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 [AppUser] WITH PASSWORD = 's0m3 p@55w0Rd'

go

 

-----------------------------------------

-- Setting up the target DB

--

use [db_Target]

go

 

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

 

-----------------------------------------

-- Setting up the source DB

--

use [db_Source]

go

 

-- The low privielged user is only required here

CREATE USER [AppUser]

go

 

-- Create an application that uses the table stored in db_Target

-- I will use a specific schema for all the application modules

--

CREATE SCHEMA [schema_MyApp]

go

 

GRANT EXECUTE ON SCHEMA::[schema_MyApp] TO [AppUser]

go

 

-- Remember that sigantures are sensitive to comments and white spaces

--

go

CREATE PROC [schema_MyApp].[sp_MyApp01] ( @new_data nvarchar(100) )

AS

  -- Print the user token on this DB

  SELECT * FROM sys.user_token

  -- Print the user token on the target DB

  SELECT * FROM [db_Target].sys.user_token

  -- Insert data on the Cross-DB table

  INSERT INTO [db_Target].[data_owner].[MyTable] VALUES (@new_data)

go

 

-----------------------------------------------------

-- Test the application as the low privielged user,

EXECUTE AS LOGIN = 'AppUser'

go

-- The call should fail teh moment it tries to access db_Target

EXEC [schema_MyApp].[sp_MyApp01] N'Test data'

go

-- revert to original context

REVERT

go

 

-----------------------------------------------------

-- Now, let's play the role of db_target DBO

--

USE [db_Target]

go

 

EXECUTE AS LOGIN = 'dbo_db_Target'

go

 

-- Create our signing certificate

CREATE CERTIFICATE [cert_MyAppSecIdentity]

    ENCRYPTION BY PASSWORD = 'S16n1n6 c3r+1f1C@+3'

      WITH SUBJECT = 'myApp siging certificate'

go

 

-- Re-create the Proc exactly as it was created in the source DB

-- including comments and blank characters

CREATE SCHEMA [schema_MyApp]

go

CREATE PROC [schema_MyApp].[sp_MyApp01] ( @new_data nvarchar(100) )

AS

  -- Print the user token on this DB

  SELECT * FROM sys.user_token

  -- Print the user token on the target DB

  SELECT * FROM [db_Target].sys.user_token

  -- Insert data on the Cross-DB table

  INSERT INTO [db_Target].[data_owner].[MyTable] VALUES (@new_data)

go

 

-- And add the siganture

ADD SIGNATURE TO [schema_MyApp].[sp_MyApp01] BY CERTIFICATE [cert_MyAppSecIdentity]

       WITH PASSWORD = 'S16n1n6 c3r+1f1C@+3'

go

 

BACKUP CERTIFICATE [cert_MyAppSecIdentity] TO FILE = 'cert_MyAppSecIdentity.cer'

go

 

-- obtain the pre-calculated signature that can be applied to the module in db_Source

DECLARE @signature varbinary(max)

SELECT @signature = crypt_property FROM sys.crypt_properties WHERE major_id = object_id('[schema_MyApp].[sp_MyApp01]')

PRINT @signature

go

-- In my case the siganture value was:

-- 0x5EF9C30476A8E3E248E9E11B7563528EB02DA1D8F440CAA9141841B9F3101F1988760D2775000CD0D70F44A8672984E327FBF2676E7FAC9AAED8E6F383A98B2A569A407577917E671F3D632EF7326AD3770A32E05CF43A613D310D64B6D52FD978E57A73912BF3587C475E48F4AA58561A7E0DB5D9DB53D35E03EC281BEC7772

 

-- Let's create a user for teh certifcate so we can use it as a secondary identity

CREATE USER [cert_MyAppSecIdentity] FOR CERTIFICATE [cert_MyAppSecIdentity]

go

 

-- And grant the right permission to it, in thsi case INSERT on teh table would be sufficient

GRANT INSERT ON [data_owner].[MyTable] TO [cert_MyAppSecIdentity]

go

 

-- Let's look at the permissions for the certificate-mapped user:

SELECT * FROM sys.database_permissions WHERE [grantee_principal_id] = user_id( 'cert_MyAppSecIdentity' )

--... notice that in addition to INSERT on our table, this user also has CONNECT permission on the database

go

 

-- revert to original context

REVERT

go

 

-----------------------------------------------------

-- Now, let's play the role of db_Source DBO

--

USE [db_Source]

go

 

EXECUTE AS LOGIN = 'dbo_db_Source'

go

 

-- Let's create a copy of teh certifcate on this DB

CREATE CERTIFICATE [cert_MyAppSecIdentity] FROM FILE = 'cert_MyAppSecIdentity.cer'

go

 

-- Now use teh pre-calculated siganture to sign the app

-- Notice that the Source DB dbo doesn't have any access to trhe private key

-- therefore, she cannot modify the SP body

--

ADD SIGNATURE TO [schema_MyApp].[sp_MyApp01] BY CERTIFICATE [cert_MyAppSecIdentity]

  WITH SIGNATURE = 0x5EF9C30476A8E3E248E9E11B7563528EB02DA1D8F440CAA9141841B9F3101F1988760D2775000CD0D70F44A8672984E327FBF2676E7FAC9AAED8E6F383A98B2A569A407577917E671F3D632EF7326AD3770A32E05CF43A613D310D64B6D52FD978E57A73912BF3587C475E48F4AA58561A7E0DB5D9DB53D35E03EC281BEC7772

go

 

-- revert to original context

REVERT

go

 

-----------------------------------------------------

-- Let's test the application as the low privielged user once more

EXECUTE AS LOGIN = 'AppUser'

go

-- The call should succeed!!!

EXEC [schema_MyApp].[sp_MyApp01] N'Test data'

go

-- revert to original context

REVERT

go

 

--0   NULL  public      ROLE  GRANT OR DENY

--6   0x0106000000000009010000002A1A61C7FF8883632259BFA45D0493B234FDD3C1      cert_MyAppSecIdentity   USER MAPPED TO CERTIFICATE    GRANT OR DENY

 

-- Verify that the insert succeeded

SELECT * FROM [db_Target].[data_owner].[MyTable]

go

 

-----------------------------------------------------

-- 2nd part

-- Using dynamic SQL with access via siganture

-----------------------------------------------------

--

USE db_Source

go

 

EXECUTE AS LOGIN = 'dbo_db_Source'

go

 

-- Let's create a simple module that will execute a select & a simpel dynamic SQL code

CREATE PROC [schema_MyApp].[sp_MyApp02] ( @new_data nvarchar(100) )

AS

  -- SELECT from teh table

  SELECT * FROM [db_Target].[data_owner].[MyTable]

  -- Using dynamic SQL for demonstration purposes only

  EXEC( 'use db_target; SELECT * FROM sys.user_token; SELECT user_name();' )

go

 

REVERT

go

 

--------------------------------------------

-- Now let's create the siganture for the previous module

USE [db_Target]

go

 

EXECUTE AS LOGIN = 'dbo_db_Target'

go

CREATE PROC [schema_MyApp].[sp_MyApp02] ( @new_data nvarchar(100) )

AS

  -- SELECT from teh table

  SELECT * FROM [db_Target].[data_owner].[MyTable]

  -- Using dynamic SQL for demonstration purposes only

  EXEC( 'use db_target; SELECT * FROM sys.user_token; SELECT user_name();' )

go

 

ADD SIGNATURE TO [schema_MyApp].[sp_MyApp02] BY CERTIFICATE [cert_MyAppSecIdentity]

  WITH PASSWORD = 'S16n1n6 c3r+1f1C@+3'

go

 

-- We need SELECT permission to succeed on the SP

GRANT SELECT ON SCHEMA::[data_owner] TO [cert_MyAppSecIdentity]

go

 

-- same step as before

DECLARE @signature varbinary(max)

SELECT @signature = crypt_property FROM sys.crypt_properties WHERE major_id = object_id('[schema_MyApp].[sp_MyApp02]')

PRINT @signature

go

-- 0x16A91194689EB9D07FB1DEB5526B1216126D79DF00B4C74CDC5D86CA94DF81732DB001C504DC7C361A3F4FC45214DA9A6484A085CDC1679E7C5D23EB0C2ADD9F118C26B20B3853CB8D329591E100BA742EFA5E47985623C8D0CF9BAE80AC488B09B42386010F079319FA241012A73BFD2E3BC214D527398B12EAB22316FC4A59

 

REVERT

go

 

------------------------------

USE db_Source

go

 

ADD SIGNATURE TO [schema_MyApp].[sp_MyApp02] BY CERTIFICATE [cert_MyAppSecIdentity]

   WITH SIGNATURE = 0x16A91194689EB9D07FB1DEB5526B1216126D79DF00B4C74CDC5D86CA94DF81732DB001C504DC7C361A3F4FC45214DA9A6484A085CDC1679E7C5D23EB0C2ADD9F118C26B20B3853CB8D329591E100BA742EFA5E47985623C8D0CF9BAE80AC488B09B42386010F079319FA241012A73BFD2E3BC214D527398B12EAB22316FC4A59

go

 

-- Let's test the application as the low privielged user

EXECUTE AS LOGIN = 'AppUser'

go

-- The SELECT call should succeed!!!

EXEC [schema_MyApp].[sp_MyApp02] N'Test data'

go

-- Notice that the token inside the dynamic SQL also contains the certificate user as a secondary identity

-- Additionally, look at the result from user_name()!

-- The reason why it shows AppUser is because the access to teh DB is via a secondary identity, similar to the

-- case when access to a database is granted via a Windows group.

 

-- revert to original context

REVERT

go

 

-----------------------------------------------------

-- 3rd part

-- Honoring denied permissions

-----------------------------------------------------

--

CREATE LOGIN [dbTarget_DenyReader] WITH PASSWORD = '53cr3+ p@55WoRd!'

go

 

USE [db_Target]

go

 

CREATE USER [dbTarget_DenyReader]

go

 

-- This user cannot SELECT from [data_owner] schema

DENY SELECT ON SCHEMA::[data_owner] TO [dbTarget_DenyReader]

go

 

USE [db_Source]

go

 

-- But it is a valid, maybe even privielged user on db_Source

CREATE USER [dbTarget_DenyReader]

go

EXEC sp_addrolemember 'db_owner', 'dbTarget_DenyReader'

go

 

-- Can dbTarget_DenyReader use the application we created?

EXECUTE AS LOGIN = 'dbTarget_DenyReader'

go

 

-- This call will succeed, after all INSERT permission via teh certificate is still valid

-- and no explicit denied permission for INSERT

EXEC [schema_MyApp].[sp_MyApp01] N'Test data as deny reader'

go

-- Notice that on the user token for db_Target this time we can see "dbTarget_DenyReader"

-- The reason is that this time we are not accessing teh db_Target based on teh secondary identity

--  as dbTarget_DenyReader is a valid user on it, we are just extending the existing permissions.

--

-- Let's try the 2nd SP now...

EXEC [schema_MyApp].[sp_MyApp02] N'Test data as deny reader'

go

-- SELECT on [db_Target].[data_owner].[MyTable] failed,

-- but the rest of the module executed as we expected, you can see

-- that the certificate is still aprt of the token on the dynamic SQL call.

 

-- revert to original context

REVERT

go