January 13, 2011 at 12:01 pm
I have users with an Access front end, and a trigger on a field so that IF the field is changed it sends an email.
To do this I was using sp_send_dbmail.
However, the end users of course don't have rights to this sproc.
Instead I created a sproc "spSendEmail".
Now my spSendEmail sproc just calls sp_send_dbmail and passes through profile, recipients, subject, and body.
I also used "EXECUTE AS OWNER" on the sproc, thinking I (who has sysadmin rights) own the sproc so it would execute as me.
However, this does not work as I had expected.
I get:
"The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'."
I thought using EXECUTE AS in the spSendEmail sproc would address this so that the person who calls the sproc would then get all the benefits of the permissions the sproc's owner had.
As a result, they would be able to then invoke sp_send_dbmail by way of my sproc....
What am I missing here?
I didn't want to have to go and create a proxy account... I was hoping to be able to do it this way.
is it because sp_send_dbmail is in msdb database and my sproc is in my own separate database (same database as the rest of the data)?
EDIT:
I see I could do:
ALTER DATABASE [My_Database_Name] SET TRUSTWORTHY ON
and that DOES work... but what EXACTLY is happening there to allow that?
Is that saying that, "yes, now we are going to allow ownership chaining to take place, because you the sysAdmin have told me that this database can be 'trusted' and isn't full of nasty 'evil' stored procedures"?
Or rather: you may access resources that are OUTSIDE the current database. Therefore, I will allow you to run the sp_send_dbmail sproc which is in the msdb database?
January 13, 2011 at 12:11 pm
Trustworthy on a database essentially means exactly what it says: the code in the database is "safe to execute". You've verified it, and you're telling the engine that it's okay.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 12:14 pm
So, I'm not opening my database up to "any horrible pandora's box of pain" because of this?
Using that to allow me to do my EXECUTE AS scheme, is legit and isn't taking some short cut?
(Basically I'm asking to make sure I'm not doing anything stupid with my plan to allow users to send emails by calling a sproc I created that uses EXECUTE AS OWNER to call the sp_send_dbmail sproc)
January 13, 2011 at 2:25 pm
That depends on who has access to create objects and run scripts in that database, and what rights they have. If, for example, you set the database to Trusted, and someone hacks into the database, they'll be able to run code that SQL Server would otherwise block. If the database is properly secured, then it should be fine.
Again, think of it this way: You're saying the database can be trusted. Can it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2011 at 10:59 am
Maxer (1/13/2011)
I also used "EXECUTE AS OWNER" on the sproc, thinking I (who has sysadmin rights) own the sproc so it would execute as me.However, this does not work as I had expected.
I get:
"The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'."
I thought using EXECUTE AS in the spSendEmail sproc would address this so that the person who calls the sproc would then get all the benefits of the permissions the sproc's owner had.
As a result, they would be able to then invoke sp_send_dbmail by way of my sproc....
What am I missing here?
As you have seen EXECUTE AS OWNER has some limitations related to ownership chaining. From this article http://msdn.microsoft.com/en-us/library/ms188354.aspx:
Ownership chaining has the following limitations:
- Applies only to DML statements: SELECT, INSERT, UPDATE, and DELETE.
- The owners of the calling and the called objects must be the same.
- Does not apply to dynamic queries inside the module.
Your login does not own the object msdb.dbo.sp_send_dbmail or its parent schema msdb.dbo which makes sense...a user login should not own those objects. Also of note not mentioned in the article is that an ownership chaining is broken across databases by default although you can setup cross-database ownership chaining if you need it.
If you want to use EXECUTE AS then I suggest you use the EXECUTE AS 'LOGIN_NAME' functionality.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 20, 2011 at 11:08 am
Then why did setting the database as trusted allow this to function in the way I needed?
I think that perhaps I am still missing a piece of the puzzle here.
January 20, 2011 at 1:34 pm
It's hard to tell from the sparse documentation on MSDN describing TRUSTWORTHY but it appears that setting the property ON allows objects using the EXECUTE AS OWNER functionality to use objects outside the current database context. That said, setting TRUSTWORTHY ON also allows other potentially threatening behavior so you will have increased security risks.
Using the EXECUTE AS 'LOGIN_NAME' functionality will give you more granular control over what the procedure can and cannot do inside and outside the current database context.
It has been a while since I have implemented anything with the EXECUTE AS 'LOGIN_NAME' functionality so I figured I would create an example for this post and it would serve as a refresher for me. Here is a working example...just replace "my_test_database" with your working database's name:
-- create a couple logins and allow one to impersonate the other
USE [master]
GO
CREATE LOGIN test_mail WITH PASSWORD = 'some_password_#11'
GO
CREATE LOGIN test_mail_elevated WITH PASSWORD = 'some_password_#11'
GO
GRANT IMPERSONATE ON LOGIN::[test_mail_elevated] TO [test_mail]
GO
-----------------------------------------------------------------------
-- add the elevated user to msdb and allow it to execute the mail proc
USE [msdb]
GO
CREATE USER [test_mail_elevated] FOR LOGIN [test_mail_elevated]
GO
GRANT EXECUTE ON dbo.sp_send_dbmail TO [test_mail_elevated]
GO
-----------------------------------------------------------------------
-- add both users to your working database
USE [my_test_database]
GO
CREATE USER test_mail_elevated FOR LOGIN test_mail_elevated
GO
CREATE USER test_mail FOR LOGIN test_mail
GO
-----------------------------------------------------------------------
-- create the proc in your working DB that wraps the msdb mail proc
USE [my_test_database]
GO
IF OBJECT_ID(N'dbo.send_some_mail') > 0
DROP PROC dbo.send_some_mail
GO
CREATE PROC dbo.send_some_mail
AS
BEGIN
EXECUTE AS LOGIN = 'test_mail_elevated'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'DBMailProfile',
@recipients = N'someone@somewhere.com',
@subject = N'Some info'
REVERT
END
GO
GRANT EXECUTE ON dbo.send_some_mail TO test_mail
GO
-- now you can try logging into the working DB using the test_mail login
-- and successfully execute this proc and in turn execute the mail proc
-- in msdb
-----------------------------------------------------------------------
-- cleanup
USE [my_test_database]
GO
DROP USER test_mail
GO
DROP USER test_mail_elevated
GO
IF OBJECT_ID(N'dbo.send_some_mail') > 0
DROP PROC dbo.send_some_mail
GO
USE [msdb]
GO
DROP USER [test_mail_elevated]
GO
USE [master]
GO
REVOKE IMPERSONATE ON LOGIN::[test_mail_elevated] TO [test_mail]
GO
DROP LOGIN test_mail
GO
DROP LOGIN test_mail_elevated
GO
-----------------------------------------------------------------------
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply