--The first few steps here set up the databases and the logins and the tables that contain the data to be protected
--Just to make things interesting we also have the two databases owned by different logins.
--We'll also create a login for the regular end user.

-- Clean up
--USE master
--go
--DROP DATABASE feedstore_callingDB
--DROP DATABASE feedstore_targetDB
--DROP LOGIN feedstore_exec_as_principal 
--DROP LOGIN feedstore_callingDB_owner 
--DROP LOGIN feedstore_targetDB_owner 
--DROP LOGIN end_user
--go

use master
create database feedstore_callingDB
go
create database feedstore_targetDB
go
create login feedstore_exec_as_principal with password = 'Yukon90'
go
use feedstore_targetDB
go
create table Secret_table (c1 int)
go
insert into Secret_table values (10)
insert into Secret_table values (10)

use master
create login feedstore_callingDB_owner with password = 'Yukon90'
create login feedstore_targetDB_owner with password = 'Yukon90'

use feedstore_callingDB
exec sp_changedbowner 'feedstore_callingDB_owner'

use feedstore_targetDB
exec sp_changedbowner 'feedstore_targetDB_owner'

use master
create login end_user with password = 'Yukon90'
use feedstore_callingDB
create user end_user
exec sp_helpuser


--********************************************************************************************************************
--ok, so now you have your secret table in the target database and
--  you want to have end users access this table but
--only through a stored proc in the source database. 
--As I said there are different options here:
--Option 1: Use execute as (But since by default this works only within 
							--the database you need one of the following two
-- suboptions to make the target database trust the source database)
------Option 1.a: Use Execute as and trust the DBO of the source database to expand scope to another database.
------Option 1.b: USe Execute as and trust the certificate that the SP was signed with.
--Option 2: Sign the SP and do not use execute as

--Lets develop each of these options one by one...
--********************************************************************************************************************
--**OPTION 1.a (Execute as and trusting the source database's DBO)
--********************************************************************************************************************
--Lets create the user in this database that we want to execute as. 
use feedstore_callingDB
go
Create user feedstore_exec_as_principal
go
--Lets create the proc to select from the target table
create proc data_proc
with execute as 'feedstore_exec_as_principal'
as
select * from feedstore_targetDB.dbo.secret_table
go
--lets ensure that this execute_as user has permissions on the table.
use feedstore_targetDB
create user feedstore_exec_as_principal
go
grant select on secret_table to feedstore_exec_as_principal
go
--Now just to prove that execute as is scoped to the database by default, lets try and call this proc. 
--You will notice that it fails with a permission error, because the context is not carried forward
use feedstore_callingDB
exec data_proc
go
--So now to make things work by trusting the source database's DBO. This is done as follows
--Step 1:
--Create a user for the calling Database's DBO in the target database. 
--Notice that if the calling database and the target database is owned by the same login, this step is not needed
use feedstore_targetDB
create user feedstore_callingDB_owner
go
--Step2:
--To trust the calling database owner, grant him the following permission
--Again remember that if the calling database and the target database are owned by the same login then since the DBOs
--are the same and DBOs have all permissions by default in the database, this step can be skipped.
grant authenticate to feedstore_callingDB_owner

--But wait, there is one more step. So far we have established that the target database trusts the DBO of the calling
--database. But the DBO of the calling database might want to control which one of the databases that he owns (he
--could own multiple) is actually trusted to do this. This is done by setting a property on the calling database. This
--property is called TRUSTWORTHY
use feedstore_callingDB
alter database feedstore_callingDB set TRUSTWORTHY ON
go

--Now you are all set. try that call again and it works.
--But lets try and call it as the end user. So simulate connecting in as end_user cand call the SP. But before that
--grant permissions to this user to execute the SP
grant execute on data_proc to end_user
EXECUTE AS LOGIN = 'end_user'
exec data_proc
select user_name()
revert---simulates logout.
--*******************************************************************************************************************
--**Option 1.b (Execute as, but with signing to establish cross database trust)
--*******************************************************************************************************************
--Lets first undo the trust establishment that we performed using the first option.
ALTER database feedstore_callingDB set TRUSTWORTHY OFF
use feedstore_targetDB
revoke authenticate from feedstore_callingDB_owner
drop user feedstore_callingDB_owner
--To check that the above undo was performed successfully. Lets run the proc now. We expect it to fail, since
--currently there is no trust establishment
use feedstore_callingDB
exec data_proc
--Notice that it failed. Now we are ready to showcase the second option for trust establishment
--Since this is done through signing the proc, we need to first create a certificate that will be used to sign
--But before we can create a certificate we first need to create a database master key.
--This database master key will be used to encrypt the private keys of the certificate.
--You can do these steps in the feedstore_callingDB database or the master database
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Feedstore_callingDB!'
go
Create certificate Feedstore_cert1
with subject = 'FeedStore Certificate'
go

--Now that we have created the certificate let us sign the procedure using that certificate.
ADD SIGNATURE TO data_proc BY CERTIFICATE Feedstore_cert1

--So we have signed the proc. Now to trust it on the target side. This is done by granting the certificate the
--authenticate permission in the target database. Actually we map a user to the certificate and grant that user the
--authenticate permission.
--But to do that we have to get the same certificate to the other database. Because in Yukon we do not allow you to
--create a user in one database that is mapped to a certificate in another database.
--Since in Yukon we also dont allow you do transfer certificates from one database to another directly, you will 
--have to first dump the certificate to a file and then load it in the target database.

--NOTE: Please alter the file path below to match something that you have on your system

BACKUP CERTIFICATE Feedstore_cert1 to FILE = 'C:\SQLTemp\feedstore_cert2.cer'

use feedstore_targetDB
go
--DROP CERTIFICATE Feedstore_cert1 
CREATE CERTIFICATE Feedstore_cert1 FROM FILE = 'C:\SQLTemp\feedstore_cert2.cer'
go
--Now to map a user to this certificate
CREATE USER feedstore_cert1_mapped_user FOR CERTIFICATE Feedstore_cert1
--Now grant this user authenticate permissions
GRANT AUTHENTICATE to feedstore_cert1_mapped_user

--Now things should be working. Again simulate connecting in as end_user
use feedstore_callingDB
go
EXECUTE AS LOGIN = 'end_user'
exec data_proc
revert---simulates logout.

--********************************************************************************************************************
--**Option 2: Dont even use execute as. Just sign the module and use the signature to give you the permissions on the 
--**underlying table
--********************************************************************************************************************
--First let us alter the proc to remove the execute as.
ALTER PROC data_proc
as 
select * from feedstore_targetDB.dbo.secret_table
go
--Now that the proc has changed we have to sign it again. Lets use the same certificate we used before
ADD SIGNATURE TO data_proc BY CERTIFICATE Feedstore_cert1

--Now let us switch to the target database and remove the authenticate permission. We dont need it as we are not using
--EXECUTE AS.
--But since we are relying on the signed module itself giving us the permission we need to grant the user mapped to the
--certificate, select permissions on the table instead of the feedstore_exec_as_principal user. WE can in fact remove
--the feedstore_exec_as_principal user since we are not using execute as anymore. So lets do that.
use feedstore_targetDB
revoke select on secret_table from feedstore_exec_as_principal
drop user feedstore_exec_as_principal
revoke authenticate from feedstore_cert1_mapped_user
grant select on secret_table to feedstore_cert1_mapped_user

--Now with that done, things should work using the signed module approach. Again simulate connecting as end_user. 
--But before that grant permissions to execute the proc
use feedstore_callingDB
--grant execute on data_proc to end_user
EXECUTE AS LOGIN = 'end_user'
exec data_proc
revert---simulates logout.

