SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Certificates to Sign Stored Procedures


Using Certificates to Sign Stored Procedures

Author
Message
Jonathan AC Roberts
Jonathan AC Roberts
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 1907
Comments posted to this topic are about the item Using Certificates to Sign Stored Procedures
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3449 Visits: 1865
Hi Jonathon

That was a nice article.

I have a doubt.

We granted select permissions to mycertificateuser. Do we need to give permissions to all the other users who are going to execute the procedure (i think not.. thats the point of this article).

User xyz has been given execute permissions to the stored procedure and no select permissions on the underlying tables. When xyz executes this procedure does the exectution context belong to mycertificateuser. How does xyz get access to the underlying tables in the dynamic sql in hte stored procedure.

Note:- I have not gone thru BOL or any other documentation before asking this. Please bear with me.

"Keep Trying"
Lawrence Moore
Lawrence Moore
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 400
Excellent article. Well written with clear examples.
Jonathan AC Roberts
Jonathan AC Roberts
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 1907
ChiragNS (6/21/2010)

We granted select permissions to mycertificateuser. Do we need to give permissions to all the other users who are going to execute the procedure (i think not.. thats the point of this article).
You need to grant EXECUTE permission to the stored procedure to all users who need to execute the procedure but you do not need to grant them any access to the tables if you use a certificate.

User xyz has been given execute permissions to the stored procedure and no select permissions on the underlying tables. When xyz executes this procedure does the exectution context belong to mycertificateuser. How does xyz get access to the underlying tables in the dynamic sql in hte stored procedure.
The procedure is executed by user 'xyz' but the stored procedure gets its permission to select from the table through the certificate.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1261 Visits: 1499
Jonathan AC Roberts (6/21/2010)
[quote]ChiragNS (6/21/2010)

We granted select permissions to mycertificateuser. Do we need to give permissions to all the other users who are going to execute the procedure (i think not.. thats the point of this article).
You need to grant EXECUTE permission to the stored procedure to all users who need to execute the procedure but you do not need to grant them any access to the tables if you use a certificate.

Jonathan is correct. In fact it would be a bad practice to do so. One of the primary reasons for using stored procedure database access is to avoid ever having to grant direct access to database data. You don't have to implement certificate access to prevent direct table access.

Still a nice article that offers up some good information.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Judy Scheinuk
Judy Scheinuk
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 461
I am trying to allow a non-sysadmin user to run the create login command. Is this possible? I have done the steps in your article but my user still can't create logins. The login created by the certificate has the security administrator role. The user is dbo in his database and I've signed the stored procedure that he runs. We would only use this for the one stored procedure on the test server. Can this be done?
SQL_Elvis
SQL_Elvis
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 143
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
246747
246747
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 51
Judy Scheinuk (6/21/2010)
I am trying to allow a non-sysadmin user to run the create login command. Is this possible? I have done the steps in your article but my user still can't create logins. The login created by the certificate has the security administrator role. The user is dbo in his database and I've signed the stored procedure that he runs. We would only use this for the one stored procedure on the test server. Can this be done?
I've never used a certificate to grant create logins to a stored procedure but you would have to grant ALTER ANY LOGIN to the certificate user that is used to sign the stored procedure.
Judy Scheinuk
Judy Scheinuk
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 461
Thank you so much! It all works now. Granting the login the system administrator role was not necessary - just the Grant ALTER ANY LOGIN to xxxxx.

Thanks for the excellent article.
janyalee
janyalee
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 44
Using a Certificate Versus Stored Procedure WITH EXECUTE AS
Hello Jonathan, I appreciated this article. It got me wondering, what signing with a certificate gets us that SP WITH EXECUTE AS functionality does not?
Either way we grant myCertUserName read access to the underlying tables called by the sp.
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\myCertUserName'
AS
SELECT user_name();
GO
Thank you in advance for your feedback.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search