sql server broken ownership chain

Broken ownership chain occurs when a user other than dbo trying to access the object from other schema(sc_b) which is authorized by another user from his own schema(sc_a)

In the below diagram, the user has the permission to execute a procedure on his own schema SC_A, but the procedure is calling a table which is in another schema where he doen’t have any permission and in fact the DBA don’t want to give the permission to any user to access the table sc_b.mytable directly except through stored procedures. If the user U1 executes the procedure pr_myproc then he will receive the below error
The SELECT permission was denied on the object 'mytable', database 'my_db', schema 's2'.

To avoid the broken ownership chain , we can use the signatures which are available from sql server 2005 to create a bridge between the calling object and objects in callstack.

· To create a certified_user , you need to have a master key , certificate , certified user

· create a signature on a caller object (sc_a.pr_myproc) procedure by a certificate

· grant the select permission on the objects in callstack (underlying table : sc_b.mytable) to the signed user(user created by certificate)

· and of course , you have to create a database master key to protect the certificates.

Lets simulate the broken ownership chain through the below script

--- Simulate broken ownership create database my_db GO use my_db create login l1 with password = 'm7yu@ik4#' create login l2 with password = 'm7yu@ik4#' create user u1 for login l1 create user u2 for login l2 ---------------------------------------------------------------------- create schema s1 authorization u1 create schema s2 authorization u2 ---------------------------------------------------------------------- create table s2.mytable(iid int) insert into s2.mytable select 1 ---------------------------------------------------------------------- GO CREATE  proc pr_myproc as select * from s2.mytable GO ---------------------------------------------------------------------- grant exec on pr_myproc to u1 grant exec on pr_myproc to u2 execute as user = 'u2' --- user-2 can access the table because the table belongs to u2 schema exec pr_myproc -- REVERT TO SYS LOGIN revert execute as user = 'u1' --- now the user u1 cant access the underlying table through the procedure due to broken ownership chain exec pr_myproc --- user cant access the table directly as well select * from s2.mytable go revert -- but we want to allow the user to access only through procedure , without giving direct permission ---- Create the objects required to bridge the connections ------------------------------------------------------------------------------------------------------- -- ** bridge the broken ownership chain using the certificate with signature on the object ** -- ------------------------------------------------------------------------------------------------------- -- create a master key , certificate , certified user create master key encryption by password = 'test' open master key decryption by password = 'test' backup master key to file = 'e:\mykey.key' encryption by password = 'passbkp' -- create a certificate CREATE CERTIFICATE mycertificate WITH SUBJECT = 'Test Certificate' backup CERTIFICATE mycertificate to file = 'e:\mycert.cert' -- create a certified user from the certificate CREATE USER CERT_USER FROM certificate mycertificate --------------------------------------------------------------------------- ------ Bridge beween calling object and the objects calling in stored procedure callstack --• create a signature on a caller object (sc_a.pr_myproc) procedure by a certificate ADD SIGNATURE TO pr_myproc BY CERTIFICATE mycertificate --•grant the select permission on the objects in callstack (underlying table : sc_b.mytable) -- to the signed user(user created by certificate) grant select on s2.mytable to CERT_USER -- test whether the user can execute the procedure with out any error execute as user = 'u1' exec pr_myproc -- now when the user is trying to access the table , directly then he cant. select * from s2.mytable go revert