Replace Ownership Chaining in SQL Server!

In my previous post 'Mitigating Double Hop in SQL Server', I have explained about the double hop issue and the resolution within the same database. Double hop can occur in different databases, which is called 'Cross Database Ownership Chaining'. In SQL Server 2000 Cross Database Ownership Chaining was a mechanism to allow DML access to other database resources such as table without explicitly granting the permission of access to the user.

Unfortunately, Microsoft does not recommend to use this mechanism in SQL Server 2000 due to security risks.(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4iyb.asp). Fortunately, in SQL Server 2005 and later, DBAs are able to resolve this matter with two different ways which is first, EXECUTE AS command and second, digital signatures.

While this approach has some advantages, including:

1. No need to create/re-use a login for the application
2. Works as a CDOC replacement, in addition, works with dynamic SQL
3. Denied permissions on the caller will be honored
4. Easier to script for ISV applications (i.e. the signature can be precalculated)
5. 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:

1. Doesn’t work if the operation requires creating an object or use the calling’s primary identity. Do not rely on implicit user creation!
2. Be extra careful when using dynamic SQL as SQL injection attacks can abuse the escalated privileges.
3. Need to write the certificate to disk (at least temporarily) in order to create the certificate in 2 or more databases.
4. If you require calling nested modules, each one of the nested modules should be signed or counter signed as well.

Scenario: John has permission to execute SP_A in DB_A, which as below figure shows, SP_A retrieves data from table dbo.TA in DB_B, which John does not have "Select" permission on dbo.TA table in DB_B.

Solution: The above scenario is same as multiple hops, the only different is the one of the resources is in the different database and the user does not have permission on that specific resource. To resolve this issue, administrator needs to create a certificate and sign the specific stored procedure and then remove the private key, after that, administrator needs to back up the certificate and restore it in the destination server. At the final step, he/she needs to create a user from the certificate and assign proper permission to it and needs to make sure that the Trustworthy option of both databases are enabled.

Below codes resolve the ownership chaining issue for mentioned scenario.