|
Great Article!
I have a couple of suggestions to make the stored proc more flexible.
1. Make the certificate name and certificate user name parameters so that it could be used for multiple certificate/users without changing code within the Proc.
2. If you changed the cursor to be a temp table or table variable that is populated from the select, it could then be used to loop through instead of a cursor. You could then use the same table variable to loop back through to add the signatures with the newly recreated certificate user.
3. Add a loop similar to above for the granting of the table permissions so that it can be dynamically granted to the cert user based on what it was before the user was dropped.
Sample procedure code:
-- **********************************************************************
-- PROCEDURE GrantCertificateAccess
-- Drops all certificate signatures and recreates them for existing certificate signed stored procs
-- for the cert value, cert user, and the new password passed in through parameters
-- Exeample Execution: EXEC GrantCertificateAccess 'myCertName', 'myCertUserName','myNewPassword'
-- **********************************************************************
CREATE PROCEDURE [dbo].[GrantCertificateAccess]
(
@CertificateName AS sysname = 'myCertName',
@CertificateUserName AS sysname = 'myCertUserName',
@Password AS sysname = 'myNewPassword'
)AS
BEGIN
--Declare Variables
DECLARE @sSql varchar(4000)
DECLARE @NewLine varchar(4000)
SET @NewLine = CHAR(13) + CHAR(10)
DECLARE @ProcRowsToProcess int
DECLARE @TableRowsToProcess int
DECLARE @currentrow int
DECLARE @ProcedureName AS sysname
DECLARE @TableName AS varchar(100)
DECLARE @Permission AS varchar(20)
--Table variable for list of Procedures with Certificate Signature
DECLARE @ProcList TABLE
(ProcedureName VARCHAR(200), Row_ID int not null primary key identity(1,1))
--Table variable for table permissions granted to Certificate User
DECLARE @TableList TABLE
(TableName VARCHAR(200), Permission VARCHAR(20), Row_ID int not null primary key identity(1,1))
--Populate the table variable for the Procedure list
INSERT INTO @ProcList (ProcedureName)
SELECT Object_Name(CP.major_id) ProcedureName
FROM sys.certificates C
INNER JOIN sys.crypt_properties CP ON CP.thumbprint = C.thumbprint
WHERE C.Name = @CertificateName
SET @ProcRowsToProcess=@@ROWCOUNT
--Populate the table variable for the Table permission list
INSERT INTO @TableList (TableName, Permission)
SELECT '['+db_name()+'].['+Object_Name(p.major_id)+']' AS tablename,
p.permission_name
FROM sys.database_permissions p
LEFT OUTER JOIN sys.all_objects o ON p.major_id = o.OBJECT_ID
INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE o.type = 'U' --only pull permissions for user tables
AND p.state_desc = 'GRANT'
AND p.permission_name IN ('SELECT','INSERT','UPDATE','DELETE')
AND dp.NAME = @CertificateUserName
--initialize this variable for the current loop
SET @CurrentRow=0
--*********************************************************************
-- Drop the signatures signed by the Certificate from stored procedures
--*********************************************************************
PRINT '-- Dropping existing Signatures from Procedures...'
WHILE @CurrentRow < @ProcRowsToProcess
BEGIN
SET @CurrentRow = @CurrentRow + 1
SELECT @ProcedureName = ProcedureName
FROM @ProcList
WHERE Row_ID = @currentrow
SET @sSql = 'DROP SIGNATURE FROM ' + QuoteName(@ProcedureName) + ' BY CERTIFICATE ' + @CertificateName
PRINT @sSql
EXEC sp_executesql @sSql
END --END WHILE
PRINT @NewLine + '-- Dropping Certificate User...'
IF EXISTS(SELECT 1
FROM sysusers
WHERE name = @CertificateUserName)
BEGIN
SET @sSql = 'DROP USER ' + @CertificateUserName
PRINT @sSql
EXEC(@sSql)
END --END IF
PRINT @NewLine + '-- Dropping Certificate...'
IF EXISTS(SELECT 1
FROM sys.certificates C
WHERE C.[name] = @CertificateName)
BEGIN
SET @sSql = 'DROP CERTIFICATE ' + QuoteName(@CertificateName)
PRINT @sSql
EXEC (@sSql)
END --END IF
-- Create a new certificate if it does not exist
IF NOT EXISTS(SELECT 1
FROM sys.certificates C
WHERE C.[name] = @CertificateName)
BEGIN
PRINT @NewLine + '-- Creating new certificate...'
SET @sSql = 'CREATE CERTIFICATE ' + QuoteName(@CertificateName) + @NewLine
+ ' ENCRYPTION BY PASSWORD = ''' + @Password + '''' + @NewLine
+ ' WITH SUBJECT = ''Certificate for stored procedures using dynamic SQL'',' + @NewLine
+ ' START_DATE = ''2010-01-01'',' + @NewLine
+ ' EXPIRY_DATE = ''2100-01-01'''
PRINT @sSql
EXEC (@sSql)
PRINT @NewLine + '-- Creating User...'
SET @sSql = 'CREATE USER ' + QuoteName(@CertificateUserName) + ' FROM CERTIFICATE ' + QuoteName(@CertificateName)
EXEC (@sSql)
PRINT @sSql
END --END IF
--initialize this variable for the current loop
SET @CurrentRow=0
--**********************************************************
-- Add back in the table permissions to the certificate user
--**********************************************************
PRINT @NewLine + '-- Granting Select to Certificate User...'
WHILE @CurrentRow < @TableRowsToProcess
BEGIN
SET @CurrentRow = @CurrentRow + 1
SELECT @TableName = TableName, @Permission = Permission
FROM @TableList
WHERE Row_ID = @currentrow
SET @sSql = 'GRANT ' + @Permission + ' ON ' + @TableName + ' TO ' + QuoteName(@CertificateUserName)
PRINT @sSql
EXEC (@sSql)
END --END WHILE
--initialize this variable for the current loop
SET @CurrentRow=0
--****************************************************
-- Add back in the signatures to the stored procedures
--****************************************************
PRINT 'Signing procedures...'
WHILE @CurrentRow < @ProcRowsToProcess
BEGIN
SET @CurrentRow = @CurrentRow + 1
SELECT @ProcedureName = ProcedureName
FROM @ProcList
WHERE Row_ID = @currentrow
SET @sSql = 'ADD SIGNATURE TO ' + QuoteName(@ProcedureName) + ' BY CERTIFICATE ' + QuoteName(@CertificateName) + ' WITH PASSWORD = ''' + @Password + ''''
PRINT @sSql
EXEC (@sSql)
END --END WHILE
END --END Procedure
GO
|
