跨資料庫信任與 msdb.dbo.sp_send_dbmail

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]

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: