|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Newbee question about permissions and sp_OACreateI'm creating a trigger that shall monitor a table on inserts and if certain conditions are met, it should send a mail the administrator. I do not want to use SQLmail for many reasons so I'm using the extended procedure sp_OACreate to create an instance of the CDO.Message object The problem is that only sysadmins can use the extended procedure sp_OACreate, and of cause I do not want users to be sysadmins. I think I read or learned somewhere that stored procedures runs under the security context of the user that created the procedure, so why can't I as the sysadmin create a my_sendmail procedure that uses the sp_OACreate extended procedure and grant permission to all users to the my_sendmail procedure? Or, is there another way? Regards, Jorgen D. First of all, this excedes the purpose of triggers. What has sending mail got
to do with ACID? It would be much more efficient if you created a job to monitor the table and log these special events in a separate table/file and mail the report/warning. Second, why is it not possible for you to use SQL Mail? It's far more efficient than (ab)using sp_OA procedures. And it can be used by any user without compromising the security. How about notification services? If mailing these messages was requested by a manager, you can be pretty sure, he'll start hating it after about a month. Either the messages will be too frequent or too infrequent and he'll still need to keep statistics somewhere, somehow. But I'm just guessing here... Anyway, seems to me that what you really need is some sort of logical event logging. This IS the purpose of triggers - propagating changes in one table to another. Be careful out there... ML Why do some people answer a question with more questions? (no, thanks for
your input) My trigger do populate data to other tables (sending work orders to PDA’s, via merge replication), and when done it’s important that the users implicated (the ones that gets the work order) gets an mail/sms to inform them about it (they should react to it immediately) This mail shall originate form different senders, therefore not SQL-mail which is based on a single profile (perhaps there is a way around that that I didn’t find) Another reason not to use SQL-mail is in another scenario, that I’m not going to explain in detail, but the mail should be HTML formatted report larger than 8000 bytes. Notification services? Hmmm, I’ll look into that, (as I don’t know what that is) But to get back to my original question….. If I want to use an extended procedure in my own procedure, how can I set permissions so the ordinary mortal users can execute that? Regards, Jorgen D. Show quote "ML" wrote: > First of all, this excedes the purpose of triggers. What has sending mail got > to do with ACID? It would be much more efficient if you created a job to > monitor the table and log these special events in a separate table/file and > mail the report/warning. > > Second, why is it not possible for you to use SQL Mail? It's far more > efficient than (ab)using sp_OA procedures. And it can be used by any user > without compromising the security. > > How about notification services? If mailing these messages was requested by > a manager, you can be pretty sure, he'll start hating it after about a month. > Either the messages will be too frequent or too infrequent and he'll still > need to keep statistics somewhere, somehow. But I'm just guessing here... > > Anyway, seems to me that what you really need is some sort of logical event > logging. This IS the purpose of triggers - propagating changes in one table > to another. > > Be careful out there... > > > ML Any user who needs to execute sp_OA procedures must have appropriate
privileges to do so. I'm sorry, I'm simply curious as to what you're trying to do. You say certain users must be notified (with a large report, even) of certain changes to yor data. And this must be done immediately after those changes were commited. Why? Do this users then do something? Couldn't that be done automatically? It's like in that nuclear plant: "push this button when that light goes on" - why not just automatically do whatever the button does when conditions are met which normally tur the light on...? Instead of sending huge reports you could simply send a link to the report built by the SQL Reporting Services. Consider the alternatives before you compromise security. ML Some users are security guards which have to respond to alarms. When they get
notified they should turn on their PDA and connect to the server to get detailed information of the alarm (address, codes etc.). Other users are doing emergency road help. So the notification is important to be timely. They can’t ask the server/system to drive to an incident. About the reports – They aren’t huge. A single HTML formatted page (tables, and colours) is larger than 8K which is the limit of SQL-mail, further more I can’t get SQL-mail to send in HTML. The reports are requested from the users PDA to be sent to the customers email. The reports are dynamically created on the server and can’t be sent from the PDA (they don’t have the data) Conclusion: Since I have to use CDO.Message I have to grant all users public access to the master database, and then on the sp_OAxxxxx procedures, give the users EXEC rights. What happened to the ideology “The code of a stored procedure runs within the security context of the creator� Show quote "JorgenD" wrote: > Why do some people answer a question with more questions? (no, thanks for > your input) > My trigger do populate data to other tables (sending work orders to PDA’s, > via merge replication), and when done it’s important that the users > implicated (the ones that gets the work order) gets an mail/sms to inform > them about it (they should react to it immediately) > This mail shall originate form different senders, therefore not SQL-mail > which is based on a single profile (perhaps there is a way around that that I > didn’t find) > > Another reason not to use SQL-mail is in another scenario, that I’m not > going to explain in detail, but the mail should be HTML formatted report > larger than 8000 bytes. > > Notification services? Hmmm, I’ll look into that, (as I don’t know what that > is) > > But to get back to my original question….. If I want to use an extended > procedure in my own procedure, how can I set permissions so the ordinary > mortal users can execute that? > > Regards, > Jorgen D. > > > "ML" wrote: > > > First of all, this excedes the purpose of triggers. What has sending mail got > > to do with ACID? It would be much more efficient if you created a job to > > monitor the table and log these special events in a separate table/file and > > mail the report/warning. > > > > Second, why is it not possible for you to use SQL Mail? It's far more > > efficient than (ab)using sp_OA procedures. And it can be used by any user > > without compromising the security. > > > > How about notification services? If mailing these messages was requested by > > a manager, you can be pretty sure, he'll start hating it after about a month. > > Either the messages will be too frequent or too infrequent and he'll still > > need to keep statistics somewhere, somehow. But I'm just guessing here... > > > > Anyway, seems to me that what you really need is some sort of logical event > > logging. This IS the purpose of triggers - propagating changes in one table > > to another. > > > > Be careful out there... > > > > > > ML You're describing a messaging and reporting system for which web services are
the ideal solution. You have a central data server or a data warehouse and several clients, that aren't capable of maintaining a 24/7 connection to HQ. Wouldn't it be more efficient if the clients would only receive short messages, rather than full reports? Upon the notification they could then connect to the Report server to see the details of the arising situation and then decide if they need to download them for off-line use. That way you can allow access to the same data to the PDA users and to the desktop users. You might end up developing a messaging and reporting system using webservices and smart clients. You could even earn $50 grand!!! :) (http://www.csdevcompetition.com/) Giving master database/extended procedure privileges to the Public role is a bad idea. It is IMHO essential to use a custom role for this - with explicitly limited privileges. Also consider the fact that your inserts might slow down due to the needed complexity of these triggers - that is if the conditions are met frequently. Of course you'll need a lot of error-handling as well - you don't want to lock up the server, do you? ML Hi
Look at SQL Server Notification Services to kick of a process where you have a .NET application send the mail via SMTP. If you kick off an sp_OA* within a trigger, and it fails, you end up having a rolled back transaction, possible some e-mails being sent about data that no longer exists. If you do not want to use NS, then in your trigger, write a row to another table indicate that something must be done, and then have SQL Server Agent process poll the table and send the e-mail. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "ML" wrote: > You're describing a messaging and reporting system for which web services are > the ideal solution. You have a central data server or a data warehouse and > several clients, that aren't capable of maintaining a 24/7 connection to HQ. > > Wouldn't it be more efficient if the clients would only receive short > messages, rather than full reports? Upon the notification they could then > connect to the Report server to see the details of the arising situation and > then decide if they need to download them for off-line use. > > That way you can allow access to the same data to the PDA users and to the > desktop users. > > You might end up developing a messaging and reporting system using > webservices and smart clients. You could even earn $50 grand!!! :) > (http://www.csdevcompetition.com/) > > > Giving master database/extended procedure privileges to the Public role is a > bad idea. It is IMHO essential to use a custom role for this - with > explicitly limited privileges. > > Also consider the fact that your inserts might slow down due to the needed > complexity of these triggers - that is if the conditions are met frequently. > Of course you'll need a lot of error-handling as well - you don't want to > lock up the server, do you? > > > ML  |
|||||||||||||||||||||||
Other interesting topics