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, 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

Comments (5)

  1.  Corrected a minor bug regarding the description of CDOC.

  2. raradhyula@hotmail.com says:

    Awesome article! I learned quite a lot from this article-

    A caller needs just execute permissions on a digitally signed stored procedure.

    If this stored procedure accesses a local database resource then a database user needs to be created FOR THE CERTIFICATE with necessary permissions on that resource. If this stored procedure accesses a server resource then a login needs to be created FOR THE CERTIFICATE with necessary permissions on that resource.

    If this stored procedure accesses another database resource then also a login needs to be created FOR THE CERTIFICATE and a database user on the remote database with necessary permissions on that resource.

    If this stored procedure accesses a server resource I guess I need to create a credential for the certificate login?

    Ravi A

  3.  Unfortunately this solution doesn’t work for accessing a remote DB (i.e. linked servers). The certificate as a secondary identity is scoped only for a given SQL Server instance. A workaround in this case could be using EXECUTE AS LOGIN (or equivalent) with linked servers.

    NOTE: SETUSER and approles are sandboxed to the current SQL Server instance and cannot be used with linked servers.

     -Raul Garcia

      SDE/T

      SQL Server Engine

  4. Ramjet says:

    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?

  5.  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

Skip to main content