Hi,

I'm testing the use of certificates as a way of controlling database access.

I've created a stored procedure in one databases (testsource) that accesses a view in another database (testtarget1) which in turn accesses a table in a third database (testtarget2).

I've created a certificate in Testsource and signed the procedure with it, I've not created a certificate user in testsource.

I've imported the certificate into TestTarget1 and TestTarget2 and created a certificate user with the necessary permissions.

When I execute the stored procedure in Testsource as a user with only access to this one database it returns the results successfully, meaning that when the view access a table in another database the permissions of the certificate user still applies in that database.

This behaviour is different to how a stored procedure works where it "loses" the certificate permissions between the jumps unless it has been signed.

Is this behaviour as regards views as expected and can it be relied on ? I've not seen this referred to in any think that I've read about certificates, maybe it is so obvious I shouldn't be suprised :-). Does anyone know of a book\ blog that goes into depth about the workings of certificates and certificate users ?

I've included the scripts I used to test this in case anyone is interested.

/*
testing certificate user permissions when accessing a view
*/

--******************************set up start*****************************
CREATE DATABASE TestSource
go

USE TestSource

CREATE CERTIFICATE TestCert1
   ENCRYPTION BY PASSWORD = 'TestCert_01'
   WITH SUBJECT = 'test certificate',
   START_DATE = '20130508', EXPIRY_DATE = '20490101'
go
-- Save the certificate to disk.
BACKUP CERTIFICATE TestCert1 TO FILE = 'c:\CertBackup\TestCert1.cer'
WITH PRIVATE KEY (FILE = 'c:\CertBackup\TestCert1.pvk' ,
                  ENCRYPTION BY PASSWORD = 'TestCert_01',
                  DECRYPTION BY PASSWORD = 'TestCert_01')
go
-- create test user
USE TestSource
CREATE LOGIN [MyTestUser] WITH PASSWORD=N'MyTestUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [MyTestUser] FROM LOGIN [MyTestUser]
GRANT EXECUTE TO [MyTestUser]
go

CREATE DATABASE [testtarget1]
go
USE [testtarget1]
CREATE CERTIFICATE TestCert1 FROM FILE = 'c:\CertBackup\TestCert1.cer'
WITH PRIVATE KEY (FILE ='c:\CertBackup\TestCert1.pvk' ,
                  DECRYPTION BY PASSWORD = 'TestCert_01',
                  ENCRYPTION BY PASSWORD = 'TestCert_01')

CREATE USER TestCert1User FROM CERTIFICATE TestCert1
EXEC sp_addrolemember 'db_owner','TestCert1User'

go
CREATE DATABASE [testtarget2]
go 
USE testtarget2	
CREATE CERTIFICATE TestCert1 FROM FILE = 'c:\CertBackup\TestCert1.cer'
WITH PRIVATE KEY (FILE ='c:\CertBackup\TestCert1.pvk' ,
                  DECRYPTION BY PASSWORD = 'TestCert_01',
                  ENCRYPTION BY PASSWORD = 'TestCert_01')

go		

Hi,

I'm testing the use of certificates as a way of controlling database access.

I've created a stored procedure in one databases (testsource) that accesses a view in another database (testtarget1) which in turn accesses a table in a third database (testtarget2).

I've created a certificate in Testsource and signed the procedure with it, I've not created a certificate user in testsource.

I've imported the certificate into TestTarget1 and TestTarget2 and created a certificate user with the necessary permissions.

When I execute the stored procedure in Testsource as a user with only access to this one database it returns the results successfully, meaning that when the view access a table in another database the permissions of the certificate user still applies in that database.

This behaviour is different to how a stored procedure works where it "loses" the certificate permissions between the jumps unless it has been signed.

Is this behaviour as regards views as expected and can it be relied on ? I've not seen this referred to in any think that I've read about certificates, maybe it is so obvious I shouldn't be suprised :-). Does anyone know of a book\ blog that goes into depth about the workings of certificates and certificate users ?

I've included the scripts I used to test this in case anyone is interested.

/*
testing certificate user permissions when accessing a view
*/

--******************************set up start*****************************
CREATE DATABASE TestSource
go

USE TestSource

CREATE CERTIFICATE TestCert1
   ENCRYPTION BY PASSWORD = 'TestCert_01'
   WITH SUBJECT = 'test certificate',
   START_DATE = '20130508', EXPIRY_DATE = '20490101'
go
-- Save the certificate to disk.
BACKUP CERTIFICATE TestCert1 TO FILE = 'c:\CertBackup\TestCert1.cer'
WITH PRIVATE KEY (FILE = 'c:\CertBackup\TestCert1.pvk' ,
                  ENCRYPTION BY PASSWORD = 'TestCert_01',
                  DECRYPTION BY PASSWORD = 'TestCert_01')
go
-- create test user
USE TestSource
CREATE LOGIN [MyTestUser] WITH PASSWORD=N'MyTestUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [MyTestUser] FROM LOGIN [MyTestUser]
GRANT EXECUTE TO [MyTestUser]
go

CREATE DATABASE [testtarget1]
go
USE [testtarget1]
CREATE CERTIFICATE TestCert1 FROM FILE = 'c:\CertBackup\TestCert1.cer'
WITH PRIVATE KEY (FILE ='c:\CertBackup\TestCert1.pvk' ,
                  DECRYPTION BY PASSWORD = 'TestCert_01',
                  ENCRYPTION BY PASSWORD = 'TestCert_01')

CREATE USER TestCert1User FROM CERTIFICATE TestCert1
EXEC sp_addrolemember 'db_owner','TestCert1User'

go
CREATE DATABASE [testtarget2]
go 
USE testtarget2	
CREATE CERTIFICATE TestCert1 FROM FILE = 'c:\CertBackup\TestCert1.cer'
WITH PRIVATE KEY (FILE ='c:\CertBackup\TestCert1.pvk' ,
                  DECRYPTION BY PASSWORD = 'TestCert_01',
                  ENCRYPTION BY PASSWORD = 'TestCert_01')

go
USE testtarget2	

CREATE USER TestCert1User FROM CERTIFICATE TestCert1
EXEC sp_addrolemember 'db_owner','TestCert1User'

go
USE testtarget2	

CREATE TABLE [dbo].[testtarget2table](
	[msg] [varchar](20) NULL
) ON [PRIMARY]

INSERT INTO [testtarget2table]
           ([msg])
     VALUES
           ('testtarget2table')
GO

USE testtarget1
go
CREATE VIEW testtarget1view
AS
select * FROM testtarget2.dbo.testtarget2table

go

USE TestSource
go

CREATE PROCEDURE usp_selecttestTarget1view
as
SELECT * FROM testtarget1.dbo.testtarget1view

--******************************set up end*****************************
-- try execute without signing
USE TestSource

EXECUTE AS LOGIN = 'MyTestUser'

EXEC usp_selecttestTarget1view

-- try execute with signing
revert

ADD SIGNATURE TO usp_selecttestTarget1view BY CERTIFICATE testcert1 WITH PASSWORD = 'TestCert_01'

EXECUTE AS LOGIN = 'MyTestUser'

EXEC usp_selecttestTarget1view
revert
--c