locked
Database Certificate users and permissions RRS feed

  • Question

  • 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

    Sean

    Wednesday, July 10, 2013 5:38 PM

Answers

All replies

  • 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
    --clean

    Sean

    • Merged by Fanny Liu Thursday, July 11, 2013 1:13 AM duplicate
    Wednesday, July 10, 2013 5:38 PM
  • http://www.sommarskog.se/grantperm.html

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Fanny Liu Friday, July 12, 2013 3:04 AM
    • Marked as answer by Fanny Liu Tuesday, July 16, 2013 5:41 AM
    Thursday, July 11, 2013 6:33 AM