Having lockable compartments inside your boat is unfortunately pretty important. A couple years ago I was traveling back from a fishing trip with my brother-in-law and we stopped at a gas station. I remained in the truck while my brother went in to get something to drink. We had gotten up very early to fish that morning, and I was not awake, but not all the way sleeping either. Anyway, someone either could tell I wasn’t paying attention or they just didn’t care and they took my tackle box right out of the back of the boat. I had failed to keep it locked up, and as a result I had quite a bit of tackle taken.
Security is a key concern for database administrators. It seems like every week we hear about another large corporation who had customer data stolen. One way to restrict access to data within the database is through the use of Users. A database user has specific permissions set up, and these permissions can be customized from the database level all the way down to specific columns within a table. However, what should you do if you want a user in one database to be able to access data in a different database? In this post I will show how to sign stored procedures with certificates to enable access to objects in other databases.
The first thing to do is to create a certificate. You can put in a password to make it secure, and give it an expiration date far in the future.
CREATE CERTIFICATE ReportingComponentsExecutor_Cert ENCRYPTION BY PASSWORD = 'Y3110wP3r¢h' WITH SUBJECT = 'Gugg: For accessing object in other databases', --Goes into the cert metadata START_DATE = '20150320', EXPIRY_DATE = '2020320'
The next step is to create a user that will be linked to that certificate in the target object. Since this is the user who has the permissions in the target database, the user needs to be created in that database.
CREATE USER [ReportingComponentsExecutor_User] FOR CERTIFICATE [ReportingComponentsExecutor_Cert] GO
Next we need to give this user the permissions on the database objects that will be needed.
USE TargetDB GRANT SELECT ON TargetDB.dbo.myTable TO ReportingComponentsExecutor_User; GRANT UPDATE ON TargetDB.dbo.myTable TO ReportingComponentsExecutor_User;
Lastly, add the certificate to the stored procedure that will be getting the data from the target database.
ADD SIGNATURE TO ReportingComponents.dbo.GetDataFromTargetDB BY CERTIFICATE ReportingComponentsExecutor_Cert WITH PASSWORD = 'Y3110wP3r¢h'
Any change made to the stored procedure will invalidate the signature, so you will have to re-sign it after every change. It can be a good idea to make a note in the stored procedure’s header so anyone making changes will remember to re-sign it. I also like to set up a table with a list of stored procedures that should be signed, and send out an alert each day for any that have become unsigned. Here is a rough example that uses a hardcoded list instead of a table. This uses the stored procedure I showed here that sends dbmail in html table format:
CREATE PROCEDURE [dbo].[VerifySPSignatures] AS BEGIN DECLARE @SignedSPs TABLE (SP VARCHAR(100)) INSERT INTO @SignedSPs ( SP ) VALUES ( 'InsertPendingTransIntoOMS' ), ('UnlockReplenishmentBatch'), ('InsertSameShipAddressIntoPickSlip'), ('UpdatePLMStyleGradeRuleValues'), ('InsertPendingReplenIntoOMS'), ('UpdateAdditionalPOInfo'), ('UnredeemTestGiftCardsForCSTraining') ;WITH cte AS ( SELECT cer.name AS [Certificate] , pro.name AS [Stored Procedure] FROM ReportingComponents.sys.procedures pro INNER JOIN ReportingComponents.sys.crypt_properties cry ON cry.major_id = pro.OBJECT_ID INNER JOIN ReportingComponents.sys.certificates cer ON cer.thumbprint = cry.thumbprint) SELECT [@SignedSPs].SP INTO ##CertSPTemp FROM @SignedSPs LEFT OUTER JOIN cte ON cte.[Stored Procedure] = [@SignedSPs].SP WHERE cte.[Stored Procedure] IS NULL EXEC DBA.dbo.SendDBEmailwithTabularQuery @qSELECT = N'SELECT', @fieldlist = N'SP', @qFROM = N'FROM ##CertSPTemp', @recipients = Nfirstname.lastname@example.org', @subject = N'Reporting Stored Procedures Missing Security Certificate', @Title = 'From DBA.dbo.VerifySPSignatures: These need to be re-signed using stored procedures in DBA.' DROP TABLE ##CertSPTemp END
I also created a stored procedure in my DBA database to help sign these stored procedures as necessary. Since it is in the DBA database it is only accessible to the DBAs, so I don’t have to worry about it being abused.
CREATE PROCEDURE [dbo].[AddSPToOMSCert] @StoredProcedureSchemaandName VARCHAR(255), --ReportingComponents stored procedure name with schema included @TargetDBObjectSchemaandName VARCHAR(255), --TargetDB table or view name with schema included @AccessType VARCHAR(25) --SELECT, DELETE, UPDATE, or INSERT AS BEGIN DECLARE @SignSQL NVARCHAR(1000) DECLARE @FullSignSQL NVARCHAR(1000) DECLARE @PermSQL NVARCHAR(1000) --Sign the stored procedure using the existing certificate SELECT @SignSQL = 'ADD SIGNATURE TO ReportingComponents.' + @StoredProcedureSchemaandName + ' BY CERTIFICATE ReportingComponentsExecutor WITH PASSWORD = ''''P@ssword123''''' SELECT @FullSignSQL = 'USE ReportingComponents; EXEC sp_executesql N''' + @SignSQL + ''''; EXEC sp_executesql @FullSignSQL --Add permission to the OMSObject SELECT @PermSQL = 'USE TARGETDB GRANT ' + @AccessType + ' ON TARGETDB.' + @TargetDBObjectSchemaandName + ' TO ReportingComponentsExecutor' EXEC sp_executesql @PermSQL END GO'
This simplifies the process of signing a stored procedure.
I love looking at examples to demonstrate how something works, so let’s set one up. Let’s assume we have two databases, Gamefish and Baitfish. I have a user in Gamefish, Muskie, who I want to be able to see into a table in Baitfish, but only through the use of a stored procedure. Here’s how I’d set it up. It is important to note that the same certificate must exist in both databases. This means we must export the cert created in the first database and import it into the second.
--Create the databases USE MASTER CREATE DATABASE Baitfish; CREATE DATABASE Gamefish; GO --Create the login that will be testing permissions CREATE LOGIN [Muskie] WITH PASSWORD=N'Password123' GO --Create the cert USE Baitfish CREATE CERTIFICATE CrossDBReportingCert ENCRYPTION BY PASSWORD = 'B1u3g1LL' WITH SUBJECT = 'Gugg: For accessing object in other databases', --Goes into the cert metadata START_DATE = '20150320', EXPIRY_DATE = '20200320' GO --Export the cert so we can import it into the other database BACKUP CERTIFICATE CrossDBReportingCert TO FILE = 'C:\temp\CrossDBReportingCert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\CrossDBReportingCert.pvk' , ENCRYPTION BY PASSWORD = 'B1u3g1LL', DECRYPTION BY PASSWORD = 'B1u3g1LL') GO --Create the user off the cert CREATE USER CrossDBReportingUser FROM CERTIFICATE CrossDBReportingCert GO --Create the table in baitfish CREATE TABLE dbo.prey (id INT IDENTITY(1,1), fishname VARCHAR(50)) INSERT INTO dbo.prey ( fishname ) VALUES ( 'whitefish'),('cisco'),('sucker') GO --Grant read-only permissions to the new user for dbo.prey GRANT SELECT ON dbo.prey TO CrossDBReportingUser GO --Switch over to the Gamefish Database USE Gamefish GO --Create a user who will have no permissions in the Baitfish database, but full access to the Gamefish DB CREATE USER [Muskie] FOR LOGIN [Muskie] EXEC sp_addrolemember N'db_owner', N'Muskie' GO --Create a Stored Procedure to get the fish names out of Baitfish.dbo.Prey CREATE PROCEDURE dbo.GetBaitfishPrey AS BEGIN SELECT fishname FROM Baitfish.dbo.prey END GO --Cert must exist in both databases CREATE CERTIFICATE CrossDBReportingCert FROM FILE = 'C:\temp\CrossDBReportingCert.cer' WITH PRIVATE KEY (FILE = 'C:\temp\CrossDBReportingCert.pvk', DECRYPTION BY PASSWORD = 'B1u3g1LL', ENCRYPTION BY PASSWORD = 'B1u3g1LL') GO -- Delete the file with the certificate, we don't need it anymore. EXEC MASTER..xp_cmdshell 'DEL C:\temp\CrossDBReportingCert.*', 'no_output' GO --Run as Muskie user EXECUTE AS LOGIN = 'Muskie' GO --Try to get results before signing procedure with certificate EXEC dbo.GetBaitfishPrey GO
REVERT GO --Next sign procedure with certificate ADD SIGNATURE TO dbo.GetBaitfishPrey BY CERTIFICATE CrossDBReportingCert WITH PASSWORD = 'B1u3g1LL' GO --Run as Muskie user EXECUTE AS LOGIN = 'Muskie' GO --Successfully get results from stored procedure EXEC dbo.GetBaitfishPrey GO
REVERT GO --Cleanup USE MASTER DROP LOGIN Muskie DROP DATABASE Gamefish; DROP DATABASE Baitfish; GO
So there is an example that I believe is easy to understand. Use this as a model to set up your own cross database permissions.