Ellie 真是一位用功的夥伴 :)，以下是她的問題與測試
Dear 百敬老師，
請問一個db mail 的問題。
Scenario
1) grant msdb.dbo.sp_send_dbmail的執行權限給 aaa,
2) 在其它user db 裡 create SP with exec as ‘aaa’
3) 有權限執行步驟2)建立的SP之使用者，要可以發信
測試script及Error 如下:
/* create sql login */
use master
go
create login [aaa] with password= N’password’
go
create login [bbb] with password= N’password’
go
use msdb
go
create user [aaa] for login [aaa]
go
/*
EXEC msdb.dbo.sp_addrolemember @rolename = ‘DatabaseMailUserRole’
,@membername = ‘aaa’;
GO
EXEC sp_droprolemember N’DatabaseMailUserRole’, N’aaa’
*/
GRANT EXEC ON [dbo].[sp_send_dbmail] TO [aaa]
go
/* create test DB & stored procedure */
create database TestMailDB
Go
use TestMailDB
go
create user [aaa] for login [aaa]
go
create user [bbb] for login [bbb]
go
create proc test_email
with exec as’aaa’
as
EXEC msdb.dbo.sp_send_dbmail
@recipients =’someone@someSMTP.com’,
@subject= ‘TEST’ ;
go
grant exec on test_email to [bbb]
go
/* execute stored procedure */
exec as user =’bbb’
go
exec test_email
revert
錯誤訊息
訊息 229，層級 14，狀態 5，程序 sp_send_dbmail，行 1
結構描述 ‘dbo’，資料庫 ‘msdb’，物件 ‘sp_send_dbmail’ 沒有 EXECUTE 權限。
/* clean testing data */
use master
drop database TestMailDB
use msdb
drop user [aaa]
use master
drop login [aaa]
drop login [bbb]
我google到一篇文章，介紹使用certificate的方法可達到我要求的目的。
http://www.sqlservercentral.com/articles/Security/68873/
請問老師是不是有其它較好的方法? 謝謝。
我的回答如下：
Dear Ellie,
這是跨 DB 安全的一個典型。
妳的作法就是文中提及 trustworthy 的作法，只要設定如下就可執行
alter database TestMailDB set trustworthy on
但該 DB 的 db_owner(dbo) 和 db_ddladmin 等相關安全權利需要掌握在妳手上，若其他人在該 DB 擁有上述權利，且知道 login 及其他 DB 的 user 關係，就有安全疑慮
否則就要利用文中的 certificate 作法。
謝謝
百敬
Ellie 提供的 Certificate Script
— Create a database for the Example
CREATE DATABASE TestDBMail
GO
— Switch the database
USE [TestDBMail]
GO
create proc test_email
WITH EXECUTE AS OWNER
–exec as user = ‘DBMailLogin’
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = someone@someSMTP.com’,
@subject = ‘TEST’ ;
End;
go
CREATE CERTIFICATE [DBMailCertificate]
ENCRYPTION BY PASSWORD = ‘password’
WITH SUBJECT = ‘Certificate for signing TestSendMail Stored Procedure’;
GO
— Backup certificate so it can be create in master database
BACKUP CERTIFICATE [DBMailCertificate]TO FILE = ‘C:\Backup\DBMailCertificate.CER’;
GO
— Sign the procedure with the certificate’s private key
ADD SIGNATURE TO OBJECT::[test_email]
BY CERTIFICATE [DBMailCertificate]
WITH PASSWORD = ‘password’;
GO
— Drop the private key.
ALTER CERTIFICATE [DBMailCertificate]
REMOVE PRIVATE KEY;
GO
— Add Certificate to Master Database
USE [master]
GO
CREATE CERTIFICATE [DBMailCertificate]FROM FILE = ‘c:\Backup\DBMailCertificate.CER’;
GO
— Create a login from the certificate
CREATE LOGIN [DBMailLogin]FROM CERTIFICATE [DBMailCertificate];
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 [DBMailLogin]
GO
— Create a MSDB User for the Login
USE [msdb]
GO
CREATE USER [DBMailLogin] FROM LOGIN [DBMailLogin]
GO
— Add msdb login/user to the DatabaseMailUserRole
EXEC msdb.dbo.sp_addrolemember @rolename = ‘DatabaseMailUserRole’, @membername = ‘DBMailLogin’;
GO
use TestDBMail
EXECUTE [test_email]
CREATE LOGIN Ellie WITH PASSWORD = ‘password’;
CREATE USER Ellie;
grant exec on test_email to [Ellie]
exec as user = ‘Ellie’
exec test_email
revert
select * From sys.certificates
— clean object
USE [msdb]
GO
DROP USER [DBMailLogin]
GO
USE [master]
GO
DROP LOGIN [DBMailLogin]
DROP CERTIFICATE [DBMailCertificate]
DROP DATABASE [TestDBMail]
偶有所得 