Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Signing Stored Procedures for Server Level Permissions Demo Code
/*********************************************************************************************************************
****
**** 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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.