Using a digital signature as a secondary identity to replace Cross database ownership chaining

  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, https://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 (https://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

Comments

• Anonymous
  March 18, 2008
   Corrected a minor bug regarding the description of CDOC.

• Anonymous
  April 18, 2008
  The comment has been removed

• Anonymous
  April 21, 2008
  The comment has been removed

• Anonymous
  November 10, 2010
  Forgive me I am just learning this security model. Why do we need the sp twice?? Once in each db? Shouldn't web user be able to exec sp in source. SP in source is signed with certificate and thus has access to any object with same signature? So SP in source can do insert in target because of the cert matching up? As a seperate question why do we have to create the cert on both dbases once from a file. I get why as they need to have same sig but why wouldn't Msoft allow us to create a cert at the server level and then we can sign whatever objects we want at the dbase level?

• Anonymous
  April 06, 2011
   Strictly speaking you are right, the SP only needs to exist on the source DB, which is trying to access the data from the target DB.  The idea behind having two copies of the SP in the demo was to simulate the following scenario: • Alice is the DBO for target DB is the one creating the original SP in his own DB and the one with control over the private key. • In order to give the SP to any other DB (or to ship the stored procedure as an ISV), Alice gets the certificate to a file & the signature for the SP she signed (i.e. the SP body is under her control). NOTE: Alice can drop the SP from the target DB at this point since she is not going to use it. • Alice can give the SP definition, the signature & the certificate file to Bob, who is the DBO for the source DB. Using this data, Bob can recreate the SP and sign it on his DB. • Bob doesn’t have the private key at all, so he cannot modify the SP  Hopefully this explanation will clarify the reason why on the demo I created the SP twice. Regarding the need to have a copy of the certificate in the database, the reason behind it is really a consequence of our design. In order to keep things simple, we defined that certificates were DB objects, same as SPs, therefore all permissions on the certificate (including private key) are on a database scope. We didn’t originally designed the module signature to address cross-DB scenarios, but the model we defined allowed for a simple approach that satisfied the most common case (same-DB access), and allowed for cross-DB scenarios with relative ease (copying the certificate).  -Raul Garcia