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

clip_image001[6]

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.

clip_image004

 

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

Post a Comment