sql-server-2005 - query - sp_send_dbmail sql 2016

Cómo ejecutar sp_send_dbmail mientras se limitan los permisos

Question

¿Hay alguna manera de proporcionar acceso a los usuarios en mi base de datos para ejecutar msdb.dbo.sp_send_dbmail sin la necesidad de agregarlos a la base de datos MSDB y al DatabaseMailUserRole?

He intentado esto:

ALTER PROCEDURE [dbo].[_TestSendMail]
(
  @To NVARCHAR(1000),
  @Subject NVARCHAR(100),
  @Body NVARCHAR(MAX)
)
WITH EXECUTE AS OWNER
AS 
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail @profile_name = N''myProfile'',
            @recipients = @To, @subject = @Subject, @body = @Body
    END

Pero me sale este error:

The EXECUTE permission was denied on the object ''sp_send_dbmail'', database ''msdb'', schema ''dbo''.

¡Gracias!

Answer 1

Una solución posible es encapsular el correo como un procedimiento almacenado, por ejemplo mail_error_as_MAILER (que luego llamará) y otro procedimiento almacenado, por ejemplo

ALTER PROCEDURE [dbo].[mail_error](@error_ID int)
SET NOCOUNT ON
declare @rc int
declare @object int
declare @src varchar(255)
declare @desc varchar(255)
declare @osql_cmd varchar(1000)
-- create shell object
exec @rc = sp_oacreate ''wscript.shell'', @object out
if @rc0 
  begin
    exec sp_oageterrorinfo @object, @src out, @desc out
    return
  END

  DECLARE @user VARCHAR(50)
  DECLARE @password VARCHAR(50)
  DECLARE @database VARCHAR(50)
  DECLARE @server VARCHAR(50)
  DECLARE @sql varchar(200) 
 SET @user=MAILER,@password=XXXXXX,@database=XXXXXX,@server=XXXXX 
SET @sql= ''EXEC mail_ERROR_as_MAILER @error_ID='' + CAST(@error_id as varchar(10))
set @osql_cmd=''osql -U''+@user+'' -P''+@password+'' -d''+@database+'' -S"''+@server+''"  -Q"''
  +@sql+''"''

exec @rc= sp_oamethod @object, ''run'', null, @osql_cmd
--print @rc
if @rc0 
  begin
    exec sp_oageterrorinfo @object, @src out, @desc out
    return
  end
-- destroy shell object
exec sp_oadestroy @object

Pero esto requiere: * contraseña de hardcoding (asegúrese de que los usuarios no puedan ver la definición del procedimiento almacenado ...) * dando acceso a los usuarios a sp_oacreate, etc ... (abre otros problemas de seguridad, por ejemplo, DOS)

de esa manera solo pueden usar el correo de la forma que desee sin darles permiso para enviar por correo otras cosas.

O más seguro, permita que los usuarios coloquen el correo en algún tipo de mail_queue (que puede controlar lo que pueden poner) y solicite a un agente que envíe estos correos periódicamente

O bien: dales permiso para enviar correos pero toma un club grande con picos oxidados en el abuso

Answer 2

De hecho, puede hacerlo con un procedimiento almacenado con certificado firmado y no tiene que ser en msdb para hacerlo:

CREATE DATABASE TestDBMail
GO

USE [TestDBMail]
GO

CREATE PROCEDURE [dbo].[TestSendMail]
(       
        @To NVARCHAR(1000),  
        @Subject NVARCHAR(100),  
        @Body NVARCHAR(MAX)
)
WITH EXECUTE AS OWNER
AS
BEGIN
        EXEC msdb.dbo.sp_send_dbmail 
                        @profile_name = N''Database Mail Profile'',
                        @recipients = @To, 
                        @subject = @Subject, 
                        @body = @Body    
END
GO

-- This should fail
EXECUTE [dbo].[TestSendMail] ''[email protected]'', ''test'', ''body''

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

-- Backup certificate so it can be create in master database
BACKUP CERTIFICATE [DBMailCertificate]
TO FILE = ''d:/Backup/DBMailCertificate.CER'';
GO

-- Add Certificate to Master Database
USE [master]
GO
CREATE CERTIFICATE [DBMailCertificate]
FROM FILE = ''d:/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]
GO

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

-- This will succeed
EXECUTE [dbo].[TestSendMail] ''[email protected]'', ''test'', ''body''

/*
-- Cleanup
USE [msdb]
GO
DROP USER [DBMailLogin]
GO
USE [master]
GO
DROP LOGIN [DBMailLogin]
DROP CERTIFICATE [DBMailCertificate]
DROP DATABASE [TestDBMail]

-- Delete the certificate backup from disk

*/

Answer 3

Su enfoque está bien, pero su proceso de contenedor debe estar en la base de datos msdb. Luego, ejecuta "EXEC msdb.dbo._TestSendMail"

Esto todavía deja el problema de los permisos en dbo._TestSendMail en msdb. Pero public / EXECUTE será suficiente: solo expone los 3 parámetros que necesita.

Si tiene dudas, agregue CON ENCRIPTACIÓN. Esto es lo suficientemente bueno para detener a cualquier persona sin derechos de administrador de sistemas viendo el código

USE msdb
GO
CREATE PROCEDURE [dbo].[_TestSendMail]
(
  @To NVARCHAR(1000),
  @Subject NVARCHAR(100),
  @Body NVARCHAR(MAX)
)
-- not needec WITH EXECUTE AS OWNER
AS 
    BEGIN
        EXEC dbo.sp_send_dbmail @profile_name = N''myProfile'',
            @recipients = @To, @subject = @Subject, @body = @Body
    END