Signing Stored Procedures for Server Level Permissions Demo Code

Signing Stored Procedures for Server Level Permissions Demo Code.sql

/*********************************************************************************************************************
****
****    Demo script for signing stored procedures with certificates
****    
****    Provided as is, run at you on risk, and not on a production server!
****
*********************************************************************************************************************/
 
 
 
/*********************************************************************************************************************
****
****    Create a sandbox to test stuff in
****
*********************************************************************************************************************/
CREATE DATABASE CertificatesTestDB;
GO
 
 
 
/*********************************************************************************************************************
****
****    Setup database master keys, and protect then with the service key so that all sysadmins have rights to use
****    them.
****
****    NOTE: By default the service key is used to encrypt the database master key - see:
****            https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql
****
*********************************************************************************************************************/
USE master
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterDatabaseKey123!';
GO
 
 
USE CertificatesTestDB
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CertificatesTestDBDatabaseKey123!';
GO
 
 
 
/*********************************************************************************************************************
****
****    Now create a certificate. We need the same cert on master to create the login, and the user database to sign
****    procs. We could use certutil to generate an X.509 certificate, or we can just use tSQL to create a self signed
****    one...
****
*********************************************************************************************************************/
USE master
CREATE CERTIFICATE TestCert
    WITH SUBJECT = 'A Test Certificate';
 
BACKUP CERTIFICATE TestCert TO FILE = 'C:\temp\TestCert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\TestCert.pvk'
                 ,ENCRYPTION BY PASSWORD = 'Backup Certificate Password 123456789!'
                 );
 
 
USE CertificatesTestDB
 
CREATE CERTIFICATE TestCert FROM FILE = 'C:\temp\TestCert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\TestCert.pvk'
                 ,DECRYPTION BY PASSWORD = 'Backup Certificate Password 123456789!'
                 );
 
-- Delete the backed up cert
EXEC xp_cmdshell 'del C:\temp\TestCert.cer';
EXEC xp_cmdshell 'del C:\temp\TestCert.pvk';

 

/*********************************************************************************************************************
****
****    Now create logins and users from the cert, and create a test proc. Aslo create a loginless user with only exec
****    permissions on the proc for testing
****
*********************************************************************************************************************/
USE master

-- Create a login with limited permissions for testing
CREATE LOGIN TestLogin WITH PASSWORD = 'P@ssw0rd123!P@ssw0rd123!';

-- Create a login based on the certificate, and add them to the bulkadmin server role
CREATE LOGIN TestCertLogin
    FROM CERTIFICATE TestCert;
 
EXEC sp_addsrvrolemember @loginame = 'TestCertLogin',
                         @rolename = 'bulkadmin';
 
 
USE CertificatesTestDB

-- Create a database user from the login for testing 
CREATE USER TestUser FOR LOGIN TestLogin;
GO

-- Create a database user from the certificate login 
CREATE USER TestCertUser FOR LOGIN TestCertLogin;
GO


 
 -- Create a simple test proc to show effective permissions 
IF OBJECT_ID('dbo.TestCertPermissions') IS NOT NULL
  DROP PROCEDURE dbo.TestCertPermissions;
GO
CREATE PROCEDURE dbo.TestCertPermissions
AS
BEGIN
    SELECT USER_NAME(),
           SUSER_SNAME(),
           ORIGINAL_LOGIN();
 
    SELECT *
    FROM fn_my_permissions (NULL, 'SERVER')
    UNION ALL
    SELECT *
    FROM fn_my_permissions (NULL, 'DATABASE');
END
GO
 
GRANT EXECUTE ON dbo.TestCertPermissions TO TestUser;
 


/*********************************************************************************************************************
****
****    Now test the proc to see the results:
****    1)  Test with my login, proc not signed. I'm sysadmin so should see lots of permissions.
****    2)  Test in the context of TestUser, proc not signed. Should only see CONNECT permissions
****    3)  Test in the context of TestUser, proc IS signed. The user context is a local db user,
****        so the permissions from the certificate are at the db level only. Should only see CONNECT
****        permissions
****    4)  Test in the context of TestLogin, proc IS signed. Should see CONNECT and BULK ADMIN 
****        permissions.
****
*********************************************************************************************************************/
-- 1) Execute it using my login
EXEC dbo.TestCertPermissions;
 
-- 2) Execute it using the restricted login
EXECUTE AS USER = 'TestUser';
EXEC dbo.TestCertPermissions;
REVERT;

-- Sign the proc with the certificate
ADD SIGNATURE TO dbo.TestCertPermissions
    BY CERTIFICATE TestCert;
 
-- 3) Execute as the test user (database scoped)
EXECUTE AS USER = 'TestUser';
EXEC dbo.TestCertPermissions;
REVERT;
 
-- 4) Execute as the test login (sever scoped)
EXECUTE AS LOGIN = 'TestLogin';
EXEC dbo.TestCertPermissions;
REVERT;



/*********************************************************************************************************************
****
****    Tidy up
****
*********************************************************************************************************************/
 
USE master


IF DB_ID('CertificatesTestDB') IS NOT NULL
BEGIN
  ALTER DATABASE CertificatesTestDB
  SET SINGLE_USER
  WITH ROLLBACK IMMEDIATE;
 
  DROP DATABASE CertificatesTestDB;
END

IF EXISTS (SELECT * FROM sys.syslogins WHERE name = 'TestLogin')
  DROP LOGIN TestLogin;
 
IF USER_ID('TestCertUser') IS NOT NULL
  DROP USER TestCertUser;
 
IF EXISTS (SELECT * FROM sys.syslogins WHERE name = 'TestCertLogin')
  DROP LOGIN TestCertLogin;
 
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'TestCert')
  DROP CERTIFICATE TestCert;
 
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
  DROP MASTER KEY;
 
/*
-- Turn on xp_cmdshell if required (dev or local instances only)
EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
EXEC sp_configure 'xp_cmdshell', 1; 
GO 
RECONFIGURE; 
GO 
*/
 
-- Delete the backed up cert (use insert into exec to discard the result set)
IF OBJECT_ID('tempdb..#xp_cmdshell_results') IS NOT NULL
  DROP TABLE #xp_cmdshell_results
CREATE TABLE #xp_cmdshell_results (Info VARCHAR(MAX))
INSERT INTO #xp_cmdshell_results EXEC xp_cmdshell 'del C:\temp\TestCert.cer';
INSERT INTO #xp_cmdshell_results EXEC xp_cmdshell 'del C:\temp\TestCert.pvk';