SQL Server 2000 permissions problems
-
I am having permissions issues with the following SQL Server 2000 stored procedure:
---------------------------------------------------------
CREATE PROCEDURE [EMail] @From varchar(100) , @To varchar(100) , @Subject varchar(100)=" ", @Body varchar(4000) =" " /******************************************
This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/ library/en-us/cdosys/html/_cdosys_messaging.asp
*******************************************/
AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000)
--***** Create the CDO.Message Object *****
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail1.traffic.com'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
--------------------------------------------
The procedure is called from a trigger. The trigger attempts to send an E-Mail when a very specific case, involving changes and deletions in many tables, happens. The procedure runs fine when I, myself, test it.
When it was released to the users, however, the .NET application throws the following exception. The exception occurs when rows are deleted, and the trigger causes the E-Mail procedure to execute:
------------------
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
------------------
From what I can tell, the procedure sp_OACreate would cause a security hole if I just opened it to all users because they would be able to execute arbitrary code. First, is this assumption about security true?
Second, if simply granting permission to this procedure is unsafe, is there any way to allow the nested procedures to be used only in this context? That is, would it be possible to grant some sort of permission to allow sp_OACreate (and the other master database procedures in this example) to be called only from within E-Mail, and only with these parameters?
Thanks,
-Steve
-
It is always a good idea or best practice to use the full naming convention (or most of it).
Try.......
EXEC dbo.sp_OACreate
-SQLBill
-
This doesn't look too good.
For your first question - yes it is true. There is a reason that sp_OACreate permissions is set to members of sysadmin role only. So, the answer to the second question is then no, unfortunately.
Let me just give my .02 on the overall idea here.
This isn't really the recommended way to go about having mails being sent from the system.
The main problem you're having is that the call is placed in a trigger. This limits you to the permissions of the current user. (which you've noticed).
Another imo bad thing is concurrency. You should never ever place external calls in triggers, no matter what they do. Doing that could cause severe locking and concurrency problems down the line.
For this particular issue of sending notifications based on certain events, the recommended strategy is to have the trigger log the event to a specific table. Then, at certain intervals you have a separate job poll this table, and if events are found to be reported, this job can take care of sending mails etc... Also, by having a separate process doing the actual call to this particular implementation with sp_OACreate will alleviate you of the permission problems involved.
As an alternative there is also xp_smtpmail http://www.sqldev.net/xp/xpsmtp.htm , which also is an excellent and easy-to-use mailing alternative.
/Kenneth
-
Thanks for the suggestion. Since we're so close to a final release, I would prefer not to have to set up additional jobs and temporary tables, although that may be a good idea for the future.
For now, perhaps I'll try using the xp_smtpmail option and see how it works. The event in question that would cause an E-Mail to be sent is extremely rare, happening once per week on average. The locking issues caused by this rare event, therefore, would probably not be worth delaying the release to set up the table logging option.
Thanks again,
-Steve
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply