Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail Expand / Collapse
Author
Message
Posted Thursday, December 17, 2009 4:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 12:44 AM
Points: 77, Visits: 568
CREATE PROCEDURE [dbo].[TestSendMail]

.....


WITH EXECUTE AS <account with rights to send e-mails>



Wouldn't it be easier that way?
Post #836047
Posted Thursday, December 17, 2009 4:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
simon.murin (12/17/2009)
CREATE PROCEDURE [dbo].[TestSendMail]

.....
WITH EXECUTE AS <account with rights to send e-mails>

Wouldn't it be easier that way?


Have you tried it without TRUSTWORTHY ON? It won't work for a number of reasons. First the possible values for the EXECUTE AS clause are

EXECUTE AS Clause (Transact-SQL)
Functions (except inline table-valued functions), Stored Procedures, and DML Triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }


You would have to specify a login_name, not a user_name because this stored procedure is in a User Database and the permissions needed to execute sp_send_dbmail are needed in msdb. The database user in a user database has no rights or permissions in another database, the login associated with that user does. TRUSTWORTHY ON would be required to make that work, which has additional ramifications security wise.

From the Article....
This allows for Impersonation across databases to gain access to resources in other databases on the server. It is not a secure method of solving the problem since having TRUSTWORTHY ON allows for other side effects. If the database owner is a sysadmin, then TRUSTWORTHY ON provides full access to the server. This flag is also set OFF whenever a database is attached or restored to SQL Server. This is an easy to miss item that can become problematic in the worst possible times, like during disaster recovery.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #836049
Posted Friday, January 8, 2010 3:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 12, 2012 9:12 PM
Points: 82, Visits: 65
Great article I found after searching for long time in Internet about impersonation and cross database accessing.

I got one simple solution perticularly for database mail.

Simply give execute permission on the sp "sp_send_dbmail" to the role "public" in the database "msdb".
Any call to this sp from other database/sp/function/.. with impersination (or without impersination I hope) will be take care.
You can get rid of the most annoying error "The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'"
Post #844742
Posted Friday, January 8, 2010 5:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
Uma-419424 (1/8/2010)


Simply give execute permission on the sp "sp_send_dbmail" to the role "public" in the database "msdb".


That would violate Audit rules at my company, and most publicly held companies as well. In fact we have a script that locks down the public role further than it is at installation. If you develop software for sale, you have to keep things like Audit's in mind or you can severely limit your customer base.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #844778
Posted Saturday, January 9, 2010 10:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 21,639, Visits: 15,309
Thanks for the great read.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #844989
Posted Monday, January 11, 2010 5:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
I thought I had found the solution for my problem, but alas...
I need to call master.sys.dm_exec_sql_text(to find the last used sql-statement) from a trigger, or sp, in another database, by ordinary users without SA-rights; please don't ask why.
I changed reference in your article from msdb into master, used GRANT VIEW SERVER STATE as authorization, but alas, no succes. For days now I am looking for a way to allow non-SA users to view DMV's. Any1 here can help me out?!



Greetz,
Hans Brouwer
Post #845315
Posted Monday, January 11, 2010 11:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
FreeHansje (1/11/2010)
I thought I had found the solution for my problem, but alas...
I need to call master.sys.dm_exec_sql_text(to find the last used sql-statement) from a trigger, or sp, in another database, by ordinary users without SA-rights; please don't ask why.
I changed reference in your article from msdb into master, used GRANT VIEW SERVER STATE as authorization, but alas, no succes. For days now I am looking for a way to allow non-SA users to view DMV's. Any1 here can help me out?!


You actually don't need any kind of special permissions to solve your problem. First sys.dm_exec_sql_text isn't going to give you the information that you probably want. If you run that for a session_id in a trigger, it is going to return the CREATE TRIGGER statement of the trigger firing the request. As long as the need to get the SQL Statement that was executed to cause the trigger to fire is self session scoped, you can get it without added permissions using DBCC INPUTBUFFER() like follows:

CREATE TABLE PermsTest
(RowID int identity primary key)
GO

CREATE TRIGGER Audit_PermsTest
ON PermsTest
WITH EXECUTE AS OWNER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @TEMP TABLE
(EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000))
INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)')
SELECT EventInfo FROM @TEMP
END
GO

If you want to get information from another session that is executing that would require the additional permissions. As long as it stays self scoped it can be called without GRANT VIEW SERVER STATE.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #845612
Posted Monday, January 11, 2010 12:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
For those interested, you can use the same method to give SA rights or access like VIEW SERVER STATE to users with no access. For example to call sys.dm_exec_sql_text() as originally requested:

USE [master]
GO

CREATE DATABASE SAPermsTest
GO

CREATE LOGIN [PermsTestLogin]
WITH PASSWORD=N'c0mpl3xp@$$'
GO

USE [SAPermsTest]
GO

CREATE USER [PermsTestLogin] FROM LOGIN [PermsTestLogin]
GO

CREATE TABLE PermsTest
(RowID int identity primary key)
GO

GRANT INSERT ON PermsTest TO PermsTestLogin
GO

CREATE TRIGGER Audit_PermsTest
ON PermsTest
WITH EXECUTE AS OWNER
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @TEMP TABLE
(EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000))
INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)')
SELECT EventInfo FROM @TEMP

SELECT SYSTEM_USER

SELECT text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id = @@SPID
END
GO

-- Test the trigger will fail. Make sure to revert
EXECUTE AS LOGIN='PermsTestLogin'
INSERT INTO PermsTest default values;
REVERT
GO

-- Revert Fails when trigger execution fails so do it now.
REVERT
GO

-- Create a certificate to sign stored procedures with
CREATE CERTIFICATE [SAPermsCertificate]
ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'
WITH SUBJECT = 'Certificate for signing Audit Triggers';
GO

-- Backup certificate so it can be create in master database
BACKUP CERTIFICATE [SAPermsCertificate]
TO FILE = 'D:\SQLBackups\SAPermsCertificate.CER';
GO

-- Add Certificate to Master Database
USE [master]
GO
CREATE CERTIFICATE [SAPermsCertificate]
FROM FILE = 'D:\SQLBackups\SAPermsCertificate.CER';
GO
-- Create a login from the certificate
CREATE LOGIN [SAPermsLogin]
FROM CERTIFICATE [SAPermsCertificate];
GO

-- The Login must have Authenticate Sever to access server scoped system tables
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [SAPermsLogin]
GO

-- Add the VIEW Server State permission to the Certificate login.
GRANT VIEW SERVER STATE TO [SAPermsLogin]
GO

USE [SAPermsTest]
GO

-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::Audit_PermsTest
BY CERTIFICATE [SAPermsCertificate]
WITH PASSWORD = '$tr0ngp@$$w0rd';
GO

-- Retest the Trigger function and it will work returning
-- CREATE TRIGGER statement for the sys.dm_exec_sql_text()
EXECUTE AS LOGIN='PermsTestLogin'
INSERT INTO PermsTest default values;
REVERT
GO

-- Cleanup
/*
USE [master]
GO
DROP LOGIN [SAPermsLogin]
DROP CERTIFICATE [SAPermsCertificate]
DROP DATABASE [SAPermsTest]
-- Delete the certificate backup from disk
*/


If you run the above, you will note that as I stated in my last response the sys.dm_exec_sql_text() DMF will not output the last statement called by the client it will output the last statement executed by the session which happens to be the execution of the trigger itself. Kind of self defeating code, at least in my opinion which is why I still fall back on good ole' trusty DBCC INPUTBUFFER to get the last input statement from the client.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #845627
Posted Thursday, January 14, 2010 12:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
Tnx Jonathan, this is really hel;pful, in several ways.


Greetz,
Hans Brouwer
Post #847367
Posted Monday, January 18, 2010 5:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
Jonathan,
I wish to say thanks again. I must have done something wrong, different then you describe in your solution. I could have sworn I used the same construction to test permission on the DMV's, but could not get it working. But as I said, I must have donw something different, and since there is no1 at my current position with the knowledge to talk this over I could not pinpoint the error.
Also tnx for showing that my initial 'solution' did not work. With your suggestion I can get things working.

Tnx again, I have learned a lot today.



Greetz,
Hans Brouwer
Post #849093
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse